DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: FORALL BULKBIND Oracle9i R2

  1. #1
    Join Date
    May 2001
    Location
    Cape Town, South Africa
    Posts
    52

    FORALL BULKBIND Oracle9i R2

    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;
    --------------------------------------

  2. #2
    Join Date
    Jan 2004
    Posts
    162
    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.

  3. #3
    Join Date
    Sep 2001
    Location
    UK
    Posts
    45
    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

  4. #4
    Join Date
    May 2001
    Location
    Cape Town, South Africa
    Posts
    52
    Thanks I will give it a try

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width