Re: ANY IDEA ON USING INDEX FOR ORDER BY

Hi, Since you have an index on fileid the optimizer is chossing it because it thinks it will be less work to scan it and do the order by afterwards. i.e. the optimizer has at least 2 choices here: 1) Read the order by index (refno) first so it doesn't have to sort later and then use weed out the non-matching records. 2) Scan the refno index and sort the returned rows. It appears the the optimizer feels that there aren't too many rows that are between the range specified. What level of update stats are you running? If you are not running high on the indexed columns you may want to try that. Another idea: Add the fileid to the order by index. Let me know how it goes, Bill } } 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 >= 'BK7180IN' } Upper Index Filter: crx.t.fileid <= 'BK7200IN' -- Bill Ennis Voice: 312-474-7516 SSA Fax: 312-474-7460 500 W. Madison email: ennis@ssax.com ennis@accesschicago.net