Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
Will you probably the statement you currently have? And the tables' structure (column datatypes) and indexes? –  ypercube Apr 3 '13 at 15:10
    
@ypercube - information added to the question. –  robarwebservices Apr 3 '13 at 15:37

1 Answer 1

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).

share|improve this answer
    
I agree, but our DBAs wanted to avoid this way to keep the undo logs small... –  robarwebservices Apr 4 '13 at 16:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.