Original: (taking 16 mins)
SELECT DISTINCT FIRST_NAME, LAST_NAME FROM View1@db_link WHERE NUM = '14718' AND UPPER(RNAME) = 'MARKETING';Modified: (taking 1 sec)
SELECT DISTINCT FIRST_NAME, LAST_NAME FROM View1@db_link WHERE NUM = '14718' AND UPPER(RNAME)||'' = 'MARKETING';Any idea how does concatenating a null character makes so much of difference? The explain plan for both the queries are same. I think there is a FBI (function based index--UPPER(RNAME)) on the RNAME col. Appending a null character ('') to UPPER(RNAME) stops using the index. As we know indexes improves the performance, but in this case, it seems using an index is hampering the performance. I just wanted to know the reason and logic behind this. Can you clarify this?
Dig Deeper on Oracle and SQL
Related Q&A from Azim Fahmi
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.