Re: Poor perfmance after large delete

From: Dave Wotton <Dave.Wotton_at_it.camcnty.gov.uk>
Date: 1997/04/25
Message-ID: <5jq2nd$8ib@dns.camcnty.gov.uk>#1/1

mwagoner_at_iac.net (Mark Wagoner) wrote:
> ... I deleted 1.4 millions rows from a table then ...
>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!
>

Oracle maintains a "high-water-mark" for tables. When you delete rows from the table, this hwm is not reset. A full table scan ( or SELECT COUNT(*) with no WHERE condition ) will read all the blocks up to the hwm, despite them being all empty - hence 3 minutes.

Truncating the table now should reset the hwm and improve your performance significantly.

Not sure why it only took 2 secs when the table was full. Did you have an index then?

Not had much experience of this myself, but I've just recently read about this in the book "Oracle:Advanced Tuning and Administration" by Oracle Press - suggest you take a look.

Dave. Received on Fri Apr 25 1997 - 00:00:00 CDT