Re: Why would an index not be used if specified as a hint in a query?

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 13 Feb 2005 16:44:15 -0600
Message-ID: <u1xbjc2px.fsf@hotpop.com>

On Sat, 12 Feb 2005, niall.litchfield_at_dial.pipex.com wrote:

> "DA Morgan" <damorgan_at_x.washington.edu> wrote in message 
> news:1108185875.191030_at_yasure...

>> Craig & Co. wrote:
>>
>>> Hi,
>>>
>>> Running Oracle 8.1.7.4 on Solaris 2.8.
>>>
>>> Running explain plan, and then a query (I don't have a copy to show
>>> at this point), apparently the index is not used.
>>>
>>> I assume it is because the optimiser thinks the index is not
>>> required even though it is specified.
>>>
>>> Cheers
>>> Craig.
>>
>> A reasonable presumption.
>>
>> On the other hand it could be lack of statistics for the optimizer or
>> any one of a number of things. Small matters like the number of rows
>> in the table, the applicability of the index to the query, the
>> cardinality, and the percentage of rows to be returned could also be
>> affecting the outcome.

> 
> A reasonable presumption indeed, but an incorrect one - assuming I
> understand 'not required' correctly - equally only one of your factors
> should be relevant.
> 
> Using an INDEX hint *WILL* cause the index to be used *IF IT CAN BE* 

I thought hints lowered the cost of a particular access path. Therefore the CBO was more apt to pick it. But, that a hint was nothing more than that. I didn't think there was anyway to "expect" that a hint was going to be obeyed.

And God knows, I really think there ought to be a setting where are able to tell Oracle to take whatever path we set, no ifs/ands or buts, of course without the RBO. More of the OBO. Obey Based Optimizer. :-)

-- 
Galen deForest Boyer

Received on Sun Feb 13 2005 - 16:44:15 CST