Home » RDBMS Server » Server Administration » Update table containing millions of records
Update table containing millions of records [message #379778] Wed, 07 January 2009 23:34 Go to next message
gajini
Messages: 259
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 #379780 is a reply to message #379778] Wed, 07 January 2009 23:40 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
You have not read or followed Posting Guideline as stated in URL above.

>but this procedure also gave the same error.
>Why it is giving this error eventhough I'm commiting frequently?
More frequent COMMITs, increases ORA-01555 errors.


This is a FAQ & I am sorry to see that both SEARCH of this forum & GOOGLE are broken for you.
Re: Update table containing millions of records [message #379783 is a reply to message #379780] Wed, 07 January 2009 23:47 Go to previous messageGo to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Hi,

Then how to run this update successfully?

Thanks..
Re: Update table containing millions of records [message #379787 is a reply to message #379778] Wed, 07 January 2009 23:52 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Then how to run this update successfully?
This is a FAQ & I am sorry to see that both SEARCH of this forum & GOOGLE are broken for you.

The problem is not the SQL which throws the error.
The real source of the problem is other DML against the table being accessed by SELECT.
Re: Update table containing millions of records [message #379800 is a reply to message #379783] Thu, 08 January 2009 00:28 Go to previous message
flyboy
Messages: 1832
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.
Previous Topic: IO error while trying to put tablespace in read only mode
Next Topic: ORA 01092 during database creation.
Goto Forum:
  


Current Time: Sat Dec 10 03:16:34 CST 2016

Total time taken to generate the page: 0.08207 seconds