Re: Optimizer problem?
Date: 1997/10/09
Message-ID: <343D166A.4CA3A6D_at_netway.at>#1/1
Phil Tsao wrote:
>
> 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
[Quoted] If you use an index as discribed in other reply(s)
it's OK.
BUT your first full table scan WILL be time-consumting because of the
delayed block-ceanout, witch means to mark the emptied block
as empty in the second access transaction.
So assume to make a full-table scan after a large delete.
-- ===================================== Norbert Ferstl DI Forsthuber GmbH Kohlbauernstr. 17 A-2630 Ternitz, AUSTRIA Tel: +43 2630 382500 Fax: +43 2630 3825014 <ferstl_at_netway.at> =====================================Received on Thu Oct 09 1997 - 00:00:00 CEST