Re: Index not used when select contain *

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 Mon Sep 11 2006 - 04:55:56 CDT