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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


    /* ~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):

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

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


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.