|
Problem IC64371
|
Status: Closed |
THE OPTIMIZER MAY FAVOR A FULLY QUALIFIED UNIQUE INDEX WITH FEWER COLUMNS OVER A UNIQUE NOT FULLY QUALIFIED UNIQUE INDEX |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
The optimizer may favor a unique index that is fully qualified
over another unique index that is not fully qualified having
more columns. This may be true, even if the second index offers
index only access.
(Fully qualified means that all the columns of the index have
equality predicates and when specified through a join, all the
join predicates have to be between the same tables.) |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users. *
****************************************************************
* PROBLEM DESCRIPTION: *
* The optimizer may favor a unique index that is *
* fullyqualified over another unique index that is not *
* fullyqualified having more columns. This may be true, even *
* ifthe second index offers index only access.(Fully qualified *
* means that all the columns of the indexhave equality *
* predicates and when specified through a join,all the join *
* predicates have to be between the same tables.) *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Version 9.7 Fix Pack 2 or later. *
**************************************************************** |
| Local Fix: |
Build unique indexes that are a superset of smaller unique
indexes using include columns:
For Example, if the original definition was:
unique index1 (a)
unique index2 (a,b,c)
In the above example, index2 is really unique on column a, so
columns b and c are not needed to be part of the unique
definition. The second index could be defined as:
unique index2 (a) include (b,c) |
| available fix packs: |
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
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 9a 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 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in Version 9.7 Fix Pack 2. |
| Workaround |
Build unique indexes that are a superset of smaller unique
indexes using include columns:
For Example, if the original definition was:
unique index1 (a)
unique index2 (a,b,c)
In the above example, index2 is really unique on column a, so
columns b and c are not needed to be part of the unique
definition. The second index could be defined as:
unique index2 (a) include (b,c) |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 05.11.2009
25.05.2010
25.05.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP2
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
