One of the challenges of
using ERP applications such as Oracle or SAP is that the SQL can't be rewritten
to improve the performance. However, with the optimizer enhancements in DB2 11,
predicates can be automatically rewritten as stage 2, making them indexable.
This information is taken
from session Terry Purcell's recent presentation at the IDUG DB2 Tech
Conference in Australia. The usual disclaimers apply.
In the sample below, the
first line is SQL rewritten as stage 2. The next line is indexable:
EQUAL
Predicates
o
WHERE
YEAR(DATE_COL) = 2013 ←Stage 2
Becomes
o
WHERE DATE_COL
BETWEEN ‘2013-01-01’ and ‘2013-12-31’ ←Indexable
This rewrite will also
occur with IN, BETWEEN and RANGE predicates. Terry's presentation has more
examples.
Here are some additional
SQL-related enhancements:
Prune always
TRUE predicates: When a predicate always evaluates to TRUE, DB2
removes it from the statement so the predicate won't evaluate at execution
time. For example:
becomes
Prune always
FALSE predicates: When a statement is
written to always evaluate to FALSE, the optimizer will rewrite the SQL and
remove the predicate. In this example, ‘A’ = ‘B’ is always false, so that
predicate is removed during SQL rewrite:
becomes
DB2
11 extends the pruning to complex predicates that include AND Boolean
operations. In this example, the predicate after the OR will always be false,
so the entire predicate is removed:
- WHERE (1=1 and T1.C1 = T2.C1)
OR
(1=2 and T1.C2 = T2.C2)
becomes
The documented statement
“OR 0=1” won't be pruned because this is a documented coding tip.
CASE statements
(formerly stage 2) are now indexable: For example:
WHERE C1 =
CASE(CAST(? AS INT))
WHEN
1 THEN ‘A’
WHEN
2 THEN ‘B’
ELSE
‘C END;
CASE statement
when JOIN predicate:
- When the CASE expression must be evaluated
first.
- In the example below, join predicate is
indexable if C1 is evaluated first.
WHERE T2.C1 = CASE WHEN T1.C1 = ‘Y’
THEN T1.C2
ELSE T1.C3 END;
Predicate PUSH
DOWN:The process of predicate push-down involves the optimizer
rewriting the SQL in such a way the predicate can be evaluated sooner; this
reduces the number of rows that are evaluated in stage 2. The IBM Redbook, "DB2
10 for z/OS Performance Topics," has a detailed explanation.
DB2 11 extends predicate
push-down to non-Boolean term (OR) predicates and stage 2 predicates
(expressions). Here's an example of each:
Example 1: With non-Boolean term
(OR) predicates, this:
SELECT EMPNO,
SALARY, DEPTCOUNT
FROM EMP A ,
(SELECT
WORKDEPT, COUNT(*)
FROM EMP
GROUP BY
WORKDEPT) AS B(WORKDEPT, DEPTCOUNT)
WHERE A.WORKDEPT
= B.WORKDEPT
AND (B.WORKDEPT LIKE 'C%' OR B.WORKDEPT LIKE 'A%‘);
becomes this:
SELECT EMPNO,
SALARY, DEPTCOUNT
FROM EMP A ,
(SELECT
WORKDEPT, COUNT(*)
FROM EMP
WHERE WORKDEPT LIKE 'C%' OR WORKDEPT LIKE 'A%‘)
GROUP BY
WORKDEPT) AS B(WORKDEPT, DEPTCOUNT)
WHERE A.WORKDEPT
= B.WORKDEPT;
Example 2: With stage 2
predicates (expressions), this:
SELECT EMPNO,
SALARY, DEPTCOUNT
FROM EMP A ,
(SELECT
WORKDEPT, COUNT(*)
FROM EMP
GROUP BY
WORKDEPT) AS B(WORKDEPT, DEPTCOUNT)
WHERE
A.WORKDEPT = B.WORKDEPT
AND UPPER( B.WORKDEPT) = 'C01'
becomes this:
SELECT EMPNO,
SALARY, DEPTCOUNT
FROM EMP A ,
(SELECT
WORKDEPT, COUNT(*)
FROM EMP
WHERE UPPER( B.WORKDEPT) = 'C01'
GROUP BY
WORKDEPT) AS B(WORKDEPT, DEPTCOUNT)
WHERE
A.WORKDEPT = B.WORKDEPT
The optimizer enhancements in DB2 11 are quite impressive.
Keep in mind though that the performance gains you'll receive by upgrading will
vary depending on your SQL. However, the good news is the optimizer is now
capable of rewriting the SQL automatically. As noted, this is especially handy
when you're running package applications that don't allow for SQL coding
changes.
Connect With Us: