slow query - index usage question

From: Ed Stevens <nospam_at_noway.nohow>
Date: Mon, 17 Mar 2003 09:44:13 -0600
Message-ID: <l4pb7vkoitnf33rnuv5tgjik345cqasl58@4ax.com>

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? Received on Mon Mar 17 2003 - 09:44:13 CST