Oracle not using index when joining

Question

I am very new to index and explain plans, so please bear with me! I am trying to tune a query but I am having issues.

I have two tables:

SKU
------
SKUIDX (Unique index)
CLRIDX (Index)
..
..

IMPCOST_CLR
-----------
ICCIDX (Unique index)
CLRIDX (Index)
...
..

When I do a select * from SKU where clridx = 122, I can see that it is using the index in the explain plan (it says TABLE ACCESS.. INDEX, it says the index name under OBJECT_NAME and the options is RANGE SCAN).

Now, when I try to join on the same field, it doesn't appear to use the index (it says TABLE ACCESS.. HASH JOIN and under options, it says FULL).

What should I be looking for to try and see why it isn't using the index? Sorry, I'm not sure what commands to type to show this so please let me know if you need more information.

Examples:
1st query:

  SELECT
    *
  FROM
    AP21.SKU
  WHERE
    CLRIDX = 100

2nd query:

  SELECT
    *
  FROM
    AP21.IMPCOST_CLR
  WHERE
    CLRIDX = 100

3rd query:

  SELECT
    *
  FROM
    AP21.SKU
  INNER JOIN
    AP21.IMPCOST_CLR ON
    IMPCOST_CLR.CLRIDX = SKU.CLRIDX

Solution 1

Look at this query:

SELECT
    *
FROM
    AP21.SKU
INNER JOIN
    AP21.IMPCOST_CLR ON
    IMPCOST_CLR.CLRIDX = SKU.CLRIDX

It has no additional predicates. So you are joining all the rows in SKU to all the rows in IMPCOST_CLR. Furthermore you are selecting all the columns from both tables.

This means Oracle has to read the entirety of both tables. The most efficient way of doing this is to use Full Table Scan, to scoop up all the rows in multi-block reads, and use hashing to match the values of the joining.

Basically, it's a set operation, which is what SQL does very well, whereas indexed reads are more RBAR. Now, if you altered the third query to include an additional predicate, such as

WHERE SKU.CLRIDX = 100

you would most likely see the access path revert to INDEX RANGE SCAN . Because you're only selecting a comparative handful of rows, so the indexed read is the more efficient path once again.

---

"The query im trying to tune is hundreds of much longer, but breaking it down and taking it step by step! "

This is a good technique but you need to understand how the Oracle optimzer works. There's lots of information in Explain Plan. Find out more. Pay attention to the value in the Rows column for each step. That tells you how many rows the Optimizer expects to get from the operation. You will see very different values for the first two queries compared to the third.

Solution 2

Now, when I try to join on the same field, it doesn't appear to use the index (it says TABLE ACCESS.. HASH JOIN and under options, it says FULL).

It's because HASH JOIN doesn't use (need) indexes on the join predicates:

http://use-the-index-luke.com/sql/join/hash-join-partial-objects