Oracle trying to update a table with data with a count values

Question

the below code doesnt complete as im trying to update a table with 2500 000 rows. Please see code below and advise what could be the issue, and how I could improve the performance so the update can be completed.

begin

open C5_CUR;
loop
  FETCH C5_CUR
    into C5_ARRAY;
  exit when C5_CUR%notfound;

  open C4_CUR(C5_ARRAY.client_entity_number);
  loop
    FETCH C4_CUR
      into C4_array;
    exit when C4_CUR%notfound;

    update Wh_stg_clc.MARKETING_NEWSLETTER_REP n
       set n.family_count = C4_array.Family_Count
     where n.client_entity_number = C5_ARRAY.client_entity_number;
    dbms_output.put_line('Count has been updated');

   commit;

  end loop;
  close C4_CUR;

end loop;
commit;

What you have done there is to reinvent the Nested Loop Join, plus doing an update row-by-row. As Littlefoot has said in their answer, you didn't post your cursor definition, so that makes it much harder for us to give you a specific answer. I reckon you could easily achieve your update by using a single MERGE statement. To do that, you need to join the two cursor sql statements to form a single sql statement (hint, your join condition will be whatever you're using the c5_cur.client_entity_number to filter the c4_cur). — Boneist, Oct 11 '18 at 07:33
In short, please provide the two cursor definitions and we can help you further. — Boneist, Oct 11 '18 at 07:34

score 2 · Answer 1 · answered Oct 11 '18 at 07:20

You didn't post cursor declarations.

Anyway: you're doing everything in nested loops, comitting within the loop.

first of all, remove COMMIT out of the loop; leave one at the end of the procedure
if possible, do everything in a single SQL UPDATE statement, avoiding PL/SQL

Oracle trying to update a table with data with a count values

1 Answers1