I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows.

I don't have admin access, or access to an admin, so this needs to be accomplished with basic sql and no temp table creates.

Further complicating matters is if I try to just do a simple update where 1=1, it runs out of redo space.

The way I have it running right now is looping such as this:

  update mytable set mycolumn = '1' where mycolumn is null and rownum < 50000;
end loop

but I know this is probably naive and there must be a quicker and more elegant solution.

  • Is the table partitioned? Aug 30, 2011 at 15:02
  • I don't believe so. There are a couple of indexes, but none of them involve the column I am updating.
    – owook
    Aug 30, 2011 at 17:31

2 Answers 2


If you have the space, you can CTAS using minimal undo/redo. If you have any indexes at all, doing it any other way will be very slow and generate logging like crazy.

In the case where you have a single IOT without any secondary indexes, or a single table cluster, you could step through the primary/cluster key updating in chunks without having to rescan the whole table to find the fields that have not yet been updated.


I am not able to create a secondary table ... There are a couple of indexes, but none of them involve the column I am updating.

Then I suggest break the table up into chunks for processing using something you are indexing on (even if it is a single column, you can split it into ranges of values) This will do a FTS once instead of once for each chunk as in your code. You will have to live with an awful lot of redo and will wipe out your undo space too (so no flashback subsequently)


if you can add/rename/drop columns, you can do this very efficiently, but only on 11g

  • 1
    If your DBA lets you do NOLOGGING, which will invalidate hotstandbys.
    – Gaius
    Aug 30, 2011 at 15:14
  • Indeed, and a backup afterwards would be a good idea too - but this is a warehouse and nologging is a tool for warehouses Aug 30, 2011 at 15:19
  • I am not able to create a secondary table, certainly not one as large as the first, even if only temporary.
    – owook
    Aug 30, 2011 at 17:35
  • Your 11g link looked promising, but I see comments in there that for a 60m table it was still horrendously slow because of having to set the value for every row. Since my table is 10x that size, that method may not be an improvement.
    – owook
    Aug 31, 2011 at 12:04
  • @owook no, on 11g this operation is quick and does not set the value for every row "for some types of tables (for example, tables without LOB columns)". Try it on a subset of your table (create table foo as select * from bar where rownum<100000) Aug 31, 2011 at 12:19

If you're on 11g, drop the column and add it back as a NOT NULL column with a default value. This is counter-intuitive, but Oracle will store the default value in the table's definition, substituting the default value at run time.

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.