RE: update about 100K records
Ryan is right do this: DECLARE TYPE myRef IS REF CURSOR; myCursormyRef; TYPE myType IS TABLE OF Table_Name.PKid%TYPE INDEX BY BINARY INTEGER; p_limit PLS_INTEGER := 1000; P_PKid myType; p_flag PLS_INTEGER := 0; p_BigRollbackSegmentVARCHAR2(30) := 'YOUR_BIG_RBS'; BEGIN DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT(UPPER(p_BigRollbackSegment)); OPEN myCursor FOR 'SELECT PKid FROM Table_name WHERE delete_flag <> :1' USING p_flag ; LOOP FETCH myCursor BULK COLLECT INTO P_PKid LIMIT p_limit; FOR i IN 1..P_PKid.COUNT LOOP UPDATE Table_Name SET delete_flag= p_flag WHERE PKid = P_PKid; END LOOP; COMMIT; DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT(UPPER(p_BigRollbackSegment)); EXIT WHEN myCursor%NOTFOUND; END LOOP; CLOSE myCursor ; END; The above code will commit after each 1000 record and should run very fast... Regis -Original Message- Sent: Wednesday, July 09, 2003 12:19 AM To: Multiple recipients of list ORACLE-L bulk collect the flag into a pl/sql table. forall with a limit clause and then commit after hitting each limit. this is on asktom. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, July 08, 2003 7:09 PM > I have a table of about one million records. > > About 100,000 of them have a flag which I need it set to 0. > > Because of the size of the rollback segment, I cannot > update them all and then commit, I need to do it in > sets of 1000 records. > > Do I need a cursor for this? > > I had something like that: > DECLARE > I BINARY_INTEGER; > > > Cursor MyCursor IS > SELECT * > FROM (Table_Name) > WHERE(Condition) > FOR UPDATE; > > BEGIN > I := 0; > FOR MyRec IN MyCursor LOOP > UPDATE (Table_name) > SET delete_flag = 1 > WHERE CURRENT OF MyCursor; > > I := I + 1; > IF MOD(I, 1000) = 0 THEN > COMMIT; > END IF; > > END LOOP; > END; > / > > But "FOR UPDATE" does not really work well, and at the 1000th record > when it reaches the commit, its dropping out of the loop. > > Can I use ROWNUM to update them in batches of 1000 per time? > > Thanks, > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan 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). * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regis Biassala 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 comman
Re: update about 100K records
Hi! You could easily use rownum for doing this kind of update... Just do a commit after each update. SQL> create table u (a number); Table created. SQL> alter table u modify a constraint un not null; Table altered. SQL> insert into u values (0); 1 row created. SQL> insert into u values (0); 1 row created. SQL> insert into u values (0); 1 row created. SQL> insert into u values (0); 1 row created. SQL> insert into u values (0); 1 row created. SQL> commit; Commit complete. SQL> update u set a = 1 where a = 0 and rownum <= 2; 2 rows updated. SQL> update u set a = 1 where a = 0 and rownum <= 2; 2 rows updated. SQL> update u set a = 1 where a = 0 and rownum <= 2; 1 row updated. SQL> update u set a = 1 where a = 0 and rownum <= 2; 0 rows updated. SQL> Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, July 09, 2003 2:09 AM > I have a table of about one million records. > > About 100,000 of them have a flag which I need it set to 0. > > Because of the size of the rollback segment, I cannot > update them all and then commit, I need to do it in > sets of 1000 records. > > Do I need a cursor for this? > > I had something like that: > DECLARE > I BINARY_INTEGER; > > > Cursor MyCursor IS > SELECT * > FROM (Table_Name) > WHERE(Condition) > FOR UPDATE; > > BEGIN > I := 0; > FOR MyRec IN MyCursor LOOP > UPDATE (Table_name) > SET delete_flag = 1 > WHERE CURRENT OF MyCursor; > > I := I + 1; > IF MOD(I, 1000) = 0 THEN > COMMIT; > END IF; > > END LOOP; > END; > / > > But "FOR UPDATE" does not really work well, and at the 1000th record > when it reaches the commit, its dropping out of the loop. > > Can I use ROWNUM to update them in batches of 1000 per time? > > Thanks, > 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). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
Re: update about 100K records
Maryann Atkinson wrote: > > I have a table of about one million records. > > About 100,000 of them have a flag which I need it set to 0. > > Because of the size of the rollback segment, I cannot > update them all and then commit, I need to do it in > sets of 1000 records. > > Do I need a cursor for this? > > I had something like that: > DECLARE > I BINARY_INTEGER; > > Cursor MyCursor IS > SELECT * > FROM (Table_Name) > WHERE(Condition) > FOR UPDATE; > > BEGIN > I := 0; > FOR MyRec IN MyCursor LOOP > UPDATE (Table_name) > SET delete_flag = 1 > WHERE CURRENT OF MyCursor; > > I := I + 1; > IF MOD(I, 1000) = 0 THEN > COMMIT; > END IF; > > END LOOP; > END; > / > > But "FOR UPDATE" does not really work well, and at the 1000th record > when it reaches the commit, its dropping out of the loop. > > Can I use ROWNUM to update them in batches of 1000 per time? > > Thanks, > maa MaryAnn, I am surprised by your assessment about the size of the rollback segment not allowing you to do this in one operation. Setting a flag is not a major change, the amount of rollback generated shouldn't be _that_ tremendous. Couldn't you rethink about this? Writing something complicated to accommodate the DB is a bit like pushing the grand piano when it is too far from the stool. After all, the first reason why you have a DB is to process business operations, not the reverse. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
Re: update about 100K records
I am very confident (but have not specifically tested today) that during
each iteration it will keep searching until if finds 1000 rows meeting the
update criteria. Since the new flag value is checked in the where clause
it will not update rows multiple times and will therefore search until it
find "commit_limit" rows to update. It's clunky because it involves
searching over updated records (depending on indexing) but it might solve
your problem. ROWNUM is incremented within an UPDATE for each row that
meets all criteria for update, not each row inspected - it won't increment
until it finds rows to update and therefore won't stop until it has updated
commit_limit number of rows.
The reason I haven't used it personally is that I cannot afford to
repeatedly scan the table due to volume differences... I'm currently
working on an update of 150-200 million rows, which is considerably worse
than your scenario.
Regards,
Mark.
PS: Technically you can save one iteration by checking n_rows_updated <
commit_limit in your exit condition rather than = 0 since it will only be
less than commit_limit if it ran out of rows to update. I just wanted to
keep it simple for yourself.
MaryAnn Atkinson
<[EMAIL PROTECTED]To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
.com>cc:
Sent by: Subject: Re: update about 100K records
[EMAIL PROTECTED]
.com
09/07/2003 09:59
Please respond to
ORACLE-L
Ryan's method is pretty hi-tech for me,
and he is flying above the clouds and does not want
to get down to poor little me, even after the inviation... :-)
But this one here,
--- Mark Richard <[EMAIL PROTECTED]> wrote:
> Yeah, it sounds pretty high-tech.
> Create a loop and place the following within it:
> update table
> set flag = new_value
> where condition and flag <> new_value and rownum <= commit_limit;
> n_rows_updated := SQL%ROWCOUNT;
> commit;
>
> The exit condition is when n_rows_updated = 0, ie: nothing left to
> update.
But this one here, I dont know, how are you getting to the next
set? What if one set does not have anything to update at all?
Then rows_updated will be 0, and we'll exit.
>
> This approach will cause a few more scans than the ideal option, but
> it
> might get the job done. Increasing the commit_limit value as high as
> possible will reduce the number of iterations. 1,000 rows sounds
> very
> small - even if your row length was 1,000 bytes you are only talking
> about
> 1MB of data. Have you tried all 100,000 rows at once? How about
> 10,000 at
> a time?
>
>
>
>
>
>
> MaryAnn Atkinson
>
> <[EMAIL PROTECTED]To: Multiple
> recipients of list ORACLE-L <[EMAIL PROTECTED]>
> .com>cc:
>
> Sent by: Subject: Re: update
> about 100K records
> [EMAIL PROTECTED]
>
> .com
>
>
>
>
>
> 09/07/2003 09:29
>
> Please respond to
>
> ORACLE-L
>
>
>
>
>
&g
Re: update about 100K records
Actually, we just upgraded to 8.0 I guess this lowers my options, doesnt it? I'm 30. Not quite senile. Sorry. maa --- Ryan <[EMAIL PROTECTED]> wrote: > if your in 8i you can only use one field for a bulk collect( i think > ic ant > remember). I think they fixed that in 9i. > > i meant whatever. > > i also 'think' to do a bulk collect in 8i you cant anchor the pl/sql > table. > > has to be varchar2, number, etc... im pretty sure you can do it with > a > rowid. I dont remember either. > > Im 29. Im senile. Sorry. > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, July 08, 2003 8:04 PM > > > > --- Ryan <[EMAIL PROTECTED]> wrote: > > > uhhh... this is pretty easy. > > > > > > declare > > > type mytable is table of Whatever > > > > should I really put whatever, or table%rowtype? > > > > > > > l_table mytable; > > >l_updatevalue mytable > > > > are both variables of the same type? > > > > > > > > > > begin > > > > > > select rowid, updateValue > > > > do you mean column-to-be-updated as opposed to "updatevalue"? > > > > > > > > >bulk collect into > > >l_table; > > > from table; > > > > > > now update off the rowid for your value. > > > > > > go through like 5000 records in the pl/sql table at a time. > > > > like how? Its not an array... > > > > > > > > end; > > > > > > - Original Message - > > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > > Sent: Tuesday, July 08, 2003 7:29 PM > > > > > > > > > > huh??? > > > > > > > > --- Ryan <[EMAIL PROTECTED]> wrote: > > > > > bulk collect the flag into a pl/sql table. forall with a > limit > > > clause > > > > > and then commit after hitting each limit. > > > > > this is on asktom. > > > > > > > > anything more down the earth for me please? > > > > > > > > thx > > > > maa > > > > > > > > > > > > > > > > > - Original Message - > > > > > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > > > > > Sent: Tuesday, July 08, 2003 7:09 PM > > > > > > > > > > > > > > > > I have a table of about one million records. > > > > > > > > > > > > About 100,000 of them have a flag which I need it set to > 0. > > > > > > > > > > > > Because of the size of the rollback segment, I cannot > > > > > > update them all and then commit, I need to do it in > > > > > > sets of 1000 records. > > > > > > > > > > > > Do I need a cursor for this? > > > > > > > > > > > > I had something like that: > > > > > > DECLARE > > > > > > I BINARY_INTEGER; > > > > > > > > > > > > > > > > > > Cursor MyCursor IS > > > > > > SELECT * > > > > > > FROM (Table_Name) > > > > > > WHERE(Condition) > > > > > > FOR UPDATE; > > > > > > > > > > > > BEGIN > > > > > > I := 0; > > > > > > FOR MyRec IN MyCursor LOOP > > > > > > UPDATE (Table_name) > > > > > > SET delete_flag = 1 > > > > > > WHERE CURRENT OF MyCursor; > > > > > > > > > > > > I := I + 1; > > > > > > IF MOD(I, 1000) = 0 THEN > > > > > > COMMIT; > > > > > > END IF; > > > > > > > > > > > > END LOOP; > > > > > > END; > > > > > > / > > > > > > > > > > > > But "FOR UPDATE" does not really work well, and at the > 1000th > > > > > record > > > > > > when it reaches the commit, its dropping out of the loop. > > > > > > > > > > > > Can I use ROWNUM to update them in batches of 1000 per > time? > > > > > > > > > > > > Thanks, > > > > > > 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). > > > > > > > > > > > > > > > > -- > > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > > > -- > > > > > Author: Ryan > > > > > 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
Re: update about 100K records
if your in 8i you can only use one field for a bulk collect( i think ic ant remember). I think they fixed that in 9i. i meant whatever. i also 'think' to do a bulk collect in 8i you cant anchor the pl/sql table. has to be varchar2, number, etc... im pretty sure you can do it with a rowid. I dont remember either. Im 29. Im senile. Sorry. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, July 08, 2003 8:04 PM > --- Ryan <[EMAIL PROTECTED]> wrote: > > uhhh... this is pretty easy. > > > > declare > > type mytable is table of Whatever > > should I really put whatever, or table%rowtype? > > > > l_table mytable; > >l_updatevalue mytable > > are both variables of the same type? > > > > > > begin > > > > select rowid, updateValue > > do you mean column-to-be-updated as opposed to "updatevalue"? > > > > >bulk collect into > >l_table; > > from table; > > > > now update off the rowid for your value. > > > > go through like 5000 records in the pl/sql table at a time. > > like how? Its not an array... > > > > > end; > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Tuesday, July 08, 2003 7:29 PM > > > > > > > huh??? > > > > > > --- Ryan <[EMAIL PROTECTED]> wrote: > > > > bulk collect the flag into a pl/sql table. forall with a limit > > clause > > > > and then commit after hitting each limit. > > > > this is on asktom. > > > > > > anything more down the earth for me please? > > > > > > thx > > > maa > > > > > > > > > > > > > - Original Message - > > > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > > > Sent: Tuesday, July 08, 2003 7:09 PM > > > > > > > > > > > > > I have a table of about one million records. > > > > > > > > > > About 100,000 of them have a flag which I need it set to 0. > > > > > > > > > > Because of the size of the rollback segment, I cannot > > > > > update them all and then commit, I need to do it in > > > > > sets of 1000 records. > > > > > > > > > > Do I need a cursor for this? > > > > > > > > > > I had something like that: > > > > > DECLARE > > > > > I BINARY_INTEGER; > > > > > > > > > > > > > > > Cursor MyCursor IS > > > > > SELECT * > > > > > FROM (Table_Name) > > > > > WHERE(Condition) > > > > > FOR UPDATE; > > > > > > > > > > BEGIN > > > > > I := 0; > > > > > FOR MyRec IN MyCursor LOOP > > > > > UPDATE (Table_name) > > > > > SET delete_flag = 1 > > > > > WHERE CURRENT OF MyCursor; > > > > > > > > > > I := I + 1; > > > > > IF MOD(I, 1000) = 0 THEN > > > > > COMMIT; > > > > > END IF; > > > > > > > > > > END LOOP; > > > > > END; > > > > > / > > > > > > > > > > But "FOR UPDATE" does not really work well, and at the 1000th > > > > record > > > > > when it reaches the commit, its dropping out of the loop. > > > > > > > > > > Can I use ROWNUM to update them in batches of 1000 per time? > > > > > > > > > > Thanks, > > > > > 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). > > > > > > > > > > > > > -- > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > > -- > > > > Author: Ryan > > > > 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). > > > > > > > > > __ > > > 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:
Re: update about 100K records
--- Ryan <[EMAIL PROTECTED]> wrote: > uhhh... this is pretty easy. > > declare > type mytable is table of Whatever should I really put whatever, or table%rowtype? > l_table mytable; >l_updatevalue mytable are both variables of the same type? > > begin > > select rowid, updateValue do you mean column-to-be-updated as opposed to "updatevalue"? >bulk collect into >l_table; > from table; > > now update off the rowid for your value. > > go through like 5000 records in the pl/sql table at a time. like how? Its not an array... > > end; > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, July 08, 2003 7:29 PM > > > > huh??? > > > > --- Ryan <[EMAIL PROTECTED]> wrote: > > > bulk collect the flag into a pl/sql table. forall with a limit > clause > > > and then commit after hitting each limit. > > > this is on asktom. > > > > anything more down the earth for me please? > > > > thx > > maa > > > > > > > > > - Original Message - > > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > > Sent: Tuesday, July 08, 2003 7:09 PM > > > > > > > > > > I have a table of about one million records. > > > > > > > > About 100,000 of them have a flag which I need it set to 0. > > > > > > > > Because of the size of the rollback segment, I cannot > > > > update them all and then commit, I need to do it in > > > > sets of 1000 records. > > > > > > > > Do I need a cursor for this? > > > > > > > > I had something like that: > > > > DECLARE > > > > I BINARY_INTEGER; > > > > > > > > > > > > Cursor MyCursor IS > > > > SELECT * > > > > FROM (Table_Name) > > > > WHERE(Condition) > > > > FOR UPDATE; > > > > > > > > BEGIN > > > > I := 0; > > > > FOR MyRec IN MyCursor LOOP > > > > UPDATE (Table_name) > > > > SET delete_flag = 1 > > > > WHERE CURRENT OF MyCursor; > > > > > > > > I := I + 1; > > > > IF MOD(I, 1000) = 0 THEN > > > > COMMIT; > > > > END IF; > > > > > > > > END LOOP; > > > > END; > > > > / > > > > > > > > But "FOR UPDATE" does not really work well, and at the 1000th > > > record > > > > when it reaches the commit, its dropping out of the loop. > > > > > > > > Can I use ROWNUM to update them in batches of 1000 per time? > > > > > > > > Thanks, > > > > 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). > > > > > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Ryan > > > 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). > > > > > > __ > > 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') 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: Ryan > INET: [EMAIL PROTECTED] > > Fa
Re: update about 100K records
Ryan's method is pretty hi-tech for me, and he is flying above the clouds and does not want to get down to poor little me, even after the inviation... :-) But this one here, --- Mark Richard <[EMAIL PROTECTED]> wrote: > Yeah, it sounds pretty high-tech. > Create a loop and place the following within it: > update table > set flag = new_value > where condition and flag <> new_value and rownum <= commit_limit; > n_rows_updated := SQL%ROWCOUNT; > commit; > > The exit condition is when n_rows_updated = 0, ie: nothing left to > update. But this one here, I dont know, how are you getting to the next set? What if one set does not have anything to update at all? Then rows_updated will be 0, and we'll exit. > > This approach will cause a few more scans than the ideal option, but > it > might get the job done. Increasing the commit_limit value as high as > possible will reduce the number of iterations. 1,000 rows sounds > very > small - even if your row length was 1,000 bytes you are only talking > about > 1MB of data. Have you tried all 100,000 rows at once? How about > 10,000 at > a time? > > > > > > > MaryAnn Atkinson > > <[EMAIL PROTECTED]To: Multiple > recipients of list ORACLE-L <[EMAIL PROTECTED]> > .com>cc: > > Sent by: Subject: Re: update > about 100K records > [EMAIL PROTECTED] > > .com > > > > > > 09/07/2003 09:29 > > Please respond to > > ORACLE-L > > > > > > > > > > huh??? > > --- Ryan <[EMAIL PROTECTED]> wrote: > > bulk collect the flag into a pl/sql table. forall with a limit > clause > > and then commit after hitting each limit. > > this is on asktom. > > anything more down the earth for me please? > > thx > maa > > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Tuesday, July 08, 2003 7:09 PM > > > > > > > I have a table of about one million records. > > > > > > About 100,000 of them have a flag which I need it set to 0. > > > > > > Because of the size of the rollback segment, I cannot > > > update them all and then commit, I need to do it in > > > sets of 1000 records. > > > > > > Do I need a cursor for this? > > > > > > I had something like that: > > > DECLARE > > > I BINARY_INTEGER; > > > > > > > > > Cursor MyCursor IS > > > SELECT * > > > FROM (Table_Name) > > > WHERE(Condition) > > > FOR UPDATE; > > > > > > BEGIN > > > I := 0; > > > FOR MyRec IN MyCursor LOOP > > > UPDATE (Table_name) > > > SET delete_flag = 1 > > > WHERE CURRENT OF MyCursor; > > > > > > I := I + 1; > > > IF MOD(I, 1000) = 0 THEN > > > COMMIT; > > > END IF; > >
Re: update about 100K records
Yeah, it sounds pretty high-tech. I might have a simpler method but I haven't tested this... Create a loop and place the following within it: update table set flag = new_value where condition and flag <> new_value and rownum <= commit_limit; n_rows_updated := SQL%ROWCOUNT; commit; The exit condition is when n_rows_updated = 0, ie: nothing left to update. This approach will cause a few more scans than the ideal option, but it might get the job done. Increasing the commit_limit value as high as possible will reduce the number of iterations. 1,000 rows sounds very small - even if your row length was 1,000 bytes you are only talking about 1MB of data. Have you tried all 100,000 rows at once? How about 10,000 at a time? MaryAnn Atkinson <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> .com>cc: Sent by: Subject: Re: update about 100K records [EMAIL PROTECTED] .com 09/07/2003 09:29 Please respond to ORACLE-L huh??? --- Ryan <[EMAIL PROTECTED]> wrote: > bulk collect the flag into a pl/sql table. forall with a limit clause > and then commit after hitting each limit. > this is on asktom. anything more down the earth for me please? thx maa > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, July 08, 2003 7:09 PM > > > > I have a table of about one million records. > > > > About 100,000 of them have a flag which I need it set to 0. > > > > Because of the size of the rollback segment, I cannot > > update them all and then commit, I need to do it in > > sets of 1000 records. > > > > Do I need a cursor for this? > > > > I had something like that: > > DECLARE > > I BINARY_INTEGER; > > > > > > Cursor MyCursor IS > > SELECT * > > FROM (Table_Name) > > WHERE(Condition) > > FOR UPDATE; > > > > BEGIN > > I := 0; > > FOR MyRec IN MyCursor LOOP > > UPDATE (Table_name) > > SET delete_flag = 1 > > WHERE CURRENT OF MyCursor; > > > > I := I + 1; > > IF MOD(I, 1000) = 0 THEN > > COMMIT; > > END IF; > > > > END LOOP; > > END; > > / > > > > But "FOR UPDATE" does not really work well, and at the 1000th > record > > when it reaches the commit, its dropping out of the loop. > > > > Can I use ROWNUM to update them in batches of 1000 per time? > > > > Thanks, > > 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 PR
Re: update about 100K records
uhhh... this is pretty easy. declare type mytable is table of Whatever l_table mytable; l_updatevalue mytable begin select rowid, updateValue bulk collect into l_table; from table; now update off the rowid for your value. go through like 5000 records in the pl/sql table at a time. end; - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, July 08, 2003 7:29 PM > huh??? > > --- Ryan <[EMAIL PROTECTED]> wrote: > > bulk collect the flag into a pl/sql table. forall with a limit clause > > and then commit after hitting each limit. > > this is on asktom. > > anything more down the earth for me please? > > thx > maa > > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Tuesday, July 08, 2003 7:09 PM > > > > > > > I have a table of about one million records. > > > > > > About 100,000 of them have a flag which I need it set to 0. > > > > > > Because of the size of the rollback segment, I cannot > > > update them all and then commit, I need to do it in > > > sets of 1000 records. > > > > > > Do I need a cursor for this? > > > > > > I had something like that: > > > DECLARE > > > I BINARY_INTEGER; > > > > > > > > > Cursor MyCursor IS > > > SELECT * > > > FROM (Table_Name) > > > WHERE(Condition) > > > FOR UPDATE; > > > > > > BEGIN > > > I := 0; > > > FOR MyRec IN MyCursor LOOP > > > UPDATE (Table_name) > > > SET delete_flag = 1 > > > WHERE CURRENT OF MyCursor; > > > > > > I := I + 1; > > > IF MOD(I, 1000) = 0 THEN > > > COMMIT; > > > END IF; > > > > > > END LOOP; > > > END; > > > / > > > > > > But "FOR UPDATE" does not really work well, and at the 1000th > > record > > > when it reaches the commit, its dropping out of the loop. > > > > > > Can I use ROWNUM to update them in batches of 1000 per time? > > > > > > Thanks, > > > 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). > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Ryan > > 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). > > > __ > 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') 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: Ryan 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).
Re: update about 100K records
huh??? --- Ryan <[EMAIL PROTECTED]> wrote: > bulk collect the flag into a pl/sql table. forall with a limit clause > and then commit after hitting each limit. > this is on asktom. anything more down the earth for me please? thx maa > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, July 08, 2003 7:09 PM > > > > I have a table of about one million records. > > > > About 100,000 of them have a flag which I need it set to 0. > > > > Because of the size of the rollback segment, I cannot > > update them all and then commit, I need to do it in > > sets of 1000 records. > > > > Do I need a cursor for this? > > > > I had something like that: > > DECLARE > > I BINARY_INTEGER; > > > > > > Cursor MyCursor IS > > SELECT * > > FROM (Table_Name) > > WHERE(Condition) > > FOR UPDATE; > > > > BEGIN > > I := 0; > > FOR MyRec IN MyCursor LOOP > > UPDATE (Table_name) > > SET delete_flag = 1 > > WHERE CURRENT OF MyCursor; > > > > I := I + 1; > > IF MOD(I, 1000) = 0 THEN > > COMMIT; > > END IF; > > > > END LOOP; > > END; > > / > > > > But "FOR UPDATE" does not really work well, and at the 1000th > record > > when it reaches the commit, its dropping out of the loop. > > > > Can I use ROWNUM to update them in batches of 1000 per time? > > > > Thanks, > > 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). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Ryan > 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). __ 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') 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).
Re: update about 100K records
bulk collect the flag into a pl/sql table. forall with a limit clause and then commit after hitting each limit. this is on asktom. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, July 08, 2003 7:09 PM > I have a table of about one million records. > > About 100,000 of them have a flag which I need it set to 0. > > Because of the size of the rollback segment, I cannot > update them all and then commit, I need to do it in > sets of 1000 records. > > Do I need a cursor for this? > > I had something like that: > DECLARE > I BINARY_INTEGER; > > > Cursor MyCursor IS > SELECT * > FROM (Table_Name) > WHERE(Condition) > FOR UPDATE; > > BEGIN > I := 0; > FOR MyRec IN MyCursor LOOP > UPDATE (Table_name) > SET delete_flag = 1 > WHERE CURRENT OF MyCursor; > > I := I + 1; > IF MOD(I, 1000) = 0 THEN > COMMIT; > END IF; > > END LOOP; > END; > / > > But "FOR UPDATE" does not really work well, and at the 1000th record > when it reaches the commit, its dropping out of the loop. > > Can I use ROWNUM to update them in batches of 1000 per time? > > Thanks, > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan 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).
