Re: Caution: Bug or "feature" - row ordering within temp tables in V7.x

I think you are asking a little too much of the database engine. When you compare Informix with Sybase et al., are their products using multiple temp dbspaces as well? It seems to me that a relational database is not required to have any sense of the physical order of the rows. With all due respect, when you take advantage of an undocumented feature, it is not required to be there in the next release. Physical ordering of rows is one such feature. Selecting one time from the temp table should provide the same performance as ordering the original rows. If you need to select them multiple times, build an index on the temp table, update statistics on it, and use an order by. Doesn't seem very onerous to me, and it guarantees results regardless of the physical order of the rows in the database. vjaco@hcia.com wrote: > > Using anybody's SQL (Sybase's, Oracle's and even Informix's), if I have an ORDER > BY clause in my SELECT statement, I expect to see my results ordered according > to the criteria that I specified. > > Here's the problem: > In V7.x if you are using multiple temp dbspaces (DBSPACETEMP) and you have a > statement like the following: > SELECT * FROM table_a INTO TEMP ORDER BY 1 temp_a> Do not expect the rows you get back from temp_a (SELECT * from temp_a) to be > ordered in any fashion, least of all by the first column. > > Informix's explanation for this, is that they are fragmenting the individual > rows over the temp dbspaces in a round_robin pattern when the temp table is > being created. However, when retrieving the rows back, they may not be using > the same round-robin sequence. > > Please correct me if I'm missing something, but when I ORDER BY rows I expect it > to be ordered; temp or permanent tables. > > The main problem is that the user is expecting ordered rows, and the statement > completes without any error indication. Hence, syntax that may have worked just > fine with pre V7.x engines or instances where only one temp dbspace was used, > will now all of a sudden produce incorrect results with no warning to the user. > > Workarounds (I'm sure there are more): > 1. Always include an ORDER BY statement when SELECTing from temp tables. > 2. Set your environment variable DBSPACETEMP to only one temp dbspace. > 3. Use a cursor to insert into a "real" temporary table. -- John Prideaux voice: (217)839-2121 email: alastl@ctnet.net Aladdin Steel, Inc. fax: (217)839-4327 alastl@ix.netcom.com Route 16 East Gillespie, IL 62033 My opinions are not those of Aladdin Steel, Inc.