Hi everyone.

I've a problem with the bulk and forall statement. I'm trying to do a update but for 564371 rows. I use 4 variable like this:
Type v_column1 IS TABLE OF myTable.column1%TYPE
. . .
So, I use this variable when I fetching the data for example (I use a cursor):
OPEN myCursor;
FETCH myCursor INTO v_column1, v_column2, v_column3;
CLOSE myCursor;

And the problem is in the next instruction when I update a table.
FORALL j IN 1..v_column1.count
UPDATE myTable2
SET column1 = v_column1(j)
WHERE column2 = v_column2(j) AND column3 = v_column3(j);

All this works fine, but it's take a lot of time. For update 550 rows I had to wait 45 minutes and I need to update 564371 rows.

I hope your help. Please.

Greetings.

Hi Mike,

>> All this works fine, but it's take a lot of time.

I agree, that's way too slow, even for a PC.

- What type of server are you running, and on what OS?

- How long did it take wuith a standard SQL update statement?

- Can you post the execution plan for this update?

Usually, slow performance of FORALL is due to a missing index, or some other weirdness where each update does too much I/O, but your programmign logic looks suspicous.

If you don't already have it, get Dr. Halls book on PL/SQL tuning:

http://www.rampant-books.com/book_2006_1_plsql_tune.htm

And look at Kent Crotty's example of using FORALL:

http://www.dba-oracle.com/oracle_news/news...ance_insert.htm

************************************************************

>> For update 550 rows I had to wait 45 minutes

Here are some notes on tuning Oracle updates:

http://www.dba-oracle.com/t_efficient_upda...ql_dml_tips.htm

************************************************************
>> For update 550 rows I had to wait 45 minutes and I need to update 564371 rows.

That impossible! If you updated 550 rows, that's how many rows are changed.

Can you elaborate on what you mean here?

Did you mean "touched" all the rows?


FETCH myCursor INTO v_column1, v_column2, v_column3;


Are you filtering this, or sucking-in the whole table?

Remember, FORALL and BULK COLLECT are NOT intended to replace standard SQL updates.

Hi.
First, thank you for answer.

I've a computer with Windows XP Proffesional and an Intel Core Due and 2.00 GB of RAM.
With a standard SQL update statement it takes about 4 minutes for 550 rows.
The total of rows for "myTable" is of 564371 rows.

This is my execution plan:
DECLARE
v_start NUMBER;
v_end NUMBER;

CURSOR c_bkup_po_items IS
SELECT number1, number2, number3, number4 FROM myTable where rownum <= 550;

--by number1
TYPE po_id_type is table of myTable.number1%type;
r_po_id po_id_type := po_id_type();

--by number2
TYPE inv_id_type is table of myTable.number2%type;
r_inv_id inv_id_type := inv_id_type();

--by number3
TYPE line_type is table of myTable.number3%type;
r_line line_type := line_type();

--by number4
TYPE items_dt_type IS TABLE OF myTable.number4%type;
r_items items_dt_type :=items_dt_type();
BEGIN
v_start := DBMS_UTILITY.get_time;

open c_bkup_po_items;
--loop
fetch c_bkup_po_items BULK COLLECT INTO r_po_id, r_inv_id, r_line, r_items; -- limit 100;
--dbms_output.put_line('in r_po_id: ' || to_char(r_po_id.count));
--EXIT WHEN c_bkup_po_items%NOTFOUND;
--end loop;
close c_bkup_po_items;

forall i IN 1..r_items.count SAVE EXCEPTIONS
update myTable2
set item_price = r_items(i)
where po_id = r_po_id(i)
and inventory_id = r_inv_id(i)
and line = r_line(i);
--and item_price != r_items(i);

DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows updated.');
COMMIT;
v_end := DBMS_UTILITY.get_time;
dbms_output.put_line('Total time: ' || to_char(v_end - v_start));

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Cannot found the data');
ROLLBACK;
WHEN OTHERS THEN
dbms_output.put_line('Error: ' || SQLCODE || ' - ' || SQLERRM);
for j in 1..SQL%bulk_exceptions.count
loop
dbms_output.put_line(sql%bulk_exceptions(j).error_index || ', ' ||
sqlerrm(-sql%bulk_exceptions(j).error_code));
end loop;
ROLLBACK;
END;

I'm following the example of the links but I'm in the same situation.

Greetings

Hi Mike,

>> I've a computer with Windows XP Proffesional and an Intel Core Due and 2.00 GB of RAM.

That's not much of a server. Is this a production system, or are you a student?

*********************************************************

>> This is my execution plan:

No, that not an execution plan! The execution plan reveals the details of the internal update steps:

http://www.dba-oracle.com/t_sql_execution_plan_tips.htm

In SQL*Plus, just issue "set autotrace on" and re-run it. That will give us a good clue what's up.

**********************************************************

>> CURSOR c_bkup_po_items IS
SELECT number1, number2, number3, number4 FROM myTable where rownum <= 550;

The idea is to only select the rows that you want to update. If you suck-in the whole table, of course it;s going to take longer than a standard update!

Remember, never do in PL/SQL what you can do in standard SQL. You you have the intro books on SQL and PL/SQL?

http://www.rampant-books.com/book_2005_1_easy_plsql.htm

Anyway to summarize:

0 - "set autotrace on" and post the stats

1 - What is it that you are doing that requires PL/SQL?

2 - What type of problem are you trying to solve?

Please advise!

Hi again

Sorry, this the execution plan:
for 550 rows: 294.50610352 seconds

AutoTrace Enabled
Statistics
-----------------------------------------------------------
0 recursive calls
0 physical writes non checkpoint
0 DBWR transaction table writes
0 DBWR free buffers found
0 global cache cr blocks served
anonymous block completed
Statistics
-----------------------------------------------------------
379 recursive calls
0 physical writes non checkpoint
0 DBWR transaction table writes
0 DBWR free buffers found
0 global cache cr blocks served

I'm not a studient, I'm work at the computers company but I'm new here. I've a laptop connect with a Data Base server.

So, I need to do a backup for a column of a table (in specific 4 columns) and all the process is the following:
1.- I fetching the data in the Table1 and I store that ones in other table (Table2).
2.- After I change a column in the Table1 with a alter file, becuase is a request. For example:
columnX Number(7,2); --> the original
columnX Number(9,4); --> after of apply the alter file
3.- I need to recover the data in the Table2 and store that ones in Table1 again. And is here where I've problems. The others scripts work fine.

So, like I said I need to update 564371 rows.
These are the time for diferents updates:
550 rows --> between 4.15 and 4.99 minutes
1000 rows --> between 6.20 and 6.98 minutes
5000 rows --> between 44.78 and 45.38 minutes

But I think that is a lot of time and I don't know, what's happend? or why is working like that?

Greetings.

P.S.: I'm following all your examples.

Hi Mike,

>> Sorry, this the execution plan:

Sorry, that's not an execution plan!

Whatever, let's look at your problem.

Time takes time, and large updates on a personal computer are going to take awhile. . . .

You want to change a column from Number(7,2) to Number(9,4), right?

I would do it, something like this:



If it still runs long, run a STATSPACK report during the update, and that will show the bottleneck:

http://www.dba-oracle.com/t_taking_statspack_awr_report.htm

I would be willing to bet you are hitting tons of exceptions. How many? Probably 550 if this has been run more than once. What are the constraints on myTable2? Since you have a named cursor, why are you using SQL%rowcount? Are there any triggers on this table? And, you never answered the question about the indexes on the table.