|
Problem IC79170
|
Status: Geschlossen |
DB2 SQL compiler might not choose an optimal order for performing outer joins |
| Produkt: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problembeschreibung: |
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-Zusammenfassung: |
****************************************************************
* 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 |
| verfügbare FixPacks: |
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
|
| Lösung |
Upgrade to DB2 Version 9.5 Fix Pack 9. |
| Workaround |
keiner bekannt / siehe Local-Fix |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 11.10.2011
05.06.2012
05.06.2012 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
9.5.FP9
|
| Problem behoben lt. FixList in der Version |
| 9.7.0.6
|
 |
