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@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.
--
http://www.freelists.org/webpage/oracle-l




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

Other related posts: