Re: ANY IDEA ON USING INDEX FOR ORDER BY

Hello Gokhan I am reading between the lines a bit, because the information you have = posted is a bit sketchy, so bear with me if I am wrong. Are you saying that this table has an indexes as follows. scenario 1. Index1 Duplicates allowed (fileid) Index2 (fileid,refno) Are you saying you want it to use the 2nd index, but it is using the = first. If so, you have company, I struck this problem last week in 7.23. Firstly, the first index can almost be considered redundant , as the = second index can give nearly as fast access for any query selected just on = fileid. Secondly, in 7.23 all of a sudden, this became a problem for us. Somehow the optimiser is not taking the cost of the order by into account = correctly. Our programs slowed right down because Online was choosing the = first index, not the second. And 'order bys' seem to be taking forever. = (Yet to pin this down by testing). The short answer is to drop the first index. Online will then use the = second. If the above is not your scenartio Then perhaps this is. Index 1 (fileid) Index 2 (refno) In this case, of course Online would use the first index, because this is = the attribute that is slected in the wherepart. To get online to not build a sort file for the order by, you need to build = an index as follows; (fileid, refno). (In situations like this, you may want to consider including in your post = the key structures of your table, as well as version numbers etc of = informix, as it gives respondents a bit more to go on in offering advice.) Anyway HTH rgds Peter Tashkoff <tashkop@iname.com> Zespri International Limited Std Disclaimers Apply All rights reserved. No party may use this document to vilify another. Zespri New Zealand Kiwifruit, The World's Finest >>> Gokhan Tercan <gtercan@milliyet.com.tr> 15/01/98 23:10:56 >>> ANY IDEA ON USING INDEX FOR ORDER BY The optimizer dos not use index on refno for order by when I add t.fileid BETWEEN "BK7180IN" AND "BK7200IN" in where clause. I tried update statistics but nothing changed. fileid CHAR(8) refno INTEGER And sqexplain.out is : QUERY: ------ SELECT * FROM transaction t WHERE t.fileid BETWEEN "BK7180IN" AND "BK7200IN" ORDER BY t.refno Estimated Cost: 3 Estimated # of Rows Returned: 1 Temporary Files Required For: Order By 1) crx.t: INDEX PATH (1) Index Keys: fileid Lower Index Filter: crx.t.fileid >=3D 'BK7180IN' Upper Index Filter: crx.t.fileid <=3D 'BK7200IN'