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).
