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]
.comcc:
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
RCBINARY_INTEGER := 1;
BEGIN
WHILE RC != 0 LOOP
UPDATE EMP3
SETGENDER = 'F'
WHERE GENDER = ' '
ANDROWNUM = 1000;
RC := SQL%ROWCOUNT;
COMMIT;
END LOOP;
END;
/
If anyone has a better way please let me know,
you'are all very helpful.
thx
maa
--- Daniel Fink [EMAIL PROTECTED] wrote:
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;
UPDATE EMP2
SET GENDER = 'F'
WHERE GENDER = ' '
AND ROWNUM = 100;
__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
--
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
