DB2 - Problem description
Problem IT26278 | Status: Closed |
INDEXES ON COLUMN-ORGANIZED TABLES ARE NOT CHOSEN FOR SOME TYPESOF SQL STATEMENTS | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
There are some situations where indexes on column-organized tables can provide significant performance improvements but they are not chosen by the optimizer. For example: create table tc1 (pk1 int not null, pk2 int not null, c1 int, c2 int, c3 int) organize by column; create unique index tc1pk on tc1 (pk1,pk2); select * from tc1 where exists ( select c1 from tc1 g where g.pk1 = tc1.pk1 group by pk1,c1 having count_big(*) >= 1000) order by pk1,pk2; Index TC1PK might've been a faster way to access table TC1 in the EXISTS subquery, depending on the data characteristics. The issue tends to occur when the potential index access occurs in sub-selects or sub-queries. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 11.1 Mod 4 Fixpack 4 or higher * **************************************************************** | |
Local Fix: | |
Use an optimization guideline to force the desired index access. | |
Solution | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 13.09.2018 27.11.2018 27.11.2018 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |