Ask the Expert

Concatenating a null character affecting performance

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: