Recently, we got a question from a customer:
“Why does adding a ||'' to the end of a key value improve performance... in this case significantly, this query runs in 187 milliseconds regularly where without the ||'' it takes minutes to run... This is the only modification in the result set from SQL Optimizer that was made to this code block. I got this code from a developer, so I know there are other methods to improve upon here, but this one change did everything performance wise that I needed to happen and I want to understand what the ||'' does to the query.”
Let me explain the question using a simple SQL like the following:
SQL A
select *
from employee
where emp_dept in (select dpt_id
from department
where dpt_name like 'D%')
Our SQL Optimizer rewrites it like this:
SQL B
select *
from employee
where emp_dept in (select dpt_id || ’’
from department
where dpt_name like 'D%')
Most SQL tuning books or SQL tuning experts will tell you how to use or enable indexes to improve the performance of a SQL statement. Most of them will only tell you that using indexes is the way to improve SQL speed. Seldom will they tell you how to change or control the execution plan that Oracle will generate for the SQL statement. In the SQL B above, you will find that we added an empty string to the dpt_id column in the select list of the subquery. It looks like this dummy operation, dpt_id || ’’, is stupid and meaningless since it is concatenating nothing to the dpt_id column in the select list.
To explain this dummy operation, let’s have a look at the following SQL statement which is semantically equivalent to SQL A:
SQL C
select *
from employee
where exists (select ‘x’
from department
where dpt_name like 'D%'
and emp_dept=dpt_id)
If we change the SQL syntax in SQL C to the following, do you think that the execution plan of this SQL will remain the same or not?
SQL D
select *
from employee
where exists (select ‘x’
from department
where dpt_name like 'D%'
and emp_dept=dpt_id || ’’)
I think most everyone would expect that the new syntax would generate a different plan since the dpt_id index is disabled by the addition of || ’’. The driving path in SQL C which would do a full table scan on the employee table and an index search of the department table using the dpt_id column can no longer be used (assuming that it was used in the original plan) in SQL D. Therefore when you add || ’’ to a character field or + 0 to a number field, Oracle will try other plans in place of the original plan. Sometimes the new plan will be better in your database environment, and therefore your SQL will be faster.
Now, let’s take a look at both SQL A and SQL C. SQL A uses an IN clause and SQL C has been rewritten to use the EXIST clause. The Oracle internal SQL optimizer like most of commercial databases has a built-in SQL transformation algorithm. When the SQL is executed, the internal optimizer will transformed the SQL syntax to a better syntax which is known to perform better or can be more easily optimized in next stage of plan generation. Therefore, SQL A and SQL C can be treated as the same SQL when you do SQL tuning by rewriting the syntax because the internal SQL optimizer in the database will perform this transformation during its limited optimization process which takes place before the SQL statement is executed. Actually, most database internal optimizers will transform an EXIST or IN statement to a Join SQL statement internally, but to simplify our discussion, I used the EXIST example instead of using a Join SQL.
I would like to emphasize that there is no computer algorithm that can 100% predict which execution plan is the best in your database. Therefore, to test run all alternative SQL which have unique execution plans is the only way to make sure you are using the best SQL alternative, no matter whether you are tuning your SQL statements manually or using our Quest SQL Optimizer.