|
Problem IC79170
|
Status: Closed |
DB2 SQL compiler might not choose an optimal order for performing outer joins |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
In a query with multiple outer join operations and an IS NULL
predicate, the DB2 SQL compiler might choose such a join order
that does not apply the IS NULL predicate early. This might
result in suboptimal performance if the IS NULL predicate can
reduce the data stream. Consider the following example:
select *
from fact left outer join d1 on f1 = id1
left outer join d2 on f2 = id2
left outer join d3 on f3 = id3
left outer join d4 on f4 = id4
where d3.c31 is null;
The access plan graph generated for this query could be as
follows
RETURN
|
>HSJOIN
/---+----\
FILTER TBSCAN
| |
>HSJOIN D4
/---------+---------\
>HSJOIN TBSCAN
/--------+--------\ |
>HSJOIN TBSCAN D3
/-----+------\ |
TBSCAN TBSCAN D2
| |
FACT D1
where the IS NULL predicate is applied by FILTER operator. If
the following conditions are true, then performing the join of
the FACT table with table D3 first is the optimal join order :
1. The left most table ( LMT ) involved in a chain of outer join
operations is a base table, such as the FACT table above.
2. There is a filtering IS NULL predicate applied to a table on
the null-producing side of an outer join ( NPT ), such as the D3
table above.
3. The join between LMT and NPT is not an expanding join. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* Please see the Error Description section. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.5 Fix Pack 9. *
**************************************************************** |
| Local Fix: |
Rewrite the query such that the chain of outer joins begins with
the join between LMT and NPT |
| available fix packs: |
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Upgrade to DB2 Version 9.5 Fix Pack 9. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 11.10.2011
05.06.2012
05.06.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.FP9
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.6
|
 |
