Re: Optimizer problem?

Change your query to
select count(pkey) from table;

The count(*) doesn't give the optimizer any hint that it should use the index, but if you use the index key Oracle will use the index without beeing forced by hints.

Phil Tsao wrote in message <343AD21D.4C94E1B_at_technologist.com>...
>Hi,
>
>I was wondering if any one has experienced the same problem.
>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. 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.
>
>--
>Phil Tsao
>philt_at_technologist.com
>
>
Received on Thu Oct 09 1997 - 00:00:00 CDT