preserving sort order
Is there any guarantee that columns selected from a table will be
returned in the order in which they were inserted into the table? It
seems to work, but I didn't know if there would ever be a case when it
would not like if there are more than one dbspace in use.
For example if I insert some data into a temporary table using an
order by clause:
select id, name, zip
from people
order by zip
into temp temp_table;
Will a subsequent insert statement that selects from the temporary
table insert the rows into the new table preserving the order? ...so
that another_table is sorted by zip (if I select from this table
ordering by a serial column).
insert into another_table (id, name)
select id, name
from temp_table;
If I were inserting the zip column into another_table then I could
order the above select statement using zip, but I'm not inserting zip.
Bryan wrote:
>
> Is there any guarantee that columns selected from a table will be
> returned in the order in which they were inserted into the table? It
> seems to work, but I didn't know if there would ever be a case when it
> would not like if there are more than one dbspace in use.
>
> For example if I insert some data into a temporary table using an
> order by clause:
>
> select id, name, zip
> from people
> order by zip
> into temp temp_table;>
> Will a subsequent insert statement that selects from the temporary
> table insert the rows into the new table preserving the order? ...so
> that another_table is sorted by zip (if I select from this table
> ordering by a serial column).
>
> insert into another_table (id, name)
> select id, name
> from temp_table;>
> If I were inserting the zip column into another_table then I could
> order the above select statement using zip, but I'm not inserting zip.
as per definition, when you see what appears like *any* order
when you do a select without an ORDER BY clause it always is
at best a side effekt.
If you want to retrieve in a certain order and do not want to have
the cost of the ORDER BY clause everytime, add a helper column which
reflects your sort order and create an index on that 1.
The optimizer then (after update statistics) is able to avoid the
costly sort because the info is all in this index on your helper
column
Of couse there are many situations (joins, SELECT clauses containing
DISTINCT keyword and so on) which can make that strategy impossible.
So always check if it does any good by using
SET EXPLAIN ON
dic_k