Why is Quest SQL Optimizer adding "+0" to some of alternative SQL expressions? (4258110)

When generating SQL alternatives to optimize a particular statement, SQL Optimizer is adding either a plus or minus zero (+ 0 or - 0) to some of the conditional statements.  Why is it doing that?

This is one of the many "tricks" that SQL Optimizer uses to guide Oracle in choosing different data access paths.

The "+ 0" is a trick to help control or guide Oracle in choosing a different path to access data.  Consider the following SQL statement:

SELECT * FROM TABLE1, TABLE2 WHERE TABLE1.COLUMN1 = TABLE2.COLUMN2;

Assume that both TABLE1.COLUMN1 and TABLE2.COLUMN2 are indexed; under a NESTED LOOP operation, Oracle can choose to use the values of TABLE1.COLUMN1 to do an index search using the index on TABLE2.COLUMN2, or vice versa.  To force Oracle to choose a particular path, one trick is to make the use of an index not possible for Oracle to use and tells it to select the other index instead.  This is done by changing the condition as follows:

TABLE1.COLUMN1 + 0 = TABLE2.COLUMN2

With the "+ 0" operation added, the meaning of the condition is unchanged but it will prevent Oracle from using the index on TABLE1.COLUMN1.