| Latest versions | fixlist |
| 11.1.0.7 |
 |
| 10.5.0.9 |
|
| 10.1.0.6 |
|
| 9.8.0.5 |
|
| 9.7.0.11 |
|
| 9.5.0.10 |
|
| 9.1.0.12 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
DB2 - Problem description
| Problem IC86792 | Status: Closed |
OPTIMIZER UNDERESTIMATES THE NUMBER OF EXPECTED ROWS IN A SORT /GROUP BY OPERATOR, WHICH LEADS TO A BAD PERFORMING ACCESS PLAN | |
| product: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
| Problem description: | |
A sample statement having a WHERE clause as shown below will
generate a plan where the expected row count in SORT / GROUP BY
operator is drastically less compared to actual number of rows.
statement:
SELECT...
WHERE tab1.col1 = 'xyz'
GROUP BY tab2.col2 ,
tab1.col1 ,....
Note: col1 is used in the GROUP BY clause
Plan generated will show estimated row count in SORT/GROUP BY
as 3 where the actual count is 13923.
0,000176497
1
NLJOIN
( 16)
1221,82
NA
/---+----\
0,000530024 0,332997
1 1
TBSCAN IXSCAN
( 17) ( 23)
1214,24 15,1398
NA NA
| |
3 -1
5469 NA
TEMP INDEX: SCHEMA1
( 18) IDX1
1214,23 Q42
NA
|
3
5469
GRPBY
( 19)
1214,23
NA
|
3
13923
TBSCAN
( 20)
1214,23
NA
|
3
13923
SORT
( 21)
1214,23
NA
|
19109
19109
TBSCAN
( 22)
1209,64
NA
|
19109
NA
TABLE: SCHEMA1
TABX
Q5 | |
| Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 10.1 Fix Pack 2. * **************************************************************** | |
| Local Fix: | |
| available fix packs: | |
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows | |
| Solution | |
First fixed in DB2 Version 10.1 Fix Pack 2. | |
| Workaround | |
not known / see Local fix | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 25.09.2012 09.04.2013 09.04.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 |
|