Solved

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

Posted on 2012-03-14
9
197 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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:netformx
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Expert Comment

by:yuching
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
None of these answers worked. Eventually I had to work around the problem using a third, temporary table.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
Comment Utility
No answer provided so far worked.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now