Temporary Files Required For: Order By - Why?
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?
Leonid.
The problem is that the server doesn't know that the rows may be
sorted. That's the fun with a database such as Informix; the data could
be physically located anywhere within the table. If there were an index
on f2, and Informix used that index, then you wouldn't have any temp
file needed for sorting. Informix then knows how the data is ordered
based on the index.
John Carlson
Informix DBA
WHSmith USA
Leonid Vorontsov wrote:
>
> 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?
>
> Leonid.
Leonid Vorontsov wrote:
> 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?
As Mr O The Clown said, nice clear question.
As both Mr O and Mr Carlson said, the problem
is that index i2 does not present the values
in f2 in sorted order; it only presents them
in order within a given value of f1.
What neither Mr O nor Mr C pointed out is that
index i1 is redundant. In any case where i1
can be used, i2 can be used. Therefore, you
should probably only keep index i2.
And, if you want data sorted by f2 regularly
without incurring the overhead of a sort when
selecting the data, then you need an index on
f2, or (f2, f1) as well.
--
Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN
#include <disclaimer.h>
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 = 4 ORDER BY f2;> Does server will do sorting?
> All information already sorted in i2 (IMHO).
If server use index i1 then work must be:
read index key 1 -> compare filter condition - not suitable
read index key 1 -> compare filter condition - not suitable
read index key 2 -> compare filter condition - not suitable
read index key 2 -> compare filter condition - not suitable
read index key 2 -> compare filter condition - not suitable
read index key 3 -> compare filter condition - not suitable
read index key 4 -> compare filter condition - suitable -> read row ->
write into temp
read index key 4 -> compare filter condition - suitable -> read row ->
write into temp
read index key 4 -> compare filter condition - suitable -> read row ->
write into temp
read index key 4 -> compare filter condition - suitable -> read row ->
write into temp
sorting -> return result set
There is 18 conditional i/o + unknown number of i/o for sorting in this
job.
If server use index i2 then work must be:
read index key 1 1 -> compare filter condition - not suitable
read index key 1 2 -> compare filter condition - not suitable
read index key 2 1 -> compare filter condition - not suitable
read index key 2 2 -> compare filter condition - not suitable
read index key 2 3 -> compare filter condition - not suitable
read index key 3 1 -> compare filter condition - not suitable
read index key 4 1 -> compare filter condition - suitable -> return
result row
read index key 4 2 -> compare filter condition - suitable -> return
result row
read index key 4 3 -> compare filter condition - suitable -> return
result row
read index key 4 4 -> compare filter condition - suitable -> return
result row
That's all - NO SORTING REQUIRED.
There is 10 conditional i/o. From user's point of view system response
time is 7 i/o.
Obnoxio The Clown wrote:
> 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
Wrong. Data will be:
1
2
3
4
See filter condition in the query (f1 = 4).
> If you created an index on f2 on its own, the temporary file would go away.
OK. CREATE INDEX i3 ON t (f2);
If server use this index work must be:
read index key 1 -> read row -> compare filter condition - not suitable
read index key 1 -> read row -> compare filter condition - not suitable
read index key 1 -> read row -> compare filter condition - not suitable
read index key 1 -> read row -> compare filter condition - suitable ->
return result row
read index key 2 -> read row -> compare filter condition - not suitable
read index key 2 -> read row -> compare filter condition - not suitable
read index key 2 -> read row -> compare filter condition - suitable ->
return result row
read index key 3 -> read row -> compare filter condition - not suitable
read index key 3 -> read row -> compare filter condition - suitable ->
return result row
read index key 4 -> read row -> compare filter condition - suitable ->
return result row
Yes, You are right - no sorting required. But total number of i/o is 20.
And system response time is 8 i/o.
Carlson@WHSmith wrote:
>
> The problem is that the server doesn't know that the rows may be
> sorted.
Index i2 contains data sorted:
1 1
1 2
2 1
2 2
2 3
3 1
4 1 <-|
4 2 | There is all result set
4 3 | in correct order
4 4 <-|
No table reading required. And no sorting required too.
> If there were an index
> on f2, and Informix used that index, then you wouldn't have any temp
> file needed for sorting.
See above.
Jonathan Leffler wrote:
> As both Mr O and Mr Carlson said, the problem
> is that index i2 does not present the values
> in f2 in sorted order; it only presents them
> in order within a given value of f1.
Exactly what i want - within a given value of f1.
Once more: Does server will do sorting?
Leonid.
Leonid Vorontsov wrote:
>
> 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 = 4 ORDER BY f2;> > Does server will do sorting?
> > All information already sorted in i2 (IMHO).
> If server use index i1 then work must be:
> read index key 1 -> compare filter condition - not suitable
> read index key 1 -> compare filter condition - not suitable
> read index key 2 -> compare filter condition - not suitable
> read index key 2 -> compare filter condition - not suitable
> read index key 2 -> compare filter condition - not suitable
> read index key 3 -> compare filter condition - not suitable
> read index key 4 -> compare filter condition - suitable -> read row ->
> write into temp
> read index key 4 -> compare filter condition - suitable -> read row ->
> write into temp
> read index key 4 -> compare filter condition - suitable -> read row ->
> write into temp
> read index key 4 -> compare filter condition - suitable -> read row ->
> write into temp
> sorting -> return result set
> There is 18 conditional i/o + unknown number of i/o for sorting in this
> job.
> If server use index i2 then work must be:
> read index key 1 1 -> compare filter condition - not suitable
> read index key 1 2 -> compare filter condition - not suitable
> read index key 2 1 -> compare filter condition - not suitable
> read index key 2 2 -> compare filter condition - not suitable
> read index key 2 3 -> compare filter condition - not suitable
> read index key 3 1 -> compare filter condition - not suitable
> read index key 4 1 -> compare filter condition - suitable -> return
> result row
> read index key 4 2 -> compare filter condition - suitable -> return
> result row
> read index key 4 3 -> compare filter condition - suitable -> return
> result row
> read index key 4 4 -> compare filter condition - suitable -> return
> result row
> That's all - NO SORTING REQUIRED.
> There is 10 conditional i/o. From user's point of view system response
> time is 7 i/o.
>
> Obnoxio The Clown wrote:
> > 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
> Wrong. Data will be:
> 1
> 2
> 3
> 4
> See filter condition in the query (f1 = 4).
>
> > If you created an index on f2 on its own, the temporary file would go away.
> OK. CREATE INDEX i3 ON t (f2);
> If server use this index work must be:
> read index key 1 -> read row -> compare filter condition - not suitable
> read index key 1 -> read row -> compare filter condition - not suitable
> read index key 1 -> read row -> compare filter condition - not suitable
> read index key 1 -> read row -> compare filter condition - suitable ->
> return result row
> read index key 2 -> read row -> compare filter condition - not suitable
> read index key 2 -> read row -> compare filter condition - not suitable
> read index key 2 -> read row -> compare filter condition - suitable ->
> return result row
> read index key 3 -> read row -> compare filter condition - not suitable
> read index key 3 -> read row -> compare filter condition - suitable ->
> return result row
> read index key 4 -> read row -> compare filter condition - suitable ->
> return result row
> Yes, You are right - no sorting required. But total number of i/o is 20.
> And system response time is 8 i/o.
>
> Carlson@WHSmith wrote:
> >
> > The problem is that the server doesn't know that the rows may be
> > sorted.
> Index i2 contains data sorted:
> 1 1
> 1 2
> 2 1
> 2 2
> 2 3
> 3 1
> 4 1 <-|
> 4 2 | There is all result set
> 4 3 | in correct order
> 4 4 <-|
> No table reading required. And no sorting required too.
> > If there were an index
> > on f2, and Informix used that index, then you wouldn't have any temp
> > file needed for sorting.
> See above.
>
> Jonathan Leffler wrote:
> > As both Mr O and Mr Carlson said, the problem
> > is that index i2 does not present the values
> > in f2 in sorted order; it only presents them
> > in order within a given value of f1.
> Exactly what i want - within a given value of f1.
>
> Once more: Does server will do sorting?
>
> Leonid.
Hey people! Discussion finished? But i haven't answers till now :-(
Does my explaination is correct?
If yes - Why string "Temporary Files Required For: Order By" emerges?
If no - Can You explain server behaviour?
Which index is "optimal" in case mentioned?
Leonid.
Leonid Vorontsov wrote:
>
> Leonid Vorontsov wrote:
> >
> > 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 = 4 ORDER BY f2;> > > Does server will do sorting?
> > > All information already sorted in i2 (IMHO).
TRUTH But! The engine will not recognize that it is only selecting for
a single value of f1 and even consider using i2 for ordering unless you
change the ORDER BY clause to be ORDER BY f1, f2. EVEN then the
optimizer feels perfectly free to use i1 for filtering and then do a
physical sort anyway, but try it. Make sure UPDATE STATISTICS HIGH FOR
t(f1); has been performed for best results (and a HIGH on f2 would help
lso). If it still does the sort and uses i1 time it and use directives
to force the engine to use i2 and time that and compare. The
optimizer's index -vs- sort choices are often very odd to us mortals
but they are usually correct.
Art S. Kagel