QUESTION POSED ON: 29 April 2005
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?
|
