Oracle not using index when joining

Look at this query:

SELECT    *FROM    AP21.SKUINNER 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.

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