| Update table containing millions of records [message #379778] |
Wed, 07 January 2009 23:34  |
gajini
Messages: 262
Registered: January 2006
|
Senior Member |
|
|
Hi,
I tried to update a table with 34 million records in single update,
but it has thrown snapshot too old error.
Then I tried it with following procedure to make commit frequently
so that I will not get snapshot error,
| Quote: | DECLARE
CURSOR c1 IS SELECT ROWID FROM VEHICLE;
BEGIN
--DBMS_OUTPUT.PUT_LINE(P_DUMMY_OUTPUT);
FOR i IN c1 LOOP
UPDATE /*+ parallel 8 */VEHICLE v SET OPTION_CD=(SELECT OPTION_CD FROM TRY_OPTION_CD t1
WHERE v.YEAR_ID=t1.YEAR_ID) WHERE v.ROWID=i.ROWID;
COMMIT;
END LOOP;
END;
|
but this procedure also gave the same error.
Why it is giving this error eventhough I'm commiting frequently?
what I've to modify in above procedure to run this update successfully?
Thanks..
|
|
|
|
|
|
|
|
|
|
| Re: Update table containing millions of records [message #379800 is a reply to message #379783] |
Thu, 08 January 2009 00:28  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| gajini wrote on Thu, 08 January 2009 06:47 | Then how to run this update successfully?
|
Firstly, resume whether you need to update all rows in a huge table.
Secondly, remove that ugly COMMIT from the LOOP - it is the main cause of ORA-01555. If there is a lot of updated rows, you may have to enlarge the UNDO tablespace though.
Of course, it would be much more better to leave this cursor approach and make it a single UPDATE statement. Also have a look at How to Update millions or records in a table thread on AskTom.
Just curious, what "/*+ parallel 8 */" is supposed to do in the UPDATE statement. After ignoring the fact, that this is not correct PARALLEL hint syntax, are you aware, that the UPDATE statements updates one row at one time? There is nothing to parallelize.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
