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
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With