APAR status
Closed as program error.
Error description
If a value set is evaluated by a functional index, it's depending on the number of values if the functional index or a sequential scan is used. For example, a query chooses a functional index when a value set consists of four values. However, the same query with a value set of eight values chooses a sequential scan. Inserting these eight values in a temporary table, and using that temp table in a subselect, will avoid the sequential scan. Using a regular index instead of a functional index will avoid the sequential scan, too. This problem can be observed using OPTCOMPIND 2, when using OPTCOMPIND 0 the functional index is used regardless of the number of values in the IN clause.
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * All 11.50 users. * **************************************************************** * PROBLEM DESCRIPTION: * * A query with predicates with functions may have sub-optimal * * query plans if there exists an IN predicate with multiple * * values in the IN clause, such as "function(col) IN * * (<const1>, <const2>, ...)". * **************************************************************** * RECOMMENDATION: * * Upgrade to 11.50.xC8. * ****************************************************************
Problem conclusion
The problem is fixed in 11.50.xC8 in situations where there exists an index with a single index key, and the index key exactly matches the function used in the predicate. For example, create index idx on tab(function(col));
Temporary fix
Comments
APAR Information
APAR number
IC70293
Reported component name
IBM IDS ENTRP E
Reported component ID
5724L2304
Reported release
B15
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2010-07-29
Closed date
2011-01-20
Last modified date
2011-01-20
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
IBM IDS ENTRP E
Fixed component ID
5724L2304
Applicable component levels
RB15 PSN
UP
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"B15","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
20 January 2011