Re: Poor perfmance after large delete

Andreas Baumkötter wrote:
>
> mwagoner_at_iac.net (Mark Wagoner) wrote:
>
> >We had been testing our database setup with sample data and decided to try
> >and go live. The main table had 1.4 million rows, which I deleted by doing
> >a series of DELETE FROM WHERE statements (I tried to truncate the table,
> >but Oracle said there were constraints even after I disabled them all, but
> >that is another problem). After about 2 hours the main tables were empty
> >so I went in and did a SELECT COUNT(*) to make sure. It took almost 3
> >minutes for the result to come back! It took less than 2 seconds when the
> >table was full!
>
> [some stuff deleted]
>
> Hello Mark,
>
> we have the same problem. Oracle told us that the internal hashing
> tables are cleared but the memory is still in use. That makes the
> things slow down.
>
> The only solution we've found ist to drop the table and then recreate
> it. But to do this you have to drop and recreate all dependent
> constraints and all indices too.
>
> So, as you might believe, we're not very happy with this solution. If
> you find a better one, let us know please.
>
> Ciao, Andreas
> -
> Andreas Baumkötter, PRO DV Software GmbH, Dortmund, Germany
> Email to: baumkoetter_at_prodv.de

I think dropping table is the way to go. I do this all the time. whay is missing from this discussing is to drop the table with cascade option then the table will be dropped disregarding it has any dependend tables or not.

Hope this helps.

-- 
John Chan
Manager, Data Services
RTE Asset Management (aka. Rightime Econometrics, Inc.)
1095 Rydal Rd.
Rydal, PA 19046-1711
215-572-7288 (Voice)
215-572-6254 (Fax)
Internet address:   john_at_rte-asset.com