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?

    Requires Free Membership to View

    Login

Are underlying tables that are sources for the view residing in the remote database analyzed? In that case the cost based optimized may think that using the function based index versus the index on num column is the best way to execute the query, when in reality using the index on the num column is a better choice.

This was first published in April 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top