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

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Sat, 12 Feb 2005 11:22:19 -0800
Message-ID: <1108235973.485860@yasure>

Niall Litchfield 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* 

Let's not turn into a testosterone contest but the OP wrote: "... the index is not required even though it is specified." which indicates to me that a hint was tried. Otherwise how could you "specify" an index?

Assuming, thereupon, that the index hint was used and ignored by Oracle, it may be that the index was not appropriate for the query ... for example an incorrect column order ... it may be that the OP's query was for 85% of the rows in the table ... or, I think, the other items I mentioned. If under those conditions they are incorrect I'd appreciate knowing why.

But given that we've not seen the SQL, not seen the explain plan, and not seen the DDL for the table or index(es) I'd say we were both shooting in the dark. I would, though, like to know why my statements would be incorrect if, as I did, it is assumed a hint was used.

Thanks.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Sat Feb 12 2005 - 13:22:19 CST