Stephen, I agree with everything you suggested except #3 -- if he is deleting that many rows, Oracle will ignore the indexes and will full table scan the delete anyway. Last I heard (from Oracle University) was that if Oracle expects more than 5-8% of the table to return from the query, it will do a full table scan no matter what
Rachel --- "Karniotis, Stephen" <[EMAIL PROTECTED]> wrote: > OK. Everyone is assuming that your deletion of millions of rows may > be > expedited by simply truncating the table. Now, that would be perfect > if you > were simply emptying the table. However, if data is to remain in the > table, > then truncate is not the preferred choice. Here are some options > along with > some cautions. > > 1. PL/SQL procedure to perform delete - If you are deleting data > based on > values in another table, you could use a DELETE with a correlated > sub-query > to delete data. You could also add a counter to the PL/SQL code to > delete > 50,000 rows, commit the deletion and then continue. Unfortunately, > if you > make a mistake with the DELETE, you must recover the table from > Offline > archivedlogs. PL/SQL has never been the preferred choice for working > with > LOTS of data. > 2. CTAS - Create Table as SELECT is another alternative. If you can > create > another table with only the data you want, delete the original table > and > simply rename this new table to the old name, you may speed up the > process. > Additionally, creating the table as UNRECOVERABLE helps as it reduces > the > amount of redo data generated by Oracle. > 3. Use indexes with the delete process - If your delete process is > referencing non-indexed columns, you are performing a full table scan > for > the DELETE. Using indexes will help performance significantly > because the > amount of data scanned is significantly reduced. > 4. Traditional DELETE wit subquery - Same as PL/SQL offering except > you > cannot control the amount of data deleted between commits. > > These are four options. Good luck. > > Thank You > > Stephen P. Karniotis > Technical Alliance Manager > Compuware Corporation > Direct: (248) 865-4350 > Mobile: (248) 408-2918 > Email: [EMAIL PROTECTED] > Web: www.compuware.com > > > -----Original Message----- > Sent: Thursday, December 27, 2001 3:15 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: HOW TO MAKE DELETION FAST > > TRUNCATE TABLE :-) > > > -----Original Message----- > Sent: Thursday, December 27, 2001 3:00 PM > To: Multiple recipients of list ORACLE-L > > > Hi Gurus > How to make deletetion of millions rows faster? > Please suggest. > Thx > Seema > > > > _________________________________________________________________ > Join the world's largest e-mail service with MSN Hotmail. > http://www.hotmail.com > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Seema Singh > 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: > 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: Karniotis, Stephen > 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!? Send your FREE holiday greetings online! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
