Re: slow query - index usage question

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Wed, 19 Mar 2003 09:40:14 +0100
Message-ID: <3E782CEE.2020206@science-computing.de>

Ed Stevens wrote:
> On Mon, 17 Mar 2003 16:18:00 +0000, Rob Cowell <rjc4687_at_hotmail.com>
> wrote:
>
>

>>
>>Ed Stevens wrote:
>>
>>>Platform - Ora 8.0.6 on NT4
>>>
>>>User presented me with this query:
>>>
>>>SELECT /* + INDEX(t_warranty_claim, T_WCLM_FK_WVIN) Use T_WCLM_FK_WVIN
>>>*/
>>>        DISTINCT VIN_NBR
>>>        FROM   T_WARRANTY_CLAIM
>>>        WHERE    VIN_NBR NOT IN (SELECT DISTINCT VIN_NBR FROM
>>>S_WARRANTY_VIN)
>>>     AND      VIN_NBR > :v1
>>>        ORDER BY VIN_NBR
>>>
>>
>>Do you mean a RANGE SCAN or FAST FULL SCAN? There are no WHERE
>>predicates in the sub select, how could it RANGE SCAN ?
>>The optimizer is deciding to evaluate both DISTINCTs and an ORDER BY
>>with a single SORT UNIQUE, sounds quite efficient in theory.

>
>
> Yes, I should have said a FAST FULL SCAN. In any event, we don't
> understand why the inner query was using a full table scan when the
> criteria could have been satisfied from the high-order column of one
> of the indexes.
>
>

Hello Ed,

it would be nice to have the table declaration, also how recent statistics are, data size and distribution etc. (You're really long enough on this group to know that the more detailed the information, the better the answer). ;-)

But since I did a few tests, here's what I found (and what I expected from the very beginning): If the column vin_nbr isn't declared as not null, then an index cannot be used. Besides, if it's not only a typo from posting, then the hint won't be effective because there's a space between '/*' and '+'.

Furthermore: it has been said often (by such knowledgeable people as T.Kyte) that a hint is a hint not an order. A hint adjusts factors that are taken into the calculation of the costs. So depending on the information the optimizer has, it still can (and will) decide that it's more cheaper to use a full table scan compared to an index fast full scan.

If that's the case, then I'd suggest you start looking for a document called something like 'Search for intelligence in cbo', which explains some of the parameters that influence cbo's decision when to use and index.

HTH Holger Received on Wed Mar 19 2003 - 02:40:14 CST