DBMS_STATS.DELETE_INDEX_STATS does _not_ delete index statistics ??

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Tue, 28 Oct 2003 16:23:28 +0100
Message-ID: <bnm1qc$12cfrg$1@ID-152732.news.uni-berlin.de>

Hello,

recently I had a phenomenon which is beyond my humble comprehension {{{8|

I have a very simple index on a non-partitioned table

   CREATE INDEX I_SUBSCRIPTORS_CLEARING_FLAG    ON SUBSCIRPTORS
(

     PROD_ID, CLEARING_FLAG
   );

When this index is just created,
but with_out_ being ANALYZEd,
an Oracle Report query needs just 30 seconds. So far so good.

When analyzed, e.g. when making new statistics for a schema, then the index is completely ignored,
no matter what CBO hints, all execution plans are worse, the query lasts 1 hour.

But now comes the strange thing:

When I do the follwing

   exec DBMS_STATS.DELETE_INDEX_STATS
(

      'myUser',
      'I_SUBSCRIPTORS_CLEARING_FLAG'

   );

in order to get rid of that statistics again, the index is _still_ being ignored ! The execution plan is the same as if the index kept statistics in a hidden place ...

User_Indexes.Last_Analyzed shows null again, so: Is DBMS_STATS.DELETE_INDEX_STATS working correctly ??? (This is a general question.)

I had expected that the index would be treated just like before, right after creation.
Now, that appears to be false.

Do I really have to drop and re-create
that special index when I run new schema statistics ? I hoped that DBMS_STATS.DELETE_INDEX_STATS would be a work-around, but it was not ...

Or are Outlines the only one solution left ? I did RTFM and expect that some execution plans may go mad when doing new statistics, but in most cases we could handle it by hints.

This strange case is the only one in my system.

P.S.: Oracle EE 8.1.7.3 on AIX 4.3.3,
Oracle Reports (about 500 in total - 1 going mad ...)

I would appreciate any comments,
TIA Jan Received on Tue Oct 28 2003 - 09:23:28 CST