Re: Index not used when select contain *

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 Received on Tue Sep 12 2006 - 05:13:21 CDT