Application Design and Tuning for Performance › Search Strategies › Boolean Term Predicate

Boolean Term Predicate

By adding a redundant Boolean term predicate, you can enable DB2 to match on one column of the index. Therefore, for this WHERE clause, you can add a redundant predicate:

Before:

WHERE COL1 = WS-COL1-MIN                               
AND   ((    COL2 >= WS-COL2-MIN                       
        AND COL2 <=  WS-COL2-MAX                        
        AND COL3 >= WS-COL3-MIN                       
        AND COL3 <= WS-COL3-MAX)                     
       OR                                              
        (      COL2 > WS-COL2-MIN                   
         AND   COL2 <= WS-COL2-MAX ))    
OR    (    COL1 > WS-COL1-MIN               
       AND COL1 <= WS-COL1-MAX )

After:

WHERE (COL1 = WS-COL1-MIN                               
AND    ((    COL2 >= WS-COL2-MIN                       
         AND COL2 <=  WS-COL2-MAX                        
         AND COL3 >= WS-COL3-MIN                       
         AND COL3 <= WS-COL3-MAX)                     
        OR                                              
         (      COL2 > WS-COL2-MIN                   
          AND   COL2 <= WS-COL2-MAX ))    
     OR (     COL1 > WS-COL1-MIN               
          AND COL1 <= WS-COL1-MAX ))
AND   (    COL1 => WS-COL1-MIN               
       AND COL1 <= WS-COL1-MAX )

The redundant predicate does not affect the result of the query, but it lets DB2 match on the COL1 column.