How to force a query to use a particular index ??

I have a problem, ...I am using Informix 4GL version 4.20 on Online 5.10 UC1. My application have to query for a record in a table which have 2 composite indexes on it, say .. Table bonus ( member_id integer, promo_id integer, promo_type char(1), bonus_type char(5), balance dec(16,2)) with indexes i_1 on bonus (member_id, bonus_type) i_2 on bonus (promo_id, promo_type) On querying for a record using member_id, promo_id, promo_type, bonus_type in the WHERE clause, with SET EXPLAIN ON, it shows index i_2 is used. This index gives a set of about 20,000 record to search on. At other times, the same query uses index i_1, which gives a set of 5 records !! The better index to use. Unfortunately, I cannot do without index i_2. So how can I force a SELECT to use index i_1 in its query plan instead of i_2 ??? Any help given is much appreciated. Thanks in advance. Alan Wong. **** Posted from RemarQ - http://www.remarq.com - Discussions Start Here (tm) ****

Alan Wong wrote: > > I have a problem, ...I am using Informix 4GL version 4.20 on Online 5.10 > UC1. > > My application have to query for a record in a table which have 2 composite > indexes on it, say .. > > Table bonus ( > member_id integer, > promo_id integer, > promo_type char(1), > bonus_type char(5), > balance dec(16,2)) > with indexes > i_1 on bonus (member_id, bonus_type) > i_2 on bonus (promo_id, promo_type) > > On querying for a record using member_id, promo_id, promo_type, bonus_type > in the WHERE clause, with SET EXPLAIN ON, it shows index i_2 is used. This > index gives a set of about 20,000 record to search on. > > At other times, the same query uses index i_1, which gives a set of 5 > records !! The better index to use. > > Unfortunately, I cannot do without index i_2. So how can I force a SELECT > to use index i_1 in its query plan instead of i_2 ??? OL5.xx's optimizer is fairly stupid compared to 7.xx's optimizer and declaring an ORDER BY clause will ALMOST always cause the optimizer to choose an index that matches the ORDER BY clause if such is available. Try ORDER BY member_id, bonus_type, if you can live with that sort order then that will solve the problem. Art S. Kagel