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