Re: Index not used when select contain *

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?

Vladimir M. Zakharychev ha scritto:

> Giulio wrote:
> > I've this problem:
> >
> > i've create an index with 4 field
> > CREATE UNIQUE INDEX AOS_INDEX_LNK23_X_LAS ON LNK23_RICHIESTA_SIS_EST
> > (L23_STATO, L23_DATAORA_RICH, L23_NUM_RICHIESTA, L23_NUM_EPISODIO)
> >
> > The table contain 20 field and the primary key is:
> > PRIMARY KEY (L23_DATAORA_RICH, L23_NUM_RICHIESTA, L23_NUM_EPISODIO)
> >
> > with this: query:
> > SELECT L23_STATO, L23_DATAORA_RICH, L23_NUM_RICHIESTA, L23_NUM_EPISODIO
> > FROM GSTUSER.LNK23_RICHIESTA_SIS_EST
> > WHERE L23_STATO='C'
> > the index is used
> >
> > but with this
> > SELECT *
> > FROM GSTUSER.LNK23_RICHIESTA_SIS_EST
> > WHERE L23_STATO='C'
> > the index in not ued.
> > Please, can you tell me where is the problem?
>
> The first query can be fully answered from that unique index you
> created (provided that at least L23_STATO is constrained to be NOT
> NULL,) Oracle doesn't even need to visit the table because all selected
> columns are contained in the index itself, so the index is used and the
> table is not even touched. For the second query, index selectivity is
> probably above 5% (that is, more than 5% of rows have L23_STATO='C',)
> so the CBO figures that using the index is more expensive than
> full-scanning the table. Usually this is close to truth, but not
> always. If the index selectivity is just marginally above 5% (say, 6%
> or 7%,) forcing index use by hinting the query may yeld significant
> performance improvement. Having statistics on the table and the index
> current helps, too - without stats the CBO can't make informed
> decisions and its calculations can go very wrong. You may need to
> gather table and index stats with one of the many DBMS_STATS calls (see
> the docs for details on this if you are not familiar with the package.)
>
>
> Hth,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com
Received on Tue Sep 12 2006 - 01:15:03 CDT