The UNION Clause - Saving DB2 CPU

I am about to reveal a thumb rule for performance tuning and I want you to try it out as well on your test tables to see if it works. Have you ever encountered an SQL query while performing the famous ' SQL HUNT ' with lots of open and close brackets, AND clause, OR clause and several other predicates ? If I were to post an example of such a query it would be -

SELECT * FROM TEST.TABLE WHERE
(( COL1 = :HV-01 AND COL2 = :HV-02 AND COL3 = :HV-03 )
OR
( COL1 = :Hv-01 AND Col2 <> :HV-02 AND COL3 = :HV-03 )) ;

This is a complex query to the DB2 optimizer. When I ran an explain on this query, the ACCESSTYPE returned a value of I and the MATHCOLS had a value of 0. The Index was present on COL1, COL2, COL3. The query was taking a long time to run and I was figuring out what can be done to improve the performance.

Eureka !! Something strikes my mind. If you have read my earlier post, you will find exactly a similar query that was tuned using the UNION clause. Well, why not apply the same rule to the above query and improve performance ?

The query was changed to -

SELECT * FROM TEST.TABLE WHERE
COL1= :HV-01 AND COL2= :HV-02 AND COL3= :HV-03

UNION

SELECT * FROM TEST.TABLE WHERE
COL1 = :HV-01 AND COL2 <> :HV-02 AND COL3 = :HV-03  ;

The total number of rows selected were exactly same and now comes performance - ACCESSTYPE I and MATCHCOLS value of 3.

Thumb Rule if you agree to the above fact -

Replace the open brackets, OR clause and close brackets with the UNION clause wherever possible and split the query. It works all the time for such scenarios and we can perform the SELECT operation and validate the results !. Making the query simpler for the DB2 optimizer to decide the best possible access path by using the UNION clause is a boon for the application.