Re: When are Temporary Files Required For: Order By

Obnoxio The Clown wrote: > > >I have the following select: > > select col_char_11, col_date, col_decimal_11_0, col_char_3,> >col_serial > > from table > > where col_char_11 = "01234567891" > > and col_date between "01/01/1999" and "31/01/1999" > > and col_decimal_11_0 > 0 > > order by col1, col2, col3. > > > >I have 2 indexes on the table: a composite on col_char_11, col_date, > >col_decimal_11_0 and > > a unique index on col_serial > > > >When I look at the sqexplain.out file, I get the output you would > expect for > >the above query i.e.. > >Index being used. Estimated cost = 1. Estimated rows returned = 1 etc. > >This is all well and good. > > > >When I change the order by to order by col_serial > >I get the same output but this time it says > >Temporary Files Required For: Order By > > > >My question is > >Why are Temporary Files required when I order by a serial column and > not > >when I order by > >the columns in the composite index ? > > Probably because the where clause in the first one contains the same > columns as the order by. What could be horrendous is that the second one > would sort the entire table before applying the where clause, possibly > leading to a much slower query. It's not QUITE that bad. The filters will be applied first on the way to the temp table. The resultant rows in the temp table will them be sorted. Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com http://www.informix.com/idn |///// / //| |http://www.iiug.org +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 838250 2325 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+