Re: Q: order by uses temp file

Sorry for previous message. I didn't understand question correctly. 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? I think, because Estimated # of Rows Returned is just estimation. Don't know what to say if your index is unique. Are there other columns in 'interest' table? Seems it will not create temporary file if you will select only objid objtype level. > > 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. > ----------------------------------------------------------------------- > Regards -- Vardan Aroustamian -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own