Re: ANY IDEA ON USING INDEX FOR ORDER BY

Gokhan Tercan <gtercan@milliyet.com.tr> wrote in article <69kphu$m3b@cssun.mathcs.emory.edu>... > 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' > The optimizer has decided that the number of rows it will get back using the "fileid" filter is low enough that it's better off using the fileid index to get the rows quickly and then doing a sort on refno. If you have a 7.x server and have done the recommended sequence of update statistics commands on the table, then the optimizer is probably making the right choice. If it used the ref_no index to avoid the sort, it would need to read every row in the table in order to find all rows which satisfied the filter on fileid. Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com