Re: Optimizer problem?

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/10/08
Message-ID: <343b4ffa.442328@www.sigov.si>#1/1

On Tue, 07 Oct 1997 17:21:50 -0700, Phil Tsao <philt_at_technologist.com> wrote:

>I have a loop to add 10,000 rows to a table one by one then another loop
>to delete each row one by one. After doing so a few times, the
>"select count(*) from table" starts doing table scan ( and physical
>read) every time.

This is perfectly normal behaviour. If you don't use hint or specify a WHERE condition indexes will *never* be used.

> I thought the index (on primary key) was corrupted due to
>the massive insert & delete and rebuilt it. It did not help.
>The only way I can force using index is to use hint or the following:
>
> select count(*) from table where pkey > -9999;
>
>Is there anything you know that causes this to happen?
>Thanks in advance.

If you noticed your "problem" because SELECT COUNT(*) FROM TABLE took unusualy long time after massive delete, then the reason for this delay is "highwater mark". HWM marks the last block that was ever occupied by table data and is not reset until you drop or truncate the table. When full table scan is performed it scans all blocks up to the HWM even if there is no records in a table at all!

>Phil Tsao
>philt_at_technologist.com

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Wed Oct 08 1997 - 00:00:00 CDT