RE: Temporary Files Required For: Order By - Why?

-----Original Message----- From: Leonid Vorontsov [SMTP:Leonids.Voroncovs@dati.lv] Sent: Friday, May 28, 1999 4:32 AM To: informix-list@iiug.org Subject: Re: Temporary Files Required For: Order By - Why? Leonid Vorontsov wrote: >=20 > Leonid Vorontsov wrote: > > CREATE TABLE t ( > > id integer PRIMARY KEY, > > f1 integer, > > f2 integer > > ); > > CREATE INDEX i1 ON t ( f1 ); > > CREATE INDEX i2 ON t ( f1, f2 ); > > SELECT f2 FROM t WHERE f1 =3D 4 ORDER BY f2;> > Does server will do sorting? > > All information already sorted in i2 (IMHO). [William Rice] I dont know if anyone has mentioned it but Order by f1, f2 should give you the results you desire as far as using the index instead of a tempory sort file. =20 I guess you just need to live with the fact that the optimizer doesnt realize that you are only dealing with one value for field one and use the index anyway. Logic like that seems like it would have to much overhead just to make the programmer have to know less. But then again = I don't write databases for a living so maybe it is something they = should build into an optimizer Will