Oracle SQL - How to do massive updates more efficient and faster?

Question

I'm trying to update 500.000 rows at once. I have a table with products like this:

+------------+----------------+--------------+-------+
| PRODUCT_ID | SUB_PRODUCT_ID | DESCRIPTION  | CLASS |
+------------+----------------+--------------+-------+
| A001       | ACC1           | coffeemaker  | A     |
| A002       | ACC1           | toaster      | A     |
| A003       | ACC2           | coffee table | A     |
| A004       | ACC5           | couch        | A     |
+------------+----------------+--------------+-------+

I've sets of individually statements, for example:

update products set class = 'A' where product_id = 'A001';
update products set class = 'B' where product_id = 'A005';
update products set class = 'Z' where product_id = 'A150';

I'm making a query putting one update statement below the other update statement and putting a commit statement each 1.000 rows. It works fine (slow, but fine) but I wanna do it better if it can be possible in any way.

There is a better way to do this more efficient and faster?

Answer 1

One approach would be to create a temporary table holding your update information:

new_product_class:

product_id  class
==========  =====
A           A001
B           A005
Z           A150

product_id should be an indexed primary key on this new table. Then you can do an UPDATE or a MERGE on the old table joined to this temporary table:

UPDATE (SELECT p.product_id, p.class, n.product_id, n.class
          FROM product p
          JOIN new_product_class n ON (p.product_id = n.product_id)
  SET p.class = n.class

or

MERGE INTO product p
  USING new_product_class n
  ON (p.product_id = n.product_id)
  WHEN MATCHED THEN
      UPDATE SET p.class = n.class

Merge should be fast. Other things that you could look into depending on your environment: create a new table based on the old table with nologging followed by some renaming (should backup before and after), bulk updates.