I ahve a table wager_tbl with 14000000 records., with following fields
oprt_id , gross_amount, feed_dt, flag.
As of now the value of flag is 'N', i want to update it to 'Y'.
There is no primary key in the table. Please suggest if there is any faster way to update using bulk collect and for all or someother technique.
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