Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

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

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
netformx
Asked:
netformx
  • 3
  • 2
  • 2
  • +2
1 Solution
 
Geert GruwezOracle dbaCommented:
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
 
yuchingCommented:
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
 
Wasim Akram ShaikCommented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
netformxAuthor Commented:
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
 
yuchingCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
netformxAuthor Commented:
None of these answers worked. Eventually I had to work around the problem using a third, temporary table.
0
 
Geert GruwezOracle dbaCommented:
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
 
netformxAuthor Commented:
No answer provided so far worked.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now