Re: slow query - index usage question

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
>>
>> On the inner select, the table has an index built on 3 columns, the
>> high-order part of which is the column VIN_NBR. I would have expected
>> that index to be used to satisfy the SELECT DISTINCT, but instead we
>> are getting a full table scan, as shown in this explain plan:
>>
>> OPERATION OPTIONS OBJECT NAME
>> rows OPT
>> ------------------------- ---------------
>> ----------------------------------------------- ---------- ------
>> SELECT STATEMENT COST = 6
>> 1 CHOOSE
>> SORT UNIQUE NOSORT
>> 1
>> INDEX RANGE SCAN T_WCLM_FK_WVIN
>> 1
>> TABLE ACCESS FULL S_WARRANTY_VIN
>> 293185 ANALYZ
>>
>> So the question is, why would the inner select not be satisfied with
>> an index range scan?
>
>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. Received on Mon Mar 17 2003 - 14:21:54 CST