Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Need help identifying why my query is not using a unique index efficiently

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

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Wed, 25 Apr 2007 13:14:19 +0200
Message-ID: <4ef2fbf50704250414i6eebbb80jbcacb142c5954214@mail.gmail.com>


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_at_yahoo.com> 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"
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Apr 25 2007 - 06:14:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US