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

That is not the real plan - explain plan always assumes the bind variable types as varchar2 (note the TO_NUMBER(:Z) in your plan) and especially, never peeks at bind values, which almost always occurs at runtime.

If you want the real plan, you need to actually execute the statement using representative values for the binds (using bind variables of the correct type - you probably want a NUMBER for :b1) and then either fetch the real plan from v$sql_plan or get it by tracing (TKPROF).

A shortcut is to use explain plan using literals instead of the binds, that simulates bind peeking - my preference is to always use a statement as close as possible to the real one, so with the bind variables and not the literals (since eg sometimes the CBO doesn't peek at the binds).

You also need to load representative data on the tables and then collect statistics using dbms_stats, since the plan is hugely affected by the data distribution.

HTH
Al

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
>
>
>

-- 
Alberto Dell'Era
"dulce bellum inexpertis"
--
http://www.freelists.org/webpage/oracle-l