I ran a query through the SQL Optimizer and the best alternative query that it found does have drastically improved performance.
The issue is that I have never before seen the syntax used in the alternative query. I’ve asked some DBA’s and searched the web, but I still don’t have an explanation of what it means. I’m reluctant to use the alternative query if I don’t understand how it works.
Here’s a simple example:
Original Query
WHERE A.col1 = B. col1
AND A.col2 = 1
Alternative Query
WHERE A.col1 = B.col1 + 0
AND A.col2 + 0 = 1
Can anyone tell me what the “+ 0” is doing or point out a resource on the web that explains this?
The simple answer is based on what the optimizer is looking for in the query to determine how the execution plan should best be compiled to execute the query.
One of the steps it takes is to look in the where clause for specific equalities between common fields of one table to another, or to a specific literal value. It then looks at what indexes on the table reference those same fields. However if you change the equality from one field of table 1 to a “result of an expression” of table 2, the optimizer will not consider looking for an index based on that field. By adding the +0 to a numeric field or a || ‘’ to a string field, you effectively changed your field you are joining to to an expression. This in turn will cause the optimizer to look at other indexes on the table to establish a join.