Q

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?
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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close