Oracle SQL technique to avoid filling trans log

Question

Newish to Oracle programming (from Sybase and MS SQL Server). What is the "Oracle way" to avoid filling the trans log with large updates?

In my specific case, I'm doing an update of potentially a very large number of rows. Here's my approach:

UPDATE my_table
SET a_col = null
WHERE my_table_id IN 
(SELECT my_table_id FROM my_table WHERE some_col < some_val and rownum < 1000)

...where I execute this inside a loop until the updated row count is zero,

Is this the best approach?

Thanks,

Answer 1

Any UPDATE is going to generate redo. Realistically, a single UPDATE that updates all the rows is going to generate the smallest total amount of redo and run for the shortest period of time.

Assuming you are updating the vast majority of the rows in the table, if there are any indexes that use A_COL, you may be better off disabling those indexes before the update and then doing a rebuild of those indexes with NOLOGGING specified after the massive UPDATE statement. In addition, if there are any triggers or foreign keys that would need to be fired/ validated as a result of the update, getting rid of those temporarily might be helpful.

Answer 2

The amount of updates to the redo and undo logs will not at all be reduced if you break up the UPDATE in multiple runs of, say 1000 records. On top of it, the total query time will be most likely be higher compared to running a single large SQL.

There's no real way to address the UNDO/REDO log issue in UPDATEs. With INSERTs and CREATE TABLEs you can use a DIRECT aka APPEND option, but I guess this doesn't easily work for you.

Answer 3

Depends on the percent of rows almost as much as the number. And it also depends on if the update makes the row longer than before. i.e. going from null to 200bytes in every row. This could have an effect on your performance - chained rows.

Either way, you might want to try this.

Build a new table with the column corrected as part of the select instead of an update. You can build that new table via CTAS (Create Table as Select) which can avoid logging.

Drop the original table.

Rename the new table.

Reindex, repoint contrainst, rebuild triggers, recompile packages, etc.

you can avoid a lot of logging this way.