Re: Index not used when select contain *

Vladimir M. Zakharychev wrote:
> Giulio wrote:
> > Thank, Vladimir but look this:
> > select count(*) from lnk23_richiesta_sis_est;
> > COUNT(*)
> > ----------
> > 6310
> > select count(*) from lnk23_richiesta_sis_est
> > where l23_stato='C';
> > COUNT(*)
> > ----------
> > 0
> > I use statistics on table.
> > Note: I've the same problem when table
> > contain 100000 record.
> > How can i force index usage?
> >
> >
>
> First of all, please refrain from top-posting.
>
> As of the question: I think you need to collect statistics on indexes
> and indexed columns, preferably with histograms. Histograms should help
> the CBO with figuring out the most optimal access path since it will
> know distribution of values and will see that there are very little
> rows with L23_STATO='C'.
>
> To force index use, you use INDEX hint:
>
> SELECT /*+ INDEX(table_alias index_name) */ ...
> or
> SELECT /*+ INDEX(table_alias (column1,column2...)) */ ...
>
> Second form is available in 10g and Oracle will use whichever index it
> finds suitable for the column list - saves you the trouble of finding
> out the right index name, protects you from index name changes and
> allows the CBO to use a newly added index if it's more suitable for the
> task, without modifications to the hint.
>
> Hth,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com

Thank you Vladimir .With
SELECT /*+ INDEX(LNK23_RICHIESTA_SIS_EST AOS_INDEX_L23STATO) */ * FROM LNK23_RICHIESTA_SIS_EST
WHERE L23_STATO='C'
the index is ok. Received on Wed Sep 13 2006 - 01:25:41 CDT