Assume two tables:
TRANSACTION Primary Key: REF_NO Columns: REF_NO, TXN_DATE, ITEM_CODE, QUANTITY ITEM Primary Key: ITEM_CODE Columns: ITEM_CODE, ITEM_DESC
Request (1):
SELECT T.REF_NO, T.TXN_DATE, T.ITEM_CODE, I.ITEM_DESC, T.QUANTITY FROM TRANSACTION T, ITEM I WHERE T.ITEM_CODE = I.ITEM_CODE
Request (2):
SELECT T.REF_NO, T.TXN_DATE, T.ITEM_CODE, (SELECT ITEM_DESC FROM ITEM WHERE ITEM_CODE = T.ITEM_CODE) AS ITEM_DESC, T.QUANTITY FROM TRANSACTION T
Indexes (indices) are located in both tables as necessary.
The above version is a very simplified version of what I am doing, but the concept is the same.
I was told that (1) is more efficient due to indexes, and Explain Plan actually assumes that it is. Explain the plan for (1) shows access to the index on both tables. Explain the plan for (2) showing access to the index on the ITEM, but full access to the table on the transaction.
But my dilemma is that I run them on a very large dataset, so that the actual performance time is (2) four times faster than (1)! What are the possible reasons for this? Why should I choose (1) over (2)? (We decided to choose (2) by (1).)
source share