Re: How to make deletes faster.
You should consider doing a create newtable as select with the oposite conditions as your delete, followed by a truncate/drop, exchange partition or whatever. Delete is really hard against the undo segments. /Bjørn. On Thursday 21 February 2002 23:13, you wrote: Hello All, I have a non-partitioned table with 20 millions records and growing. Every night a pl/sql stored procedures deletes around 1 million rows 10,000 at a time.Currently it is taking aroung 1 hour to delete 1 million messages. Is there any way I can make deletes faster. I need good suggestions. I have already tried all the obvious init.ora parameters like make_delete_faster=true but they do not seem to work.:-) Thanks Sonia __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Bjørn Engsig, Miracle A/S http://MiracleAS.dk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Bj=F8rn=20Engsig?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: How to make deletes faster.
[Bjørn Engsig] You should consider doing a create newtable as select with the oposite conditions as your delete, followed by a truncate/drop, exchange partition or whatever. Delete is really hard against the undo segments. Anyway to lessen that? like nologging on the delete itself, putting the table into nologging during the delete (if that's an option, which it probably isn't), etc? -- James Manning [EMAIL PROTECTED] GPG Key fingerprint = B913 2FBD 14A9 CE18 B2B7 9C8E A0BF B026 EEBB F6E4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Manning INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: How to make deletes faster.
e are in the process of partitioning but it mighttake couple of weeks to implement that optionThanksSonia P.--- "Aponte, Tony" [EMAIL PROTECTED] wrote: How are you selecting the rows to be deleted? Is it in one cursor driving a loop with incremental commits or is it done via batch cycles of 10,000-row delete ... from ...where commit; delete ... from ...where commit; .? Tony Aponte -Original Message- From: sonia pajerowski [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 5:13 PM To: Multiple recipients of list ORACLE-L Subject: How to make deletes faster. Hello All, I have a non-partitioned table with 20 millions records and growing. Every night a pl/sql stored procedures deletes around 1 million rows 10,000 at a time.Currently it is taking aroung 1 hour to delete 1 million messages. Is there any way I can make deletes faster. I need good suggestions. I have already tried all the obvious init.ora parameters like make_delete_faster=true but they do not seem to work.:-) Thanks Sonia __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sonia pajerowski INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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!?Yahoo! Sports - Coverage of the 2002 Olympic Gameshttp://sports.yahoo.com--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: sonia pajerowski INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: How to make deletes faster.
Is it replicated? Any indexes? -Original Message- Sent: Thursday, February 21, 2002 5:13 PM To: Multiple recipients of list ORACLE-L Hello All, I have a non-partitioned table with 20 millions records and growing. Every night a pl/sql stored procedures deletes around 1 million rows 10,000 at a time.Currently it is taking aroung 1 hour to delete 1 million messages. Is there any way I can make deletes faster. I need good suggestions. I have already tried all the obvious init.ora parameters like make_delete_faster=true but they do not seem to work.:-) Thanks Sonia __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sonia pajerowski INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: How to make deletes faster.
How'bout 1) add a char(1) column...call it DELETE_ME and allow NULLS 2) index the DELETE_ME column 3) every night run an update like UPDATE yourbigwhackintable SET DELETE_ME = 'Y' WHERE the row satisfies your delete condition 4) DELETE FROM yourbigwhackintable WHERE DELETE_ME = 'Y' This is assuming of course you have free reign over the app as far as adding a column and the index. The index won't store the NULLs of the non-deleteable rows if you were worrying about index size BTW. Note..this technique is not 'mine'...I got it from a book (can't remember which one!) so your mileage may vary! Cheers Jeff Herrick Jeff Herrick Associates On Thu, 21 Feb 2002, sonia pajerowski wrote: Hello All, I have a non-partitioned table with 20 millions records and growing. Every night a pl/sql stored procedures deletes around 1 million rows 10,000 at a time.Currently it is taking aroung 1 hour to delete 1 million messages. Is there any way I can make deletes faster. I need good suggestions. I have already tried all the obvious init.ora parameters like make_delete_faster=true but they do not seem to work.:-) Thanks Sonia __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sonia pajerowski INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Jeff Herrick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: How to make deletes faster.
that init.ora parm is an undocumented one, i'm surprised your database even came up. its _make_delete_faster=true, kinda like _make_sql_run_faster. joe sonia pajerowski wrote: Hello All, I have a non-partitioned table with 20 millions records and growing. Every night a pl/sql stored procedures deletes around 1 million rows 10,000 at a time.Currently it is taking aroung 1 hour to delete 1 million messages. Is there any way I can make deletes faster. I need good suggestions. I have already tried all the obvious init.ora parameters like make_delete_faster=true but they do not seem to work.:-) Thanks Sonia __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Joe Testa, Oracle DBA Nothing new to put here, hmm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: How to make deletes faster.
Sonia - Have you considered Oracle's Partitioning Option? Since you mention that your table is non-partitioned, I assume you have considered this. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 21, 2002 4:13 PM To: Multiple recipients of list ORACLE-L Hello All, I have a non-partitioned table with 20 millions records and growing. Every night a pl/sql stored procedures deletes around 1 million rows 10,000 at a time.Currently it is taking aroung 1 hour to delete 1 million messages. Is there any way I can make deletes faster. I need good suggestions. I have already tried all the obvious init.ora parameters like make_delete_faster=true but they do not seem to work.:-) Thanks Sonia __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sonia pajerowski INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: How to make deletes faster.
I have only one cursor which selects the max sequence number and all the records before that sequence number are deleted. I was just wondering if oracle 9i has a truncate like option to delete records with nologging option. We are in the process of partitioning but it might take couple of weeks to implement that option Thanks Sonia P. --- Aponte, Tony [EMAIL PROTECTED] wrote: How are you selecting the rows to be deleted? Is it in one cursor driving a loop with incremental commits or is it done via batch cycles of 10,000-row delete ... from ...where commit; delete ... from ...where commit; .? Tony Aponte -Original Message- From: sonia pajerowski [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 5:13 PM To: Multiple recipients of list ORACLE-L Subject: How to make deletes faster. Hello All, I have a non-partitioned table with 20 millions records and growing. Every night a pl/sql stored procedures deletes around 1 million rows 10,000 at a time.Currently it is taking aroung 1 hour to delete 1 million messages. Is there any way I can make deletes faster. I need good suggestions. I have already tried all the obvious init.ora parameters like make_delete_faster=true but they do not seem to work.:-) Thanks Sonia __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sonia pajerowski INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sonia pajerowski INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
