Re: Why index Scan in this case ?

From: Peter van Rijn <pgm.van.rijnREMOVE_at_THIShccnet.nl>
Date: Wed, 02 Nov 2005 15:50:04 +0100
Message-ID: <11mhkgt2ej5nj5c@corp.supernews.com>

Js schreef:
> Using 9.2.0.4
> =============
>
> SQL> create table t as select * from dba_objects;
>
> Table created.
>
> SQL> update t set object_type = 'TABLE';
>
> 5792 rows updated.
>
> SQL> create index ind_t on t ( object_type );
>
> Index created.
>
> SQL> analyze table t compute statistics for table for all indexes;
>
> Table analyzed.
>

Although I don't have a *real* explanation I think it's being caused by the "for table" part of the analyze. This causes column statistics not being computed.
Following your case step by step I could easily reproduce. Things changed when i omitted the "for table" clause.

hth,

Peter Received on Wed Nov 02 2005 - 08:50:04 CST