Re: Massive deletes & performance

You could try this:
Copy the records you want to keep to another table

    (create table keep_these as select * from old_table unrecoverable ) You'll need to sepecify some decent storage parameters and

        a tablespace on a different set of disks to the one you're selecting from. You could also use a suitable degree of parallilism.) Drop the original table (or rename it for extra safety) Rename the new table to replace the old. This has implications on grants, dependent packages, indexes etc. which will need to be rebuilt/regranted/recompiled.

If it's faster or more practical to delete then remove any indexes you don't need and don't do it in archivelog mode. Also, when you've finished the delete rebuild ALL of the indexes on the table.

Hope this is some help

Mike Hately

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 Sun Sep 27 1998 - 21:45:20 CDT