Original: (taking 16 mins)
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.