DB2 SQL – Perfect Way to Use Conditions in WHERE

Best rewriting SQL query methods to improve Query performance.

First write

Split predicates out if possible:

SELECT DEPTNO, DEPTNAME
FROM DEPT
WHERE (ADMRDEPT = ‘E01’
AND DEPTNAME LIKE ‘BRANCH%’)
OR (DEPTNO = ‘D01’
AND DEPTNAME LIKE ‘BRANCH%’)
can also be tried as:

Second write

SELECT DEPTNO, DEPTNAME
FROM DEPT
WHERE (ADMRDEPT = ‘E01’ OR
DEPTNO = ‘D01’)
AND DEPTNAME LIKE ‘BRANCH%’
The idea here is to see that both examples have the same logic.

Many times this will move the optimizer to indexable from non-indexable or possibly move the optimizer to choose a multi-index processing access path, or move from multi-index process.

Connecting predicates with an OR creates non-Boolean term predicates, which means neither predicate can totally eliminate a row as false—and it is usually best to stay away from those.

If one predicate is false, the other may be true. It is usually more efficient to connect predicates by Anding them together. But in this case, it gives the optimizer more choices.

When you have predicates that are connected by an ‘OR’, it is important to code the predicate that matches the most number of rows in the table first. This is because as soon as a row qualifies, predicate evaluation within the OR list stops.

Indexable and non-Indexable

When simple predicates are connected by OR logic, the resulting compound predicate will be evaluated at the higher stage 1 or 2 of the simple predicates. For example:
SELECT EMPNO, LASTNAME, SALARY
FROM EMP
WHERE WORKDEPT = ‘D01’ — Stage 1 Indexable
OR EDLEVEL <> 16 — Stage 1 Non Indexable
Because the second predicate is a stage 1 non-indexable predicate, and the other simple predicate is stage 1 indexable, the entire compound predicate becomes stage 1 non-indexable:

Indexable and Stage-2

SELECT EMPNO, LASTNAME, SALARY
FROM EMP
WHERE WORKDEPT = ‘D01’ — Stage 1 Indexable
OR YEAR(HIRE_DATE ) = 1990 — Stage 2
Because the second predicate is a stage 2 non-indexable predicate, and the other simple predicate is stage 1 indexable, the entire compound predicate becomes stage 2 non-indexable.