| 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: Commit 1000 at a time, thats what I ended up doing
MaryAnn,
That was my original suggestion to you (although my typo in the rownum query probably caused confusion). What you have done is a fairly safe way to update in batches - the query is fairly simple and should never have rollback issues although it will waste some time rescanning the table to find new rows to update (again - as I originally mentioned).
An index on the column to be updated may help the query identify those rows which require updating - depending on whether the original value is null or something different and depending on whether you use bitmap indexes... Normal indexes won't help Oracle find NULL rows since they are excluded from the index.
Since you originally mentioned that you need to update 100,000 rows of a 1,000,000 table and can only update 1,000 rows per commit then it would stand to reason that you will scan the table 100 times at most. The initial scans will be quite fast (assuming no index) the later scans will have to traverse the table further and further before finding rows to update. My gut feeling though is that the loop will complete relatively fast - depending on the hardware available and your definition of "fast".
You could save the final scan by changing the following:
rc_binary_integer := 1; becomes rc binary_integer := 1000; while rc != 0 loop becomes while rc = 1000 loop
Of course this will save the final (worst) scan out of 100 only so the gain won't be much. Other than that standard tuning principals will apply, but before spending a lot of effort tuning I'd see if it meets your needs.
Regards,
Mark.
MaryAnn Atkinson
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
.com> cc:
Sent by: Subject: Re: Commit 1000 at a time,
thats what I ended up doing
[EMAIL PROTECTED]
.com
10/07/2003 05:29
Please respond to
ORACLE-L
> IF you are looking to break up the update into more 'manageable'
> pieces, here is a thought...
>
> UPDATE EMP2
> SET GENDER = 'F'
> WHERE GENDER = ' '
> AND ROWNUM <= 100;
Yes, that was exactly my objective, to update a large number of records and commit them 1000 at a time. Here is what I did:
DECLARE
RC BINARY_INTEGER := 1;
BEGIN
WHILE RC != 0 LOOP
UPDATE EMP3
SET GENDER = 'F'
WHERE GENDER = ' '
AND ROWNUM <= 1000;
RC := SQL%ROWCOUNT;
COMMIT;
END LOOP;
If anyone has a better way please let me know, you'are all very helpful.
thx
maa
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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 Wed Jul 09 2003 - 17:40:43 CDT
 |
 |