select index ???
Hi everyone !
We have Informix 7.23.FC1 for DEC Alpha.
How to force Informix to use an index " sl1 _ wrk _ b200 _ 5 "
Instead of " sl1 _ wrk _ b200 _ 3 "
TABLE:
---------
create table "informix".sl1_wrk_b200
(
acc_date date not null constraint "informix".n477_1114,
acc_l0 char(5) not null constraint "informix".n477_1115,
acc_l1 char(20) not null constraint "informix".n477_1116,
ccy char(3) not null constraint "informix".n477_1117,
equ smallint not null constraint "informix".n477_1118,
planned smallint not null constraint "informix".n477_1119,
overdraft smallint not null constraint "informix".n477_1120,
acc_type smallint not null constraint "informix".n477_1121,
rdf_count smallint not null constraint "informix".n477_1122,
turn_db decimal(18,2) not null constraint "informix".n477_1123,
turn_cr decimal(18,2) not null constraint "informix".n477_1124,
sld_db decimal(18,2) not null constraint "informix".n477_1125,
sld_cr decimal(18,2) not null constraint "informix".n477_1126,
prev_date date,
e_count integer not null constraint "informix".n477_1127
);
create unique index "informix".sl1_wrk_b200_1 on "informix".sl1_wrk_b200
(acc_date,
acc_l0,acc_l1,ccy,equ,planned);
create unique index "informix".sl1_wrk_b200_2 on "informix".sl1_wrk_b200
(acc_l0,
acc_l1,ccy,planned,acc_date,equ);
create unique index "informix".sl1_wrk_b200_3 on "informix".sl1_wrk_b200
(acc_l0,
acc_l1,ccy,equ,planned,acc_date);
create unique index "informix".sl1_wrk_b200_4 on "informix".sl1_wrk_b200
(acc_l0,
acc_l1,ccy,acc_date,equ,planned);
create unique index "informix".sl1_wrk_b200_5 on "informix".sl1_wrk_b200
(acc_l0,
ccy,acc_l1,acc_date,equ,planned);
QUERY:
------
select acc_date,acc_l0,acc_l1,s.ccy,equ,planned
FROM informix.sl1_wrk_b200 s
where
s.acc_date=(select max(acc_date) from sl1_wrk_b200 t
where
t.acc_l0=s.acc_l0 and
t.ccy = s.ccy and
t.acc_l1=s.acc_l1 and
t.acc_date <= '15/05/1998' and
t.equ = s.equ and
t.planned = s.planned )
and s.planned = 0
order by s.acc_l0,s.ccy,s.acc_l1,s.acc_date,s.equ,s.planned
Estimated Cost: 55259
Estimated # of Rows Returned: 130
Temporary Files Required For: Order By
1) informix.s: INDEX PATH
Filters: (informix.s.acc_date = <subquery> AND informix.s.planned = 0 )
(1) Index Keys: acc_l0 acc_l1 ccy equ planned acc_date (Key-Only)
Subquery:
---------
Estimated Cost: 10
Estimated # of Rows Returned: 1
1) informix.t: INDEX PATH
(1) Index Keys: acc_l0 acc_l1 ccy equ planned acc_date (Key-Only)
Lower Index Filter: (informix.t.acc_l0 = informix.s.acc_l0 AND
(informix.t.ccy = informix.s.ccy AND (informix.t.acc_l1 = informix.s.acc_l1
AND (informix.t.equ = informix.s.equ AND informix.t.planned =
informix.s.planned ) ) ) )
Upper Index Filter: informix.t.acc_date <= 15/05/1998
Thanks.
Serg