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

From: Tony Aponte <Tony_Aponte_at_Jabil.com>
Date: Tue, 24 Apr 2007 17:04:52 -0400
Message-ID: <C6594C4B5C99CF4E9F536C038021F82B04463BBA@alfarsmsg07.corp.jabil.org>

I have a packaged application that generates a simple correlated sub-query. The execution plan uses the unique index I want but not efficiently. The access predicate information shows that only the first column (Company) is used to navigate to the starting point in the index. The second column (Part_id) has a filter applied to it even though it's equi-joined to the results of the correlated sub-query into Part.

I produced this test scenario by trimming out the irrelevant stuff. It's a simple Item and Parts relationship in a multi-company warehouse. Items are made up of Parts. Items and Parts are owned by separate companies so everything is connected by the Company column as well as IDs. Any insight would be greatly appreciated.

Tony Aponte

Here's the script and plan output:

create table item (item_id number not null , company number not null , part_id number not null , quantity number);

alter table item add constraint item_pk primary key (company,part_id);

create table part(part_id number not null, company number not null , part_name varchar2(30) not null);

create unique index part_idx on part(company,part_name);

alter table part add constraint part_fk foreign key (company, part_id) references item (company, part_id) enable validate;

explain plan for
select /*+ first_rows */ *
  from item i
 where i.company = :b1
   and i.part_id = (select part_id

                      from part p
                     where p.company=i.company
                       and part_name=:b2);

select * from table(dbms_xplan.display);

---

---

| Id  | Operation                     |  Name       | Rows  | Bytes |

Cost |

---

---

| 0 | SELECT STATEMENT | | 1 | 52 |
1|
| 1 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 52 |
1|
|* 2 | INDEX RANGE SCAN | ITEM_PK | 1 | | 2|
| 3 | TABLE ACCESS BY INDEX ROWID| PART | 1 | 43 |
1|
|* 4 | INDEX UNIQUE SCAN | PART_IDX | 1 | | 1|

---

---

Predicate Information (identified by operation id):

---

   2 - access("SYS_ALIAS_1"."COMPANY"=TO_NUMBER(:Z))
       filter("SYS_ALIAS_1"."PART_ID"= (SELECT /*+ */ "P"."PART_ID" FROM
              "PART" "P" WHERE "P"."PART_NAME"=:Z AND

"P"."COMPANY"=:B1))
   4 - access("P"."COMPANY"=:B1 AND "P"."PART_NAME"=:Z)

Note: cpu costing is off

20 rows selected.

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

Received on Tue Apr 24 2007 - 16:04:52 CDT