Re: When are Temporary Files Required For: Order By

Rekaish Bhardwaj 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 ? Because you have filters on three columns that do not appear in the serial column index. So the index is not much use. If you had an index on col_serial, col_char_11, col_date, col_decimal_11_0 then it would probably not need the temp table for sorting. Hope that helps, -- 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"!|/ ////////| +----------------------+-----------------------------------+-----------+