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
Solved

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

Posted on 2012-03-14
9
204 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 37

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
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.

 

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 76

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 37

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

790 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