Start Updating millions of records in oracle

Updating millions of records in oracle

To satisfy our curiosity however, let’s see how the PL/SQL block stacks up against the UPDATE and MERGE statements if we were allowed to add indexes to the NEW_SALARY_INFO table and if the EMPLOYEES table did not have a security policy against it.

Please let me know if there is a better way of updating the col2 of t1 instead of firing so many update statements. If so, then how and how will it affect performance?

"Usually" is an important word here: check it out in a test environment to confirm for your data.

If you and your users can cope with the data being partially updated at any given time then this (probably with a smaller number of rows then 100,000) gives you the advantage of not blocking your application for other uses as you don't have an update lock on the entire table for the length of time it takes to update all the rows.

Is the process I/O bound (there is lots of disk activity) or CPU bound (which is possible if the functions are complex)?

It is most likely the former and if so then if there are indexes covering those columns it is usually better to drop them before operations that update every row and recreate them afterwards (don't drop the PK and/or clustered index though).

COUNT 25 UPDATE employees 26 SET employee_salary = lt_new_salary_info(i).new_salary 27 WHERE employee_id = lt_new_salary_info(i).employee_id; 28 29 v_rowcount := v_rowcount SQL%ROWCOUNT; 30 END LOOP; 31 32 CLOSE c_new_salary_info; 33 34 DBMS_OUTPUT. The first reason has already been noted, and that is the use of bulk operations and FORALL to reduce context switching between PL/SQL and SQL. The optimizer no longer has to worry about performing an update as part of the query and is able to simply join the two tables in a very efficient manner.

PUT_LINE(v_rowcount||' rows updated.'); 35 END; 36 / 999000 rows updated. Elapsed: .91 SQL In approximately 30 seconds we’ve updated nearly one million rows with data from a four million row table. We’ve gone from a series of SQL update statements that would either have taken hours to complete or produced an Oracle error, to a small PL/SQL block that does exactly we need in a very reasonable amount of time.

Many shops are replacing their platter-style disks with solid-state disks, and creating a very small data buffer, just for the updates.