RE: Order by effect on temp tables
I don't think it matters whether it is mirrored or not. You can't guarantee
the order unless you use an ORDER BY. And that has been the case with every
Informix engine I have used. I had one classic where two users did the same
query at the same time and got the answers back in a different order.
Regards
Malcolm
-----Original Message-----
From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]
On Behalf Of Doug Lawry
Sent: 16 May 2007 12:49
To: informix-list@iiug.org
Subject: Re: Order by effect on temp tables
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.ib
m.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
>
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list