DB2 - Problem description
Problem IC83616 | 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 / 970 - 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 9.7 Fix Pack 7. * **************************************************************** | |
Local Fix: | |
available fix packs: | |
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows | |
Solution | |
First fixed in DB2 Version 9.7 Fix Pack 7. | |
Workaround | |
not known / see Local fix | |
BUG-Tracking | |
forerunner : APAR is sysrouted TO one or more of the following: IC86792 follow-up : | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 21.05.2012 14.12.2012 14.12.2012 |
Problem solved at the following versions (IBM BugInfos) | |
9.7.FP7 | |
Problem solved according to the fixlist(s) of the following version(s) | |
9.7.0.7 |