| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: insert and commit 1000 records at a time
Jared's suggestion improves performance but does not implement batch commits. I think most people are trying to steer you away from batch commits - for a good reason. Often people say "you have to commit every 1000 records" - where I work that exists as a standard even. I've fought long and hard to explain why this rule doesn't make sense. One approach is to consider the amount of rollback - if the row length is (perhaps) 100 bytes then a 1000 row insert is only looking to use about 10k of Rollback... How large is the rollback on your system.
Having said that, it's time to answer your question... You can create a cursor in the declare section and then use some kind of loop (while, for, etc) and then include a counter to determine when to commit. The most simple approach (and slowest) would be to insert a single record each iteration, include an if statement to check for commit points, and then exit the loop when the cursor is finished and issue a final commit.
I do tend to agree with the others though - unless someone has an exceptionally good reason to commit every 1000 records you need to show them the light and explain why an enterprise ready database like Oracle can easily chew a large amount of data. I've just finished scripts to convert some 150,000,000 row tables and commiting every 1000 records would never have worked. I compromised and settled on 20,000,000 records per commit since the conversion will occur whilst the system is still live and processing a lot of data.
MaryAnn Atkinson
<maryann_30_at_yahoo To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
.com> cc:
Sent by: Subject: Re: insert and commit 1000 records at a time
ml-errors_at_fatcity
.com
17/10/2003 06:49
Please respond to
ORACLE-L
How's that commiting every 1000 records?
> Read up on the 'append' and 'nologging' first.
???
>
>
>
>
>
>
>
> Maryann Atkinson <maryann_30_at_yahoo.com>
> Sent by: ml-errors_at_fatcity.com
> 10/16/2003 08:54 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject: insert and commit 1000 records at a time
>
>
> I have 2 tables, Rqmt and Resource, same structure.
>
> I need to take all almost-one-million records from Rqmt and
> insert them to Resource. So far this worked ok:
>
> DECLARE
> RowCount NUMBER := 0;
>
> BEGIN
> SELECT Count(*)
> INTO RowCount
> FROM RQMT;
>
> IF RowCount > 0 THEN
>
> INSERT INTO RESOURCE
> SELECT Resource_Id, Classification
> FROM RQMT;
>
> RowCount := SQL%RowCount;
>
> DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
> Rows
> transitioned.');
> COMMIT;
> ELSE
> DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
> transitioned.');
> END IF;
>
> EXCEPTION
> WHEN OTHERS THEN
> Raise;
> END;
> /
>
>
> But now I need to commit every 1000 records. Any suggestions as to
> what would be the best way? I dont think ROWNUM would help here,
> because it would pick the same 1000 records every time, causing
> primary key violation...
>
>
> thx
> maa
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Maryann Atkinson
> INET: maryann_30_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: maryann_30_at_yahoo.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: mrichard_at_transurban.com.au Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Oct 16 2003 - 19:04:25 CDT
 |
 |