Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is the table partitioned? –  Jack Douglas Aug 30 '11 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 '11 at 17:31
add comment

2 Answers 2

up vote 4 down vote accepted

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

share|improve this answer
1  
If your DBA lets you do NOLOGGING, which will invalidate hotstandbys. –  Gaius Aug 30 '11 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 –  Jack Douglas Aug 30 '11 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 '11 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 '11 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) –  Jack Douglas Aug 31 '11 at 12:19
show 4 more comments

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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