informix opitmalisation

I dont know well Informix but there is a strange situation for me in query optimalisation. 1. I have large table with rows r1,r2,r3 2. I have 2 idndexes: i1 on (r1, r2) and i2 on (r3) when I execute query like this "select * from table where r1=1 and r2=0 order by r3" informix optimalisator use only index i1! so orderby statement force informix to do physical sort data! thanks for any suggestions for resolving this problem

Krzys wrote: > > I dont know well Informix but there is a strange situation for me in query > optimalisation. > 1. I have large table with rows r1,r2,r3 > 2. I have 2 idndexes: i1 on (r1, r2) and i2 on (r3) > when I execute query like this "select * from table where r1=1 and r2=0 > order by r3" informix optimalisator use only index i1! so orderby statement > force informix to do physical sort data! This is correct and normal behavior for the optimizer. It has decided that the cost of using index r3 to get the records in sorted order, but then having to filter every row in the table, is too high. Instead it has calculated that the cost of sorting the small result set is much less since it can use the filter capability of index i1 instead. Note that IDS 7.xx can only use one index per table in a query it has not facility to combine indexes. IDS/XPO 8.xx CAN combine indexes, but, 7.xx cannot. I'd wager lots of money that if you time the query then drop index i1 so the optimizer has only i2 to improve performance it will use i2 and the query will take longer. If you have a version 7.3x server, you can use optimizer hints to force the engine to use whatever index you want but I'd advise against it unless you test the benefits with MANY sample queries, what works for one specific filter value may not work for others. The optimizer IS smart enough to know this so if you are going to try to do some of its job for it you had better be smart enough also. BTW always provide OS and IDS versions and platform information when posting so we can better answer your questions without excess verbiage. Art S. Kagel