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

Tony,

Only the first column (company) is being used as an access predicate because the CBO chose to drive (start) from the ITEMS table and at that point it has not evaluated the sub-query and does not have the part_id value.

Since the selective filters are in the sub-query, we would want to drive from the sub-query and then join back to the ITEMS table. Then it should use both Company and Part_id as access filters.

The ROWS column of the explain plan indicates that there is not much data in the tables. At small data volumes, there should not be much difference in performance, no matter where you drive from. That is probably why the CBO decided to join from the ITEMS table in this case. It would be a good idea to get a 10053 event output for your application sql to understand why the CBO is making that choice.

Rakesh Tikku

On 4/24/07, Tony Aponte <Tony_Aponte_at_jabil.com> wrote:
> 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
>
>
>

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