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.
Mmm. Max sequence number. I'll go on the assumption that you are selecting the max for a column populated by a sequence.Here's one option.1) CREATE TABLE temp_table AS SELECT ROWID myrowid FROM the_active_table WHERE the_active_table.colum max_value_for_first_query2) DELETE FROM the_active_table WHERE ROWID IN (SELECT myrowid FROM temp_table)3) DROP TABLE temp_tableAnother option: 1) establish a maintenance window with users2) CREATE TABLE temp_table insert DDL in the same format as the active table except indexes, triggers and contraints3) INSERT INTO temp_table SELECT * FROM the_active_table WHERE the_active_table.colum = max_value_for_first_query4) DROP TABLE the_active_table5) RENAME TABLE temp_table to the_active_table6) recreate indexes7) recreate constraints8) recreate triggers9) recompile invalid objects10) recreate the grantsHere's a slicker option that I had posted previously: "Aponte, Tony" [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] et cc: Sent by: Subject: RE: providing 24*7 database --- [EMAIL PROTECTED] om 10/22/01 09:05 AM Please respond to ORACLE-LWe use a modified version of your duplicate schema idea. But we don't havethe objects in different schemas. We use partitioned objects so that wecan exchange the partitions with the production tables at a scheduled time.The voodoo is that we use a single range partition of MAXVALUE and allindexes are LOCAL PARTITIONED. The partitioning key doesn't really matterin this setup since we aren't using the features for its advantages, justto be able to swap data and index segments on the fly. I've attached atranscript showing the actual sequence but I'll give you a shortexplanation first:There are production tables/indexes that are used by the application,whether directly or via synonyms. There is a second set of tables with a_TEMP suffix that have duplicate structural definitions (constraints,column names and data types, etc.) The indexes also end with a _TEMP butare identical to the production ones. The only difference is that they arepartitioned tables/indexes. All partitioned objects have a single rangepartition by a bogus column. The single partition is bounded by theMAXVALUE keyword, so all of the data is contained in one partition.Now you can manipulate the _TEMP tables at your convenience withoutinterrupting the access tot he "published" objects. Once you haverefreshed your _TEMP objects and are ready to publish the new data yourwould execute a series of ALTER TABLE tablename_TEMP EXCHANGE PARTITIONTABLE tablename. That's it. No re-pointing of synonyms, revalidating ofviews/stored procs./etc. The application keeps chugging along. The nextexecution of SQL will use the published tables.HTHTony Aponte** pseudo-attachment **SQL create table x(x1 number,x2 varchar2(50));Table created.SQL create index xi1 on x(x1);Index created.SQL create table y(x1 number,x2 varchar2(50)) 2 partition by range (x1) (partition y values less than (maxvalue));Table created.SQL create index yi1 on y(x1) 2 local (partition yi1 );Index created.SQL insert into x values (1,'original data from regular table');1 row created.SQL insert into y values (2,'original data from partitioned table');1 row created.SQL commit;Commit complete.SQL select * from x; X1 X2-- -- 1 original data from regular tableSQL select * from y; X1 X2-- -- 2 original data from partitioned tableSQL alter table y exchange partition y with table x;Table altered.SQL select * from x; X1 X2-- -- 2 original data from partitioned tableSQL select * from y; X1 X2-- -- 1 original data from regular tableSQL select * from user_indexes;output snippedSQL select * from user_part_indexes;output snippedSQL alter table y exchange partition y with table x;Table altered.SQL select * from x; X1 X2-- -- 1 original data from regular tableSQL select * from y; X1 X2-- -- 2 original data from partitioned tableSQL select * from user_indexes;output snippedSQL select * from user_part_indexes;output snippedSQL drop table x;Table dropped.SQL drop table y;Table dropped.SQL spool off-Original Message-From: sonia pajerowski [mailto:[EMAIL PROTECTED]]Sent: Thursday, February 21, 2002 10:23 PMTo: Multiple recipients of list ORACLE-LSubject: RE: How to make deletes faster.I have only one cursor which selects the max sequencenumber and all the records before that sequence numberare deleted.I was just wondering if oracle 9i has a truncate likeoption to delete records with nologging option.We are in the process of
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).
