Re: slow query - index usage question

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. Received on Mon Mar 17 2003 - 10:18:00 CST