Re: Q: order by uses temp file

Hi, In article <361A65BF.8BABD201@ix.netcom.com>, Mickey Mestel <mickm@ix.netcom.com> wrote: > hi, > > from the following sqexplain.out output: > > QUERY: > ------ > select * from interest > where objid = 72359 and objtype = 2 > order by level desc> > Estimated Cost: 1 > Estimated # of Rows Returned: 1 > Temporary Files Required For: Order By > > 1) anderson.interest: INDEX PATH > > (1) Index Keys: objid objtype level (desc) > Lower Index Filter: (anderson.interest.objid = 72359 AND > anderson.interest.objtype = 2 ) > > why is it generating a temporary file for the order by? by giving > values for objid and objtype, we are limiting are search to just those > values. level is already sorted in desc order within objid and objtype, > so what is the need for the temp file, the data is already sorted > according to level in descending order, which is what we are asking for > in the query. > > any thoughts? Probably because optimizer is using index created on objid objtype level (desc) and you want to have only level (desc). > > thanks, > > mickm > -- > > ----------------------------------------------------------------------- > This is a signature file. This is only a signature file. Had this > been an actual piece of useful information, you would have been > instructed on what to do with it. > ----------------------------------------------------------------------- > hth -- Vardan Aroustamian -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own