Re: How to 'force' optimizer to use indexes?

In article <8c3c9j$smc$1_at_nnrp1.deja.com>, krichine_at_my-deja.com wrote:
>You request the query results to be sorted by the indexed column, which
>is why it chooses the index. The question is why doesn't it choose the
>index in your 8.0.5 box? There could be a few reasons:
>- check your SORT_AREA_SIZE, the higher it is, the less the cost of
>sorting is, as perceived by the Oracle optimizer;
>- make sure that you ran the stats on the table and that they reflect
>the true state of the data, especially the number of rows in the table.

Here's a different perspective. Indexes are used to filter data according to the comparisons in the WHERE clause, not for sorting. Look at the WHERE clause and see whether the leading columns of the index are compared. If the index is on columns A, B and C and the WHERE clause compares only columns B and C, the index can't be used.

If yes, are the columns disqualified from indexing by appearing inside functions?
TO_CHAR(MY_DATE,'YYYY-MM-DD') can't be retrieved from an index, nor can a comparison involving MY_DATE without a function since Oracle will still implicitly translate it to a character string.

If no, are you trying to retrieve more than about 20% of the rows in the table (at which point scanning is usually faster)?

If no, look at the clustering ratio. The closer it is to the number of rows in the table, the less efficient the index becomes. Ideally it should be close to the number of blocks in the table. To improve it you need to physically store the rows in the same order as specified by the index. This may adversely affect performance of other SQL addresing the table.

Paul de Anguera | "You can't write a chord ugly enough to say
Reply to:       | what you want to say sometimes, so you have to
deanguer@       | rely on a giraffe filled with whipped cream."
quidnunc.net    | - Frank Zappa