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. |
****************************************************************
* 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. *
**************************************************************** |
****************************************************************
* 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. *
**************************************************************** |
