Thank you for taking the time to answer my question! I've taken your approach and can see a massive performance increase nearly halving the time it takes to execute.

create table new_table
as
select ...., 
       case when ua1.oid is null then t.payer_account_number else ua1.user_account_number end payer_account_number,
       case when ua2.oid is null then t.payee_account_number else ua2.user_account_number end payee_account_number
  from transaction001 tran,
       user_account_number ua1,
       user_account_number ua2
 where tran.oid = ua1.oid(+)
   and tran.oid = ua2.oid(+);


drop table transaction001;
rename table new_table to transaction001;

Hi there,

The goal is to update _all_ the records in the table (t) (since all the records in the other table (ua) has been updated / changed. and thus I imagine will affect most if not all data blocks. Already for one other table the method was chosen to create a new table as (select) since it was MASSIVE and required only one columns' data to be changed and it did / does not matter what the new value is. Unfortunately in this case, it very much does. But, as you've now pointed out, seems the same route can be followed.

Thank you again for providing valuable input. You've certainly broadened my knowledge ten fold the past couple of days!.

Best Regards.

Hi Tom,
We have a similar scenario in that we have updates coming into a large(ish) table that we're not sure how to make more efficient.

There's a table, say, 'order details'. It's got about 15 columns, most description-type varchars-256. This table does not keep history.
As part of the business process, each record get updated at least twice as follows after it's been inserted:
- for a given order id,
- every record in that order gets its status updated (3-char column) by the system (once); usually 1,000 records in 1 shot
- then individual records in that order may get updated by the business one to umpteen times across 1 description column and 3 flags over the next few days
- once this step in the process has finished, the records are never modified again (though occasionally order-based deletes happen as part of another process)

The table is about 500,000 rows now expected to grow to 10-15M over the course of the project.

NOTE: The first (full order) update we can do in one shot. For individual updates, we cannot receive them together from the application (i.e., even if a user changes 2 records and clicks 'submit', we will get them as 2 individual update statements to 2 records).

We were worried about constantly updating records like that in a table that size, so our original approach was to create a 'draft' table containing just the updateable values. When we insert into 'order details', we insert into 'draft' as well. All the updates are done there.
Then the question became what do we do with them at the end of the process--do we write them back to the original table or do we just take contents and insert into a new 'order details final amendments' table... Writing back to the original table would be cleaner, but seems to defeat the purpose.
1. Would doing direct updates in the base table be a problem in the long term (so there's value in having a draft table and potentially not writing back to the base table) or should we just update the base table over and over?
2. If we had to have a draft table for other reasons (say the business don't want to see transient changes in reports run against 'order details'), would that change the decision on what to do with it in the end--write finalised changes back to base table or keep in a separate one?

Thank you as always.

Hi Chris,
Not entirely sure what you mean by 'merge two changes into single update', as the two happen at different time by different processes: when an order is open for investigation, the status of every record is updated by the system (big starting update). Then business users can make some changes to some records, save their work, go away, come back the next day, make changes, etc. Nothing ca be combined with anything into a single update other than what gets 'saved' at the same time. They don't tend to update the same record multiple times, but they can update 20 records in an order, saving each one as they go (thus resulting in 20 updates to db).

The draft table is very skinny--only the fields that can be updated, but the indexes are mostly the same--no one searches for the bulk of the columns in the main table, they're for display only. We thought the draft table would give us less 'churn' on the main table, as it would be big. The draft table could be updated over and over, but it would be small, as records would be deleted once the investigation/updating of details is done. Are you saying that won't matter?

We would rather not get into flashback data archive, as reports are run by multiple applications against db, some are not Oracle and just want to run a simple query against a table. We are not in control of those.