DB2 - Problembeschreibung
Problem IC70379 | Status: Geschlossen |
THE OPTIMIZER MIGHT CHOOSE A LESS-THAN-OPTIMAL QUERY ACCESS PLAN AFTER A RUNSTATS IS EXECUTED | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 | |
Problembeschreibung: | |
To determine if you are encountering this problem, produce an EXPLAIN for the query and check for the following conditions: - there are multiple equality join predicates applied between the same two tables - the cardinality of the join is very small (a value less than 1 is a strong indicator) - the join operation chosen is a NLJOIN when a HSJOIN/MSJOIN is more likely a better choice The following is a query with a join between two tables involving multiple join predicates: SELECT ... FROM ... T1, T2, ... WHERE ... T1.A=T2.A AND T1.B=T2.B AND T1.C=T2.C | |
Problem-Zusammenfassung: | |
Problem Description: THE OPTIMIZER MIGHT CHOOSE A LESS-THAN-OPTIMAL QUERY ACCESS PLAN AFTER A RUNSTATS IS EXECUTED Problem Summary: To determine if you are encountering this problem, produce an EXPLAIN for the query and check for the following conditions: - there are multiple equality join predicates applied between the same two tables - the cardinality of the join is very small (a value less than 1 is a strong indicator) - the join operation chosen is a NLJOIN when a HSJOIN/MSJOIN is more likely a better choice The following is a query with a join between two tables involving multiple join predicates: SELECT ... FROM ... T1, T2, ... WHERE ... T1.A=T2.A AND T1.B=T2.B AND T1.C=T2.C | |
Local-Fix: | |
Collecting column group statistics on the columns involved in the join will usually solve the problem. If column group statistics or an index with the appropriate key is already available, then you are likely to be encountering this issue. The following article provides more details on how column group statistics are used by the optimizer and how they could help in these scenarios: http://www.ibm.com/developerworks/data/library/techarticle/dm-06 12kapoor/index.html | |
verfügbare FixPacks: | |
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows | |
Lösung | |
Problem is first fixed in Version 9.5 Fix Pack 8 | |
Workaround | |
Collecting column group statistics on the columns involved in the join will usually solve the problem. If column group statistics or an index with the appropriate key is already available, then you are likely to be encountering this issue. The following article provides more details on how column group statistics are used by the optimizer and how they could help in these scenarios: http://www.ibm.com/developerworks/data/library/techarticle/dm-06 12kapoor/index.html | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 04.08.2010 05.07.2011 05.07.2011 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.5.FP8 | |
Problem behoben lt. FixList in der Version | |
9.5.0.8 |