Help with certain slow SQL query

Hi there, I have an SQL query listed below which I would appreciate some advice on trying to make it run quicker. It currently runs for about 2 mins and returns 36 rows, however, it is called many times in a program that produces investment statements and thus takes all weekend to run. When I leave out the date range filters the query takes under a second and returns about 128 rows. If I just have one the date filters it takes around 2 or 3 seconds and returns around 70 rows. The purpose of the date range is let the program print a years worth of unit history on the statement, but seems to fool the optimizer to use the index path for the date columns rather than the cnhldgid_ref column. In my mind, the optimizer should use the cnhldgid_ref join to filter the rows to a subset of 100 and then it shouldn't need to use the date indexes for a set of rows this small in number. The version of Informix Online is 7.23.UC1, the tables have had update statistics run (using Mr. Kagel's updstats script), and there are indexes on cnhldgid_ref, fk_ref, status_ref and the due_date columns. OPTCOMPIND is set to 0 in ONCONFIG. # of rows in tab_a = 280,000 # of rows in tab_b = 1,200,000 QUERY: ------ select tab_b.* from tab_b, tab_a where tab_a.cnhldgid_ref = tab_b.cnhldgid_ref and tab_b.due_date >= '17/02/1997' and tab_b.due_date <= '16/02/1998' and tab_a.fk_ref = 298516 and tab_b.status_ref = 99 Estimated Cost: 7 Estimated # of Rows Returned: 1 1) new067.tab_b: INDEX PATH Filters: new067.tab_b.status_ref = 99 (1) Index Keys: due_date Lower Index Filter: new067.tab_b.due_date >= 17/02/1997 Upper Index Filter: new067.tab_b.due_date <= 16/02/1998 2) new067.tab_a: INDEX PATH Filters: new067.tab_a.fk_ref = 298516 (1) Index Keys: cnhldgid_ref Lower Index Filter: new067.tab_a.cnhldgid_ref = new067.tab_b.cnhldgid_ref -- Simon Barber