Toad World® Forums

Unknown syntax in alternative query


#1

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?


#2

Here is a detail descussion in my blog

How to Control Two Tables Join Path?

http://www.toadworld.com/BLOGS/tabid/67/EntryID/47/Default.aspx

Thanks,

Richard


#3

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.


#4

Thanks for filling in more information, Rich. It is a very good write-up.

Alex

Message was edited by: Alex Luk


#5

Excellent! I now understand what this does. Thanks for the help.