Re: ANY IDEA ON USING INDEX FOR ORDER BY

Hi, first, I agree to Bill Ennis' words. If no UPDATE STATISTICS HIGH will help and if the estimated number of rows returned (1) is not correct, there's a way to force the optimizer to use an index. Please, if you notice what I'm doing here, do me a favour and place a comment inside your query, because it's not the kind of good and portable programming. Describe inside the comment what you want the optimizer to do. SELECT { USE INDEX(t refno_idx) } * FROM transaction t WHERE t.fileid || "" BETWEEN "BK7180IN" AND "BK7200IN" ORDER BY t.refno; If this does not work even if you set OPTCOMPIND=0, I'm sure that you do not have an index on "refno" or the index is corrupted. Bye Stefan Bill Ennis wrote: > > 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 SNIP > Bill Ennis Voice: 312-474-7516 > SSA Fax: 312-474-7460 > 500 W. Madison email: ennis@ssax.com ennis@accesschicago.net