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" <Stephen_Karniotis_at_compuware.com> 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: Stephen.Karniotis_at_Compuware.com
> 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: oracledbam_at_hotmail.com
>
> 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: ListGuru_at_fatcity.com (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: lhoska_at_calibresys.com
>
> 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: ListGuru_at_fatcity.com (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: Stephen_Karniotis_at_compuware.com
>
> 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: ListGuru_at_fatcity.com (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: wisernet100_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Thu Dec 27 2001 - 16:13:09 CST
