Re: Massive deletes & performance

I've been in the same position myself, and with tables well over 100 million rows. I've read the replies you got, and I wouldn't really recommend any of them, as they all take too much time/effort. Still, they may be worth a try.

The saving of the rowid's will give you a pretty fast delete, but it's usually best to also drop the primary key once you've stored the rowid's, drop the rows based on the rowids, and then recreate the primary key (index) afterwards. To be honest, you are hitting so much of this table you may be better off full-scanning it. Seriously.

The above (I've found in testing) can improve performance by a factor of 2. Obviously you'll have to *test you own system* to get real figures for it.

For my stuff, the improvement still wasn't enough, and the tables were growing. So I switched to using partitioned tables in Oracle8 (look at partitioned views in Oracle7). Not sure if that is an option for you? Anyway, a delete that was initially taking 12 hours came down to 6.5 hours using the 'rowid' idea listed above. Using a 'truncate' on a paritioned table brought this down to around *15 seconds* - a pretty good improvement. :-) Maybe worth a look. Again, I recommend dropping indexes beforehand and rebuilding them afterwards.

Regards,

MotoX.

tomscott_at_nospam.abac.com wrote in message <360ceec4.673470774_at_news1.abac.com>...
>We have a large table (up to 50 million rows) with a very simple
>structure:
> Person_Id Number(10),
> Group_Id Number(10),
> Cell_Id Number(10)
> Primary Key(Person_Id, Group_id, Cell_id)
>
>We are "cleaning" this table using packaged procedures. Part of the
>cleaning involves removing rows that don't conform to certain business
>rules. This may mean removing up to 25% of them, or around 10 to 15
>million rows at a time. To clean the entire table, we have to run
>through hundreds of procedures with hundreds of select statements.
>
>In my preliminary testing, the more deletes I do, the worse
>performance is getting. I am doing a commit from within the package
>every 500 records.
>
>Am I having a problem with the primary key index getting so out of
>balance that it can't function correctly?
>
>Would I be better off having a 4th field that operates as a
>delete_flag and just changing the value of that field to indicate
>whether or not we want to keep the row?
Received on Mon Sep 28 1998 - 04:12:53 CDT