Re: Q: order by uses temp file

Art S. Kagel wrote: > > 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. What about: select * from interest where objid = 72359 and objtype = 2 order by objid, objtype, level desc ????? John Carlson Informix DBA WH Smith, Inc.