Re: What causes a temp file to be used?

wmark@pvar.com (Mark S. Winsor) wrote: :I have a select with a join to another table on the primary key, and :an order by of custnum, callnum in the primary table. The set explain shows :that a temporary file is created due to this order by, yet I have an index :of custnum, callnum on the table. Why would this use a temporary table? :I'm using 5.01 SE. May be because it uses another index on the primary key to do the join. It can't then use your index on custnum, callnum to do the order by. This use of indexes also depends on what table is read first. Usually it reads from the smallest table, and do a lookup via an index in the larger table (I think it's this way). If your index is on the table where the lookup is done it can obviously not be used for an order by. There isn't much to do about this in SE 5.x other than rewriting your select statement. (I obviously assume you have done update statistics.) I have been able to trick the optimiser sometimes by changing the systables.nrows values manualy after an update statistics, but that is generally a very bad idea. There may be things you can do in SE 7.x or OnLine via different update statistic statements to make the optimiser use indexes differently. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org