12

I want to know the most efficient manner to update every row in an extremely large Oracle table for a single column. For example:

update mytable set mycolumn=null;

or:

update mytable set mycolumn=42;

My knowledge may very well be stale. What I do is alter the table to drop the column. Then, I alter the table to add the column with a default value of the new value that I want to use. Then, I alter the table to remove the default value for the column. I find this to be much faster than just running an update, but I have a feeling that there is a better method.

4
  • As far as I understand it adding a new not null column with a default is a metadata only change in Oracle. I doubt they will have optimised the "update all rows to the same value" case. Is this a common operation for you? Jan 17, 2015 at 21:28
  • 1
    Just try both methods and time them. What's preventing you from doing this ? Behold the fact you must end with the same result, not with a different result ! Otherwise, the comparison is invalid.
    – tvCa
    Jan 18, 2015 at 10:48
  • @tvCa I have tried both ways. If I just do an update, it runs for about two hours and then I kill it. If I drop a column, it only takes a few seconds. Adding a column without a default value (which nulls the column) only takes a few seconds. Adding a column with a default value takes about 30 minutes. So, if I want to, for example, set all values in a column to 'Some Value', I currently drop and add the column. I just want to know if there is a faster way to do it.
    – kainaw
    Jan 18, 2015 at 19:31
  • 2
    Are you using 11gR2? @MartinSmith is correct. See here for a description on how adding the new column with a DEFAULT as NOT NULL is a much faster change than adding it as NULL, which will force an update of all rows in the table (just as issuing an UPDATE statement will). The problem I see is removing the DEFAULT value afterward, because the performance increase comes from storing the DEFAULT in the dictionary. You will also have to deal with the NOT NULL constraint at that point.
    – ansible
    Jan 19, 2015 at 17:52

5 Answers 5

2

A lot depends on the other activity going on against this table while you are doing this mass update. I hope you have some kind of test environment where you can run some samples of what you'd like to do and get an idea of which way is best. I would try:

  1. Run the single update table set column_name = blah;
  2. Create a plSql loop to select all of the primary keys in the table and loop through them, updating the column=blah and committing every X updates (maybe 10000). You can parallelize this code by copying it and making it copy do a separate section of Primary keys.

We had a very similar issue with a table that was very actively used in the OLTP system and we were able to parallelize it 5x and ran with no user locking impact on a 100+ MM row table committing every 10000. You didn't say how large your table is or what kind of application your are running, but this kind of solution may fit you.

0

For a fast UPDATE, be sure you don't have any triggers that are firing.

SELECT trigger_name, status FROM user_triggers WHERE table_name = 'MYTABLE';

ALTER TABLE mytable DISABLE ALL TRIGGERS;

Be sure to only re-enable the ones you want when you're done.

ALTER TRIGGER mytrigger ENABLE;

You might also be running into the overhead of index maintenance. Try rebuilding your indexes separately. To do that, the answer here by pappes should be helpful: https://stackoverflow.com/questions/129046/disable-and-later-enable-all-table-indexes-in-oracle

I'm repeating pappes' answer here for reference. (Note that this SPOOL command makes assumptions about your platform and environment.)

set pagesize 0    
alter session set skip_unusable_indexes = true;
spool c:\temp\disable_indexes.sql
select 'alter index ' || u.index_name || ' unusable;' from user_indexes u;
spool off
@c:\temp\disable_indexes.sql

Do import...

select 'alter index ' || u.index_name || ' rebuild online;'
  from user_indexes u;
-1

remove the index. update the column. return the index back. but if the column contains one and same value for all rows you can drop the index.

-2

If you do not have space limitation, you can create a new table, the same as your table with your new column added to that table and delete the old table:

create new_table as
select old_table.*, (with or without default_Value) as new_column
from old_table;
2
  • 1
    Will this more efficient? Why? And what if there are FKs that reference the existing table? Jun 25, 2016 at 19:15
  • yes, You can try it on other sample table and see the result yourself. If there are FKs, I do not know exactly but you can disable and enable them if it is efficient.
    – E_Salamon
    Jun 26, 2016 at 8:28
-3

Try multiple update/commit sequences. Inserting/Updating/Deleting too many rows without a commit leads to heavy IO load. It can be fairly optimized knowing block sizes and record sizes and stuff.

For deleting whole data on a table, truncate table x is better than delete from x. Also purging makes another process workload.

Edit: You can use inmemory option, loading table in memory in columnar format and then do the update. it really depends on your DB's relationships and structure. See this article.

8
  • 3
    They want to update one column of the table. I don't see how truncate or delete would be of any help. Sep 28, 2015 at 8:40
  • @ypercube I just explained how multiple data manipulation without commit leads to unwanted IO load; either be update or other OLTP s.
    – Tala
    Sep 28, 2015 at 23:39
  • 3
    Could you explain how frequent commits reduce I/O? Wouldn't they increase I/O because of checkpoints?
    – mustaccio
    Sep 29, 2015 at 21:29
  • 3
    Your use of nonconventional terminology ("tx journal", "flushes your session") is a bit confusing. Whether you use multiple short transactions or one massive transaction, the total volume of generated redo records will be the same. I/O operations only occur when the redo log buffer is written to disk (leaving alone buffer cache checkpoints for now), which happens upon commit or when the redo buffer is almost full. Subsequently, if you commit frequently you cause additional I/O, so I'm wondering how that can reduce I/O.
    – mustaccio
    Oct 1, 2015 at 17:20
  • 4
    You might want to read what Tom Kyte has to say about "frequent commits": asktom.oracle.com/pls/apex/… "is wrong, wrong, wrong. So wrong.... So very very wrong"
    – user1822
    Nov 3, 2015 at 7:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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