Update the large table takes a lot of time without any sucess

Question

I have recently added a new column to the Oracle table with 715476364 records and 37 columns. The new column name is b_id. We have another table,b_id_list, which has b_id value and I have to use that table to update the new column. the_table and b_id_list have a common column named id; so I use this pl/sql query:

set serveroutput on;
begin
dbms_output.put_line('START');
FOR rec in 
 (
  select /*+parallel(10)*/ distinct date_m td from the_table order by td
 )
LOOP
dbms_output.put_line(rec.td);
   EXECUTE IMMEDIATE 'update /*+parallel(10)*/ the_table a
        set (b_id)=(select b.b_id b_id from b_id_list b where 
        substr(a.id,12)=to_char(b.id)) 
        where exists (select 1 from b_id_list b where substr(a.id,12)=to_char(b.id)) and date=to_date(rec.td,''YYYYMMDD'')';
   EXECUTE IMMEDIATE 'commit';
  END LOOP;
END;

The table is partitioned on date_m field. The problem is when I run the above query, it takes more than two days and then it is finished with this error:

ERROR at line 2
ORA-01555 : snapshot too old: rollback segment number 9 with name "_SYSSMU9_1692468413$" too small
ORA-06512 : at line 13
ORA-06512 : at line 13

Would you please guid me what is the problem with the query and how to make it run faster?

Any help is really appreciated.

Answer 1

Is there a reason why you couldn't just use a single merge statement to do the job? E.g.:

MERGE INTO the_table tgt
USING b_id_list src
  ON (SUBSTR(tgt.id, 12) = to_char(b.id))
WHEN MATCHED THEN
  UPDATE SET tgt.b_id = src.id
  WHERE tgt.b_id IS NULL
  OR    tgt.b_id != src.b_id;