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

In <5ealcf$efi@lana.zippo.com> vjaco@hcia.com writes: >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. I certainly would not expect this as a general rule. Using an ORDER BY in this fashion certainly gives you some control over the sequence of insertions into your temp table, and yes, normally you might think that an unordered select from the temp table would return the rows in the very same sequence, but this is second guessing the engine and must be counted, I'm afraid, as poor practice. Apart from the round robin situation, you describe below, subsequent manipulations on the temp table (deletes followed by more inserts) might also screw up your sequencing, and I wouldn't trust that I knew all of the engine's tricks in other areas to guarantee the retrieval. >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. Not really. The ORDER BY clause is for use in SELECT statements, and is not designed to give you physical or logical ordering on disk. If the Other Database, or Sybase, or whatever, gives you the functionality, then I would suggest that this is a side-effect you have been taking advantage of, and not a design feature of the database engine. >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. This one. 100% of the time. >2. Set your environment variable DBSPACETEMP to only one temp dbspace. Possible performance impact? >3. Use a cursor to insert into a "real" temporary table. I doubt that 3) will give you what you want, and in any case, you just shouldn't be trying to force the engine into doing it. Put your ORDER BY on the SELECT from the temp table, where it belongs. Bryan Tonnet batonnet@zeta.org.au