|
Problem IC70380
|
Status: Closed |
THE OPTIMIZER MIGHT CHOOSE A LESS-THAN-OPTIMAL QUERY ACCESS PLAN AFTER A RUNSTATS IS EXECUTED |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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 Summary: |
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 |
| available fix packs: |
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
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 |
Problem is first fixed in Version 9.7 Fixpack 4 |
| 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 |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 04.08.2010
29.04.2011
29.04.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
