Updating Millions of Rows (Merge vs. Bulk Collect)

For a 9.2.0.5 database, I have been asked to add few columns and update them with new values from another table. Base table contained 35 million rows.

To accomplish this, we researched 3 solutions: update via insert, merge statement and pl/sql with bulk collect

Update via Insert

With this method, you don’t execute any updates, instead you create a new table with the old and new data.

Here are the steps:

1. Create a new empty table with both old and new columns.
2. Insert data using append hint into the new table.
3. Recreate indexes on new table.
4. Gather statistics on new table.
5. Rename old table or drop.
6. Rename new table to old name.

Pros:

• The fastest method to finish.
• Not complicated, steps are straight forward.

Cons:

• When executing steps 5 & 6 there is a sub-second where the table is not accessible. Even that tiny bit is not acceptable in our environment.
• If you have materialized view log or CDC (change data capture), you would have to drop it and then recreate it.

We didn’t benchmark this method because it wouldn’t work in our environment due the sub-second unavailability of base table.

Merge Statement

Use Oracle built-in function merge introduced in 9i.

Pros:

• Easiest to implement, just 1 SQL statement.

Cons:

• Merge statement was very slow. It took 17 hours to complete which was unacceptable.

Bulk Collect

Using rowid to perform bulk updates on the base table.

Here are the steps:

1. Add/populate rowid of the base table to new table containing new data.
2. If possible, try to sort the new data in the same way like base table. In my case, we sorted the new data by the primary key. This will reduce multiple reads/writes of the same base table blocks.

Pros:

• It took 74 minutes down from 17 hours

Cons:

• Writing pl/sql was a bit of pain and must be adjusted and retested every time there is a change.
• You might have to drop materialized view log and change data capture tables and then recreate them again after the operation finishes.

Here is sample code for bulk collect

DECLARE
-- source table cursor (only columns to be updated)

CURSOR base_table_cur IS
SELECT rowid, col1, col2
FROM   base_table;

type base_type  IS TABLE OF base_table_cur%ROWTYPE  INDEX BY PLS_INTEGER;

base_tab base_TYPE;

-- new data

CURSOR new_data_cur IS
SELECT row_id, new_col1, new_col2
FROM   new_data;

type new_data_type  IS TABLE OF new_data_cur%ROWTYPE  INDEX BY PLS_INTEGER;

new_data_tab new_data_TYPE;

type row_id_type  IS TABLE OF ROWID INDEX BY PLS_INTEGER;

row_id_tab row_id_TYPE;

TYPE rt_update_cols IS RECORD
(
new_col1 new_data.new_col1%type,
new_col2 new_data.new_col1 %type
);

TYPE update_cols_type IS TABLE OF rt_update_cols INDEX BY PLS_INTEGER;

update_cols_tab update_cols_type;

dml_errors EXCEPTION;

PRAGMA EXCEPTION_INIT(dml_errors, -24381);

BEGIN

open base_table_cur;
open new_data_cur;

loop

fetch base_table_cur bulk collect into base_tab limit 5000;
fetch new_data_cur bulk collect into new_data_tab limit 5000;

FOR i in base_tab.FIRST .. base_tab.LAST LOOP
  row_id_tab(i) := new_data_tab(i).row_id;
  update_cols_tab(i).col1 := new_data_tab(i).new_col1;
  update_cols_tab(i).col2 := new_data_tab(i).new_col2;
end loop;

FORALL i IN base_tab.FIRST .. base_tab.LAST SAVE EXCEPTIONS

update (select col1, col2
              from base_table )
set row = update_cols_tab(i)
where rowid = row_id_tab(i);

commit;

exit when base_tab.count < 5000;

end loop;

commit;

close base_table_cur;
close new_data_cur;

EXCEPTION

WHEN dml_errors THEN

FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP

dbms_output.put_line('Output your error here');

END LOOP;

END;
/

Conclusion

Use insert via update if sub-second of downtime is acceptable

Use merge if you have small set data

User pl/sql bulk collect if you have large set of data and even sub-second of downtime is not acceptable

Hazem Ameen
Senior Oracle DBA

---

Possibly related posts: (automatically generated)

• SQL Interview Questions
• Updating millions of records in a table
• BASIC SQL