what is "0<>0" means in DB2 SQL ?

Deepak Dash · New User Joined: 03 Sep 2007 Posts: 16 Location: India

Can somebody please explain me the use of "0<>0" predicate in DB2 SQL. I found in my code.

Where .....
AND A.Copy = B.copy
OR 0<> 0

What does this mean ?

Thanks for help.

Bharath Bhat · Posted: Mon Jul 05, 2010 6:37 pm

That means only one thing, the condition is always wrong.
The right question here would be "Why do we use it?"

This is a tuning technique to make it a stage 2 predicate and to force an access path.
ex. if there are two indexes on the table and you are qualifying the sql with both the columns, the optimizer might choose any of these indexes. But, when you add OR 1=0 (or 0<>0 in this case) to one of the predicates, it will force the optimizer to take the other index.

Hope this helps.

dbzTHEdinosauer · Posted: Mon Jul 05, 2010 7:02 pm

Deepak Dash,

the easiest way to see the effect of the 0<>0 is to
take the query into spufi, run explain,
modify the query (removing the 0<>0), run another explain (different plan number),
and compare the two explain outputs.

since you omitted part of the where clause,
we really don't know the effect of the additional predicate (0<>0),
other than knowing we have a stage 2 predicate.

As Bharath Bhat said, the effect is to disable the use of the index by the optimizer
forcing either a table space scan or use of another index mentioned in the where clause (which you have omitted).

Deepak Dash · New User Joined: 03 Sep 2007 Posts: 16 Location: India

Dear Bharath Bhat & dbzTHEdinosauer,

Thank you so much for education. Appreciate your help. Thanks.