|
Problem IC69509
|
Status: Closed |
Inaccurate NLEAF statistic for partitioned indexes of a partitioned table may lead to non-optimal access plans. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Inaccurate NLEAF statistic for partitioned indexes of a
partitioned table may lead to non-optimal access plans.
With DB2 V9,7 indexes for a partitioned table can be created as
partitioned or non-partitioned. For partitioned indexes, the
NLEAF statistic may be inaccurate. For example, a table with the
following per-partition statistics:
select datapartitionid, numrids, nleaf from
syscat.indexpartitions where tabschema='IC69509' and
tabname='T1'
DATAPARTITIONID NUMRIDS NLEAF
--------------- -------------------- --------------------
0 1000 334
1 2000 667
2 3000 1000
3 4000 1334
4 0 1
5 0 1
may have an NLEAF of 1:
select nleaf from syscat.indexes where tabschema='IC69509' and
tabname='T1'
NLEAF
--------------------
1
An inaccurate NLEAF statistics can affect query performance from
non-optimal access plans. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users are affected *
****************************************************************
* PROBLEM DESCRIPTION: *
* Inaccurate NLEAF statistic for partitioned indexes of a *
* *
* partitioned table may lead to non-optimal access plans. *
* *
* *
* *
* With DB2 V9,7 indexes for a partitioned table can be created *
* as *
* partitioned or non-partitioned. For partitioned indexes, *
* the *
* NLEAF statistic may be inaccurate. For example, a table with *
* the *
* following per-partition statistics: *
* *
* *
* *
* select datapartitionid, numrids, nleaf from *
* *
* syscat.indexpartitions where tabschema='IC69509' and *
* *
* tabname='T1' *
* *
* *
* *
* DATAPARTITIONID NUMRIDS NLEAF *
* *
* --------------- -------------------- -------------------- *
* *
* 0 1000 334 *
* *
* 1 2000 667 *
* *
* 2 3000 1000 *
* 3 4000 1334 *
* 4 0 1 *
* *
* 5 0 1 *
* *
* *
* *
* *
* *
* may have an NLEAF of 1: *
* *
* *
* *
* select nleaf from syscat.indexes where tabschema='IC69509' *
* and *
* tabname='T1' *
* *
* *
* *
* NLEAF *
* *
* -------------------- *
* *
* 1 *
* *
* *
* *
* An inaccurate NLEAF statistics can affect query performance *
* from *
* non-optimal access plans. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 version 9.7 FP3. *
**************************************************************** |
| Local Fix: |
Manually update NLEAF of affected partitioned indexes to a more
accurate value. In the example above, a more accurate NLEAF is
the average of the non empty partitions:
UPDATE SYSCAT.INDEXES SET NLEAF=834 where tabschema='IC69509'
and tabname='T1'; |
| available fix packs: |
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
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 |
Defect has been fixed in DB2 v9.7 FP3. |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC69696
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 29.06.2010
27.09.2010
27.09.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP3
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.3
|
 |
| 9.7.0.3
|
|
