Please can anybody tell me if oracle considers index if decode is used?
To explain myself, in my where clause I have one condition
tab1.col1|| '-' ||tab1.col2 = decode(tab2.col1,'ALL',tab1.col1|| '-' ||tab1.col2 ,tab2.col1).
Now functional index is created on tab1.col1|| '-' ||tab2.col2.When I just compare
tab1.col1|| '-' ||tab2.col2 = tab2.col1 index is considered but if decode is used it is going for full table scan.
(tab2.col1 contain concatenated values).
This is degrading my query performance as it is my table contains crores of records.
Any help
Thanks in Advance!