From: krichine@my-deja.com
Subject: Re: How to 'force' optimizer to use indexes?
Date: 2000/04/04
Message-ID: <8cd32d$4kj$1@nnrp1.deja.com>#1/1
References: <8a3gkl$ig$1@nnrp1.deja.com> <8c3c9j$smc$1@nnrp1.deja.com> <8calus$cvk$4@news.chatlink.com>
X-Http-Proxy: 1.1 x25.deja.com:80 (Squid/1.1.22) for client 209.91.109.234
Organization: Deja.com - Before you buy.
X-Article-Creation-Date: Tue Apr 04 15:51:49 2000 GMT
X-MyDeja-Info: XMYDJUIDkrichine
Newsgroups: comp.databases.oracle.server
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 4.01; Windows NT)



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