Order by effect on temp tables
While mentioning the 'Restrictions on the Insert Selection' the IDS
documentation mentions
that: "the lack of an ORDER BY clause is not important. If you need
to ensure that the new rows are physically ordered in the table, you
can first select them into a temporary table and order it, and then
insert from the temporary table."
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlt.doc/sqltmst139.htm
Does it mean that if I select values into a temp table using an order
by clause and then read from the temp
table the order is guaranteed to be the same.
Considering the following data:
Employee table:
Name-Age
A-10
B-20
C-11
Largest table:
Name
K
==
Select name
from employee
order by age desc -- Does this ensure the temp table will have theresults in 'proper' order even while selecting?
into temp t;
update largest
set name = (select first 1 name from t);
insert into largest
select skip 1 name from t;
Would executing the above *guarantee* that the result will be the
following *always*?
Name
B
C
A
Krishna wrote:
> While mentioning the 'Restrictions on the Insert Selection' the IDS
> documentation mentions
> that: "the lack of an ORDER BY clause is not important. If you need
> to ensure that the new rows are physically ordered in the table, you
> can first select them into a temporary table and order it, and then
> insert from the temporary table."
>
> http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlt.doc/sqltmst139.htm
>
> Does it mean that if I select values into a temp table using an order
> by clause and then read from the temp
> table the order is guaranteed to be the same.
No.
First, you cannot include an ORDER BY clause in a SELECT statement that
includes an INTO TEMP clause. The doc you quote is suggesting that you:
SELECT name
FROM employee
INTO TEMP t;
SELECT name
FROM t
ORDER BY name DESC;
K
C
B
A
-- OR order the data in the temp table with:
CREATE CLUSTER INDEX t.idx ON t(name DESC);
SELECT name
FROM t;
K
C
B
A
But, see below, you MAY not be able to depend on the cluster ordering.
Of course, either way, you COULD:
update largest
set name = (SELECT FIRST 1 name
FROM t
ORDER BY name DESC);
--or why not just simply--
update largest
set name = (SELECT MAX( name ) FROM employee);
Even if you selected from the temp table ORDER BY and inserted into an
existing table using INSERT INTO ... SELECT ... ORDER BY... there's no
guarantee that the data will be returned from the ultimate table in sorted
order if you do not include an order by clause. ANSI SQL explicitely
requires that the order of data returned is NOT guaranteed unless an ORDER
BY clause is included and most modern optimizers and multi-threaded RDBMSes
take advantage of that to improve performance. Yes, data that's been
inserted ordered (or clustered into some specific order) will TEND to be
returned in the same order, but there's nothing that says it always will be.
Art S. Kagel
> Considering the following data:
> Employee table:
> Name-Age
> A-10
> B-20
> C-11
>
> Largest table:
> Name
> K
>
> ==
> Select name
> from employee
> order by age desc -- Does this ensure the temp table will have the> results in 'proper' order even while selecting?
> into temp t;
>
> update largest
> set name = (select first 1 name from t);
>
> insert into largest
> select skip 1 name from t;>
> Would executing the above *guarantee* that the result will be the
> following *always*?
>
> Name
> B
> C
> A
>
Beware that a SELECT from a table without an ORDER BY clause might not return
data in the order in which it was inserted if the dbspace is mirrored (in
Informix rather than the operating system), as the query optimizer may decide to
read from both copies in parallel, as I know to my cost!
--
Regards,
Doug Lawry
www.douglawry.webhop.org
"Krishna" <calvinkrishy@gmail.com> wrote in message
news:1179240100.864989.136510@p77g2000hsh.googlegroups.com...
> While mentioning the 'Restrictions on the Insert Selection' the IDS
> documentation mentions
> that: "the lack of an ORDER BY clause is not important. If you need
> to ensure that the new rows are physically ordered in the table, you
> can first select them into a temporary table and order it, and then
> insert from the temporary table."
>
> http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlt.doc/sqltmst139.htm
>
> Does it mean that if I select values into a temp table using an order
> by clause and then read from the temp
> table the order is guaranteed to be the same.
>
> Considering the following data:
> Employee table:
> Name-Age
> A-10
> B-20
> C-11
>
> Largest table:
> Name
> K
>
> ==
> Select name
> from employee
> order by age desc -- Does this ensure the temp table will have the> results in 'proper' order even while selecting?
> into temp t;
>
> update largest
> set name = (select first 1 name from t);
>
> insert into largest
> select skip 1 name from t;>
> Would executing the above *guarantee* that the result will be the
> following *always*?
>
> Name
> B
> C
> A
>