Re: Need help identifying why my query is not using a unique index efficiently
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: Tony_Aponte@xxxxxxxxx
- Date: Tue, 24 Apr 2007 23:47:04 +0200
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@xxxxxxxxx> 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.
--
//www.freelists.org/webpage/oracle-l
--
Alberto Dell'Era
"dulce bellum inexpertis"
--
//www.freelists.org/webpage/oracle-l
Other related posts:
