Re: Order of rows in temp tables

On Mon, 15 Jun 1998 16:11:51 CDT, "Sunil Thakkar" <sunil0@hotmail.com> wrote: >Martin Brassard wrote: >[SNIP] >> 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. No is the simple answer. The relational theory says so and then it is so. Simple. Although SQL doesn't exactly implement relational theory very well, in this case it does. There is no defined order to the returned rows of any select statement unless you use an order by. See futher below. >> 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. >Many others replied : > >I just tried it and the data from the temp table (1660 rows) was sorted >in batches. Also in 7.30+ temp tables are created as fragmented tables >across all tempspaces and loaded round robin so the return order would >depend on several factors including PDQPRIORITY. It is best to include >an ORDER BY clause WHEN EVER you want data in a particular order. > >=================================== > >I have also tried it with a large table on 7.30 and with different >fields in the order by clause. The result is it gives the output from >the temp table with the correct order. > >I have also tried to see the time it takes with > >1. giving order by clause in select ...insert into temp and >2. selecting from temp table with order clause > >The result is that the time taken is almost the same. (the difference >could be due to the other processes at different times) > > >My question is : > >NOW IF THE ORDER IS CORRECT then why not do an order by once at the time >of creation of temp table, and use that order whenever you access the >temp table. > >If it gives you the results in the correct order and if you are going to >use this temp table for many times, Then I would like to differ from >others and suggest you use the order by at the time of creation of table >and then just use the temp table without order by. I wouldn't even dream of emploing you as a designer nor even a programmer until you check up a little on how relational databases works. I wouldn't ever want this kind of unreliable code in any of our applications. In a relational database the order of the returned rows is *by definition* undefined when no order by clause is used. By definition means that even if they are ordered in a spesific way in every test you do one day something may happen resulting in a different order. This something may be any of a large number of things, from a change in the engine (in a new version) to some relocation of the data the next time you create a temporary table like this or anything else. To design and program anything with relational databases it's all important to understand the major underlying theory out of which this is one of the important parts. If you don't understand and follow this you may one day wake up with problems that can no longer be solved. >Logic Works every time. >Sunil Thakkar. > >If you are accessing the temp table in that order many times and if Above not sniped by me. Something may be lacking here. Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html (Now with ODBC info under "Third party products".)