Informix - Problem description
Problem IT30853 | Status: Closed |
OPTIMIZER INCORRECTLY SELECT MULTI INDEX SKIP SCAN FOR SINGLE VALUE, PRODUCING WRONG RESULTS | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem description: | |
QUERY: (OPTIMIZATION TIMESTAMP: 10-29-2019 16:15:05) ------ select * from t1 where cdmat = 4011 Estimated Cost: 101464 Estimated # of Rows Returned: 68925 1) informix.entmed: INDEX PATH (SKIP SCAN) (1) Index Name: informix.t1_fk_001 Index Keys (Detached): cdmat (Serial, fragments: ALL) Lower Index Filter: informix.t1.cdmat = 4011 Causes optimizer to scan the table a second time, adding wrong results. Workaround is to add AVOID_MULTI_INDEX directive. QUERY: (OPTIMIZATION TIMESTAMP: 10-29-2019 16:18:28) ------ select /*+ AVOID_MULTI_INDEX (t1) */ * from t1 where cdmat = 4011 DIRECTIVES FOLLOWED: AVOID_MULTI_INDEX( t1) DIRECTIVES NOT FOLLOWED: Estimated Cost: 197826 Estimated # of Rows Returned: 68925 1) informix.t1: INDEX PATH (1) Index Name: informix.t1_fk_001 Index Keys: cdmat (Serial, fragments: ALL) Lower Index Filter: informix.t1.cdmat = 4011 Update Statistics Distribution required for reproduction. | |
Problem Summary: | |
Local Fix: | |
Solution | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 05.11.2019 11.11.2019 11.11.2019 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |