Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I make Oracle use an index without giving it a hint?

Posted on 2012-03-14
9
Medium Priority
?
210 Views
Last Modified: 2012-09-29
Our Oracle server (10.2.0.4.0 64bit) has a schema with two tables called TEMP_ITEM_MAPPINGS and AUTOEXCLUDE_ALL where all columns are indexed separately. The former table has 254793 records and the latter has 61571 records.
I ran the following, so that all statistics are gathered on these tables:
begin
 dbms_stats.gather_table_stats(user,'TEMP_ITEM_MAPPINGS',method_opt=>'for all columns size 1');
 dbms_stats.gather_table_stats(user,'AUTOEXCLUDE_ALL',method_opt=>'for all columns size 1');
end;

Open in new window

I ran a complicated query joining these tables and it took 2 and a half hours to finish. So I reduced it to the following, rather simple query:
SELECT A.ATO_ITEM_ID, C.PRODUCT_ID
FROM
 AUTOEXCLUDE_ALL A INNER JOIN
 TEMP_ITEM_MAPPINGS C ON COMPONENT_ID = C.INVENTORY_ITEM_ID;

Open in new window

When I ran this query after running SET AUTOTRACE TRACEONLY EXPLAIN I received the following output:
Execution Plan
----------------------------------------------------------
Plan hash value: 567967328

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    | 60608 |  1479K|   105  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |                    | 60608 |  1479K|   105  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| AUTOEXCLUDE_ALL    | 60608 |   769K|    28   (8)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEMP_ITEM_MAPPINGS |   257K|  3014K|    72  (12)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("COMPONENT_ID"="C"."INVENTORY_ITEM_ID")

Open in new window

The fact that TABLE ACCESS FULL appears for each of these tables means that indexes are not used in the join. Of course I can use hints to force the query to use indexes, but I would like to know why they are not used in the first place. After all, these are rather large tables with an index on each column.
0
Comment
Question by:netformx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 37718738
there is no restriction on what data you need
> you requested all the records, so it's faster with a full table scan

>put something specific in your where
like where inventory_item_id in (10,20,30)
0
 
LVL 11

Expert Comment

by:yuching
ID: 37718765
Hi, you are retrieving the whole table data, hence it's use hash join and full table access.

Unless you specify the where condition i.e. it will use index range scan

SELECT A.ATO_ITEM_ID, C.PRODUCT_ID
FROM
 AUTOEXCLUDE_ALL A INNER JOIN
 TEMP_ITEM_MAPPINGS C ON COMPONENT_ID = C.INVENTORY_ITEM_ID;
Where COMPONENT_ID = 123
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37719145
as experts said, you have to make changes in query..!!!

There is no doubt that an index is a performance-tuning method of allowing faster retrieval of records, but we have to know clearly when the index gets picked....

For every query which sql engine executes to retrieve/fetch ouput, it will by default evaluate its cost using(Cost Based Optimizer) technique, if by using index the cost reduces then oracle automatically picks up the index, there are very rare scenarios you might need to specify an index hint to make CBO to use a different execution plan..

even they can be avoided by analyzing tables and rebuilding indexes etc.,
however in this case, as experts said, with and without index the cost involved in retrieving the output is same, so oracle has not picked up the index...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:netformx
ID: 37719148
Geert_Gruwez and yuching,

The join between the two tables is a restriction by itself. I would expect one of these tables to be accessed fully, and the other to be accessed with the index using specific values from the first table.
For comparison, I extracted the plan for the following query (which is very similar) on two other tables:
SELECT A.PARENT_ID,I.ATTRIBUTE_VALUE
FROM
 TBL_ATTACH_RULES A INNER JOIN
 TBL_ITEM_ATTRIBUTES I ON A.CHILD_ID=I.PRODUCT_ID;

Open in new window

The resulting plan was:
Execution Plan
----------------------------------------------------------
Plan hash value: 3078203673

---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                               |    15M|   508M|       |  5647  (10)| 00:00:22 |
|*  1 |  HASH JOIN            |                               |    15M|   508M|    52M|  5647  (10)| 00:00:22 |
|   2 |   INDEX FAST FULL SCAN| UK_ATTACHRULES_PARENTCHILDLOG |  2296K|    26M|       |   939   (6)| 00:00:04 |
|   3 |   TABLE ACCESS FULL   | TBL_ITEM_ATTRIBUTES           |  3005K|    63M|       |  1672   (7)| 00:00:07 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."CHILD_ID"="I"."PRODUCT_ID")

Open in new window

This is exactly what I would expect: one table accessed fully, and then an index used to access the other with specific values from the fully-accessed table.

How can I know why this did not happen with the two tables in question?
0
 
LVL 11

Expert Comment

by:yuching
ID: 37719243
Query below, is the index UK_ATTACHRULES_PARENTCHILDLOG  created on ParentId and ChildID?

SELECT A.PARENT_ID,I.ATTRIBUTE_VALUE
FROM
 TBL_ATTACH_RULES A INNER JOIN
 TBL_ITEM_ATTRIBUTES I ON A.CHILD_ID=I.PRODUCT_ID;


For 1st query, if you select the column that is index it will use index_fast_full_scan
SELECT A.COMPONENT_ID
FROM AUTOEXCLUDE_ALL A INNER JOIN
 TEMP_ITEM_MAPPINGS C ON COMPONENT_ID = C.INVENTORY_ITEM_ID
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37725955
The plan from the original post took 2 hours?  From the cost and rows involved my guess is that is the plan after you "reduced it".

As others have mentioned, just because an index exists does not mean it is faster to retrieve all your rows.  On of the tables only had 60,000 rows.  Based on the cardinal a full table scan would probably be faster.

The difference between the two posted plans is if the returned columns are part of the index being used.  If the columns are in an index, Oracle can used the nix only to satisfy the results.
0
 

Accepted Solution

by:
netformx earned 0 total points
ID: 38429091
None of these answers worked. Eventually I had to work around the problem using a third, temporary table.
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38431506
you didn't provide the original query and execution plan
>> so the solution has to be a guess

tuning by forum/mail is one of the most complicated types of tuning i know off

are your sure you aren't making assumptions when looking at the original query
>> check everything >> even the compatible parameter
0
 

Author Closing Comment

by:netformx
ID: 38446880
No answer provided so far worked.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question