DB2 - Problem description
Problem IC68031 | 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 / 980 - 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. * **************************************************************** * PROBLEM DESCRIPTION: * * See above. * **************************************************************** * RECOMMENDATION: * * Upgrade to version 9.8 Fix Pack 3 or a later release that * * includes the fix. * **************************************************************** | |
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.8 Fix Pack 3 for Linux, UNIX, and Windows | |
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 : | 20.04.2010 22.12.2010 22.12.2010 |
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 |