Informix - Problem description
Problem IT32532 | Status: Closed |
QUERY PLAN WITH BEST ESTIMATED COST THAT ALSO RUNS THE QUICKEST IS NOT CHOSEN | |
product: | |
INFORMIX SERVER / 5725A3900 / B70 - IDS 11.70 | |
Problem description: | |
Consider the following query (tab1 is a temp table) select distinct tab2.c1, tab2.sn, tab2.c2||" "||tab2.c3||":"||tab2.c4||" "|| tab2.c5 as OUT1, tab2.c15, t.c14t * t.c8 / abs(t.c8) OUT2, t.c13 * t.c8 / abs(t.c8) OUT3, case when (tc.tc10='S') then tab2.c6 else sd.c9 end as OUT4, tc.c12, tab1.c11, tc.c10 from tab1, tab2, tab3 t, tab4 tc, outer tab5 sd where tab2.c7 = t.c7 and tc.c7 = t.c7 and tab2.c1 = t.c1 and tab1.c1 = tab2.c1 and tab2.c1 = sd.c1 and t.c8 "0" into temp tran_n_p; Query plan 1: DIRECTIVES FOLLOWED: INDEX ( tab2 tab2_idx03 ) Estimated Cost: 348628 Query time: 28 seconds Query plan 2: DIRECTIVES FOLLOWED: INDEX ( tab2 tab2_idx06 ) Estimated Cost: 8141022 Query time: 18 seconds The optimizer choses Query plan 1 above, HOWEVER, there is another query plan that is not chosen that has the best Estimated Cost and query time: DIRECTIVES FOLLOWED: INDEX ( tab2 tab2_idx06 ) AVOID_HASH ( tab2 ) Estimated Cost: 5108 Query time: 10 seconds Note: In a system with much more data, the query times are significantly longer for the query plans with the higher estimated costs. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 11.70.FC9W2, 12.10.xC15 * * and 14.10.xC5. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Informix Server 11.70.xC9W2, 12.10.xC15 (when * * available) or 14.10.xC5. * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 11.70.FC9W2, 12.10.xC15 * * and 14.10.xC5. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Informix Server 11.70.xC9W2, 12.10.xC15 (when * * available) or 14.10.xC5. * **************************************************************** | |
Comment | |
Fixed in Informix Server 11.70.xC9W2, 12.10.xC15 and 14.10.xC5. | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 12.04.2020 21.12.2020 21.12.2020 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |