Re: DBMS_STATS.DELETE_INDEX_STATS does _not_ delete index statistics ??

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:<bnm1qc$12cfrg$1_at_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 ...

Looking at the procedure in ?/rdbms/admin/dbmsstat.sql, I notice that there are possible different places to keep index stats...

jg

--
@home.com is bogus.
http://www.signonsandiego.com/news/computing/20031029-0016-wst-wildfires-technology.html