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: Wed, 25 Apr 2007 13:14:19 +0200
Nigel Thomas comment about query transformation prompted me
to investigate further ... this seems a case where the CBO
does not apply the "obvious" predicate transitivity rule, i.e,
that "i.company = :b1" implies that
"p.company=i.company" can be transformed into "p.company= :b1".
To test this, I've loaded your tables with "plausible" data:
insert into item (item_id, company, part_id, quantity)
select -r, mod(r-1, 100), trunc((r-1)/100), 0
from (select rownum r from dual connect by level <= 10000);
insert into part (company, part_id, part_name)
select mod(r-1, 100), trunc((r-1)/100), 'x'||mod(r-1,
100)||'.'||trunc((r-1)/100)||'.'||rp
from (select rownum r from dual connect by level <= 10000),
(select rownum rp from dual connect by level <= 10);
exec dbms_stats.gather_table_stats (user, 'item', cascade=>true,
method_opt=>'for all columns size 1', estimate_percent=>100);
exec dbms_stats.gather_table_stats (user, 'part', cascade=>true,
method_opt=>'for all columns size 1', estimate_percent=>100);
Then I've tried the two variants (10.2.0.3, plans from v$sql_plan):
variable v1 number
variable v2 varchar2(30)
exec :v1 := 0; :v2 := 'x0.0.1';
select /*+ first_rows */ * -- original
from item i
where i.company = :v1
and i.part_id = (select part_id
from part p
where p.company=i.company
and part_name=:v2)
peeked binds values: :V1 = 0, :V2 = 'x0.0.1'
peeked binds types : :V1 = number(22), :V2 = varchar2(128)
Plan hash value: 1227956558
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
5 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 12 |
3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ITEM_PK | 1 | |
2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| PART | 1 | 14 |
2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PART_IDX | 1 | |
1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("I"."COMPANY"=:V1)
filter("I"."PART_ID"=)
4 - access("P"."COMPANY"=:B1 AND "PART_NAME"=:V2)
select /*+ first_rows */ * -- manual predicate transitivity
from item i
where i.company = :v1
and i.part_id = (select part_id
from part p
where p.company=:v1
and part_name=:v2)
peeked binds values: :V1 = 0, :V1 = null, :V2 = 'x0.0.1'
peeked binds types : :V1 = number(22), :V1 = number(22), :V2 = varchar2(128)
Plan hash value: 2968900728
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 12 |
2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | ITEM_PK | 1 | |
1 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| PART | 1 | 14 |
2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PART_IDX | 1 | |
1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("I"."COMPANY"=:V1 AND "I"."PART_ID"=)
4 - access("P"."COMPANY"=:V1 AND "PART_NAME"=:V2)
And the second version, with "manual predicate transitivity" applied,
does seem to show the plan you want ( INDEX UNIQUE SCAN on
both the index columns).
Not necessary more efficient, but at least there's another option
to try on the real data.
Could also be that the CBO tried the transformation and discarded it
as less efficient (IIRC, in 10g query transformation is costed).
HTH
Alberto
On 4/25/07, Nigel Thomas <nigel_cl_thomas@xxxxxxxxx> wrote:
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
--
Alberto Dell'Era
"dulce bellum inexpertis"
--
//www.freelists.org/webpage/oracle-l
Other related posts:
