Re: Poor perfmance after large delete

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 Received on Tue Apr 22 1997 - 00:00:00 CDT