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

> 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.

In the original message the WHERE clause did compare the leading column of an index, however, the comparison was done using LIKE '%, which effectively makes index-based filtering impossible. So far as I understand Oracle's COST-BASED optimizer, it evaluates the overall cost of retrieving query results. In the given example, the query contains ORDER BY clause, which contributes to this overall cost. The CBO tries to compare the cost of table scan plus the cost of sorting versus the cost of index-driven scan, which does not require sorting because rows will come already sorted. It appears, from the timing provided by the original author that the second alternative is more efficient, while her 8.0.5 instance chose the first, less efficient, alternative as the execution plan. Her 8.0.6 instance, on the other hand, chose the good alternative properly. Kirill

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Apr 04 2000 - 00:00:00 CDT