|
Problem IC67983
|
Status: Closed |
QUERY PERFORMANCE CAN BE POOR WHEN JOINING TO A DATA PARTITIONED TABLE USING THE DATA PARTITIONING COLUMNS. |
| product: |
DB2 FOR LUW / DB2FORLUW / 980 - DB2 |
| Problem description: |
The DB2 Optimizer may underestimate the number of pages read
from disk when a join to a data partitioned table is using the
data partitioning columns. This can cause the optimizer to
choose a plan that will perform poorly.
This may be applicable to queries with an inner join involving a
data partitioned table or an outer join when the NULL producing
input to the join is a data partitioned table. In both cases,
the query will have equality (=) join predicates involving all
the data partitioning columns of the data partitioned table.
For the poorly performing query, obtain the output from db2exfmt
and look for a NLJOIN operator with a TBSCAN of the data
partitioned table as the right-side input. Furthermore the
estimated I/O cost of the NLJOIN will look small in comparison
to the size of the data partitioned table.
For example:
4.52747e+07
NLJOIN
( 27)
7.66338
1985 <<< I/O Cost estimate is
small given the large table
/-------+--------\
37666.1
TBSCAN
( 29)
7.64325
1985
|
4.52747e+07
DP-TABLE: DB2INST1
PARTTABLE1
Q11
When examining the operator details for the TBSCAN(29), the join
predicates are used a DP Elim predicates:
DP Elim Predicates:
-----------------
Range 1)
Start Predicate: (Q11.PARTCOL = Q10.COL1
Stop Predicate: (Q11.PARTCOL = Q10.COL1 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* See above. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Version 9.8 Fix Pack 3 or later release that *
* contains the fix. *
**************************************************************** |
| Local Fix: |
1) Create an optimization profile for the query forcing either a
different join method for the join in question or forcing the
access of the data partitioned table to use an IXSCAN of a good
index.
2) Increase the values for SYSSTAT.TABLES.NPAGES and
SYSSTAT.TABLES.FPAGES for the data partitioned table until a
different plan is chosen. |
| available fix packs: |
DB2 Version 9.8 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.8 Fix Pack 4 for AIX and Linux
DB2 Version 9.8 Fix Pack 5 for AIX and Linux
|
| Solution |
Problem was first fixed in Version 9.8 Fix Pack 3. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 16.04.2010
06.01.2011
06.01.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.8.FP3
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.8.0.3
|
 |
