large table update 1

[saxgeek]

I need to update rows in a large (100 million rows) table. The number of rows that will be changed is relatively small compared to the size of the table - my guess is that no more than 2-3% will be updated at any one time. Does anyone know of a fast way to do this other than with update statements?

 

[dnoeth]

Depends on:
- are there secondary indexes on modified columns
- are Primary Index columns modified
- are you updating from another table
- how likely is a rollback

Submitting an UPDATE is probably the fastest way for that small percentage. But a tollback may be slow if there's an error.

Even an INSERT/SELECT into an empty table (modifying the data on the fly using CASE) is probably not faster and you need much more disk space. But a rollback is instantaneous.

Another way may be an export of the modified rows/columns and a MultiLoad. No need for a rollback.

Dieter

 

[saxgeek]

Thanks Dieter - I had thought about the insert/select but space is at a premium.

I like the export/multiload idea better - some of the columns being updated have secondary indexes on them but the primary key is not involved at all.

Here's what I'm trying to do. There are a bunch of lookup tables where the description on the codes change periodically. I have summary tables that contain the lookup codes and their descriptions - so when the descriptions change, I need to update the summary tables.

I was going to write triggers on the lookups to save the values that change into another table and then use those changed values to update the big tables.

Rollback shouldn't be an issue here but you never know.