Re: Need help identifying why my query is not using a unique index efficiently

From: Nigel Thomas <nigel_cl_thomas_at_yahoo.com>
Date: Tue, 24 Apr 2007 15:27:21 -0700 (PDT)
Message-ID: <885340.12186.qm@web58712.mail.re1.yahoo.com>

Tony

The answer is in your question. The subquery is correlated. The optimizer has to find all ITEMS (by company) - only then can it find all matching PARTS (dipping into the PART_IDX index to satisfy the subquery filter).

You could reformulate the query to make it uncorrelated, and so drive from the subquery: explain plan for
select *
  from item i
where (i.company , i.part_id) IN

                   (select company, part_id
                      from part p
                     where p.company=:b1
                       and part_name=:b2);


Plan hash value: 932036950                                                                                               
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT             |          |     1 |    95 |     2   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |          |     1 |    95 |     2   (0)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| PART     |     1 |    43 |     1   (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN         | PART_IDX |     1 |       |     1   (0)| 00:00:01 | 


|   4 |   TABLE ACCESS BY INDEX ROWID| ITEM     |     1 |    52 |     1   (0)| 00:00:01 |

|*  5 |    INDEX UNIQUE SCAN         | ITEM_PK  |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   3 - access("P"."COMPANY"=TO_NUMBER(:B1) AND "PART_NAME"=:B2)                  
   5 - access("I"."COMPANY"=TO_NUMBER(:B1) AND "I"."PART_ID"="PART_ID")     

I don't have Jonthan Lewis's CBO book handy to see whether there are any circumstances for your query to be automatically transformed into mine... and which one is 'better' will depend on the relative statistics in real life.

Regards Nigel

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 24 2007 - 17:27:21 CDT