Re: Q: order by uses temp file

Mickey Mestel wrote: [SNIP] > 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? Yup. The optimizer isn't THAT smart. It says: "The data is coming back from the index ordered by level within objtype within objid and this user wants it ordered by level. Oh well, I'll just have to sort it myself!" Keep in mind the engine chooses sorting over an index scan and low level filter because it tends to be faster so even adding an index starting with level may not change the query path. No good solution. If you add another index on (level, objid, objtype ) and use SET OPTIMIZATION FIRST_ROWS or force the optimizer to use that index, with a directive, it MAY be faster, but I doubt it. However, if the problem is that you have to wait too long for the first few rows (and there are many matching rows), and you do not care how long the total query takes, this might be a working solution. Art S. Kagel