FORALL BULKBIND Oracle9i R2

[truma1]

Hi I am trying to update a col on a large table (1.7mil rows)
I have this stored proc and I dont know how to conver the folloing code to use a forall bulkbind to speed up my update any sugestions or solutions?
---------------------------------
CURSOR cursor_a
IS
SELECT
a.field1, b.field2
FROM schema1.table1 a, schema2.table2 b
WHERE b.field3 = a.field3;
--
BEGIN

FOR curs_rec IN cursor_a
UPDATE
schema1.table1 a
SET a.field2 = curs_rec.field2
WWHERE a.field1 = curs_rec.field1;

COMMIT;
--------------------------------------

[padders]

It is likely that the fastest way (other than recreating the table) to do this update will be with a single update statement, probably update of view (where view is your query).

Bulk bind would certainly be an option though.

Post a proper example and get a proper solution.

[dinesh_hm]

Based on the data type of your columns in select statement
declare 2 pl/sql tables for eg.
type t_num is table of number(30) index by binary integer;
type t_char is table of char(30) index by binary integer;

/*declare 2 variables to hold the data values */

l_var1 t_num;
l_var2 t_char;

/* bulk collect the cursor elements in the pl/sql tables */

open cursor_a;
loop
fetch cursor_a bulk colect into l_var1,l_var2 limit 100000;
forall i in l_var1.first .. l_var1.last
UPDATE
schema1.table1 a
SET a.field2 = l_var2(i)
WHERE a.field1 = l_var1(i);
exit when cursor_a %notfound;
end loop;
close cursor_a;
commit;
------------------------------------
Hope this helps

[truma1]

Thanks I will give it a try