This content has been marked as final. Show 7 replies
why not just update statement as the column is to be updated with another value, why do you want to do bulk collect and all?
its taking too long of a time. not sure when it will complete. was looking if there is any other way.
checkout below article by Vivek Sharma, if you are using 11g you can use this database feature. If you have license you can also try parallel dml option.
I think the recommended way to do this kind of update is to create a new table via CTAS with nologging, and maybe parallel, then drop the old table and rename the new one - search Asktom for more details.
Edited by: BrendanP on 07-Dec-2012 06:12
However, if the update is just on a non-indexed, not null, one character-field I'm not sure if the benefits would be as great as in more general updates - might be worth trying though.
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6407993912330: "If I had to update millions of records I would probably opt to NOT update."
If you have a maintainance window to do this change - and no other DML has to be done at the same time - CTAS is much faster than the update operation, because you can skip the massive undo (and redo for undo) creation.
I suspect this isn't this case, but if all records are N and you need to move to Y, and you have the right version and constraints don't stop you...
What I would do would be this:
Add a column using setting the default to the new value. This will be nigh instant as it is dictionary only operation.
Rename the two columns to flip them over then later mark the old one unused/drop it.
However, there's a lot of "if" in that. BUT it can be a handy technique if you are able (and will be a damned sight faster than updating millions of rows).
Edited by: mrk on Dec 7, 2012 3:05 PM