Re: Temporary Files Required For: Order By - Why?
From: Leonid Vorontsov <Leonids.Voroncovs@dati.lv>
>
>Hi All!
>Can You explain me server behaviour?
>Situation:
>CREATE TABLE t (
> id integer PRIMARY KEY,
> f1 integer,
> f2 integer
>);
>INSERT INTO t VALUES ( 0, 1, 1 );
>INSERT INTO t VALUES ( 1, 1, 2 );
>INSERT INTO t VALUES ( 2, 2, 1 );
>INSERT INTO t VALUES ( 3, 2, 2 );
>INSERT INTO t VALUES ( 4, 2, 3 );
>INSERT INTO t VALUES ( 5, 3, 1 );
>INSERT INTO t VALUES ( 6, 4, 1 );
>INSERT INTO t VALUES ( 7, 4, 2 );
>INSERT INTO t VALUES ( 8, 4, 3 );
>INSERT INTO t VALUES ( 9, 4, 4 );
>CREATE INDEX i1 ON t ( f1 );
>CREATE INDEX i2 ON t ( f1, f2 );
>UPDATE STATISTICS FOR TABLE t;>Query:
>SELECT f2 FROM t WHERE f1 = 4 ORDER BY f2;>Problem:
>There is subj string in explain file.
>Does server will do sorting?
>All information already sorted in i2 (IMHO).
>Or my understanding is incorrect?
Lovely clear exposition, BTW.
You misunderstand ordering, however, in the above example, the data coming
back will be ordered:
1
1
1
1
2
2
2
3
3
4
Whereas your data was sorted:
1
2
1
2
3
1
1
2
3
4
If you created an index on f2 on its own, the temporary file would go away.
HTH.
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com