PL/SQL for Updating 1 Column in Table A Where PK = a row in table B

oracleplsql

Using an oracle DB, 11g r2, I need to write a pl/sql which updates one column in table A where the PK exists in table B, with commits ever ~50,00 rows.

3200000 rows are involved in the update, and table A has ~70 million rows total.

What is the most efficient way to do this?

Table A (has ~70 million rows, and ~100 columns. Only one needs to be updated):

CREATE TABLE ORD 
   (    
    ORDER_KEY NUMBER NOT NULL, 

    /* ~95 other columns */

    ORDER_TYPE" NUMBER(10,0) /* col needing update */
   )

Order key has an index.

Table B (contains the id's of the rows from table a which need the update):

CREATE TABLE GR_ORD (ORDER_KEY NUMBER NOT NULL)

Best Answer

Most efficient way to do the update is most likely not with PL/SQL, but with single SQL statement, for example:

update ord
set order_type=<new value>
where order_key in (
  select order_key
  from gr_ord
);

or with merge:

merge into ord
using gr_ord
on (ord.order_key = gr_ord.order_key)
when matched then
  update set order_type=<new value>;

You should be able to do ~3M update in a single transaction. I have run updates against table with 100+ columns at rate of 1M updates in 15 seconds (single thread in average server).

Related Question