|
Problem IC88340
|
Status: Closed |
JOIN PUSHDOWN THROUGH COMPLEX UNION ALL OPERATORS MAY NOT OCCUR IN DPF |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
Join pushdown through complex UNION ALL may not occur in DPF if
any of the inputs to the UNION ALL are OUTER JOIN operators.
Priori to DB2 version 10.1 fixpack 2, there is no capability for
DB2 to automatically push the join below the UNION to get early
filtering of the tables participating in join.
To verify if you are affected, obtain the explain output from
db2exfmt and look at the plan to determine if there are OUTER
JOINs below the UNION ALL and if the filtering joins are left
above the UNION ALL in the plan graph. Below there is execution
plan snippet, where you can see situation in subject:
1.07441e+08
HSJOIN
( 12)
8.86317e+06
3.67748e+06
/------------+-----------\
1.16703e+09 116
FILTER BTQ
(13) (29)
8.75289e+06 7.71536
3.67747e+06 1
| |
1.16704e+09 29
UNION TBSCAN
( 14) (30)
8.32995e+06 7.61313
3.67747e+06 1
/--------+--------\ |
1.1099e+09 5.71335e+07 29
>^HSJOIN >^HSJOIN TABLE: SESSION
(15) (22) ZZRF03
7.79279e+06 267330 Q16
3.45164e+06 225837
In example above, result sets of outer joins ( >^HSJOIN )
operations are not filtered by pushing down join predicate on
underlying tables. As a consequence cardinality is overestimated
and plan is suboptimal.
There are two parts required by DB2 to enable join pushdown
through complex UNION ALL operator:
1. Unique index must be defined on the session table. If unique
index is created, looking at SESSION.ZZMQ01 in the db2exfmt
output:
Schema: SESSION
Name: ZZMQ01
Type: Table
Time of creation: 2012-08-13-11.15.14.581620
...
Distinct row values: Yes
...
2. Setting the registry variable:
db2set DB2_UNION_OPTIMIZATION=ENABLE_BT_PD_COMPLEX_UA=true |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 version 10.1 Fix Pack 2 *
**************************************************************** |
| Local Fix: |
Manually rewrite the query so that the filtering joins are
pushed into each arm of the UNION ALL (ex. it can be done by
breaking up the query so that it is done as two parts,
corresponding to each leg of the UNION ALL, and creating the
view in conjunction with the join that couldn't be pushed down). |
| available fix packs: |
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
First fixed in version 10.1 Fix Pack 2 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 17.11.2012
03.01.2013
03.01.2013 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.2
|
 |
| 10.5.0.2
|
|
