Updating 700 million rows to same value

Question

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:

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

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

Answer 1

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.

--edit

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)

--edit2

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

Answer 2

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.