Re: Order of rows in temp tables
Posted in 1998
Topics: Performance & Tuning
Martin,
Yes, the rows will be returned in the order in which they were inserted
(physical order). You need not specify the order by clause. It will
basically do a sequential scan; Unless, of course,if you have an index on
ColA, in which case they will returned in the order of the indexed column
for your query of :
select ColA, Colb from TempA where ColA >= 100;
Suhas Tembe
stembe@msn.com
Martin Brassard wrote in message <35842CFB.6B1B@sympatico.ca>...
>Hi,
>
>We have made a little experiment with temp tables and would appreciate
>that someone confirme the results.
>
>Let's say we have TableA (ColA Integer, ColB Char(10), ColC smallint);
>
>We issue the folowing query:
>
>Select ColA, ColB from TableA where ColC = 2 order by ColA, ColB into>temp TempA;
>
>Here whe have in TempA the subset of TableA that should be ordered by
>ColA & ColB.
>
>Then we issue that other query:
>
>Select ColA, ColB from TempA;>or
>Select ColA, ColB from TempA where ColA >= 100;>
>In these cases, the rows from TempA are returned ordered by ColA & ColB,
>even if we don`t specify the order by clause, probably because of the
>physical order of the data in TempA.
>
>My question is : Can we assume that the rows of the temp table will
>always be returned in the physical order of the table if we don`t
>spedify an order by clause in our query.
>
>What makes me hesitate to implement this is that I think that the clause
>"order by" with "into temp" was a invalid syntax in earlier version of
>Informix.
>
>Thanks,
>
>Martin