Re: Hopeful No-brainer !!!

Sorry guys. What you say is wrong. Very wrong. By definition (of a relational database) the order of rows is *random* when you select without an order by. This is a very important part of any relational database. There is *no* built in order of the rows. That was the theory (which is correct). Now for what happens when you do your select. 1. In Informix SE - if you never delete any rows - the rows are returned in the order they where inserted (rowid order). You shouldn't ever utilize this. One day someone will delete a row (although you never expected that). You will still get the data back in rowid order (probably) but no longer in the order the rows where inserted. 2. As you say if there is an index on the rows you select you will get them back in index order at least in OnLine ver. 7.1x. On other versions, who knows? Also indexed scans will return will return rows in index order. But how do you know what index will be used next year or the next minute? You should *not* care. 3. On OnLine version 7.10.UC1 on SCO 3.2.4.2 it seems to return the rows in the order they are physically stored on the disk (in rowid order also probably) if there is no index on the selected rows, nor a where clause the results in an index scan. There is however no way of knowing this order for shure. The pages may be spread in any order on any disk. If you ever do anything to a table that will reorganize it (alter index to cluster, modify any columns or whatever) there will be free pages on the disk where that table was originally stored. A later insert into any other table that results in another extent beeing allocated may then use this freespace randomly spread over the disk. After that your rows may be returned in any order. 4. There may be any number of other issues giving different results (fragmentation, a new optimizer.....). The answer to the question of what order the rows are returned is simply to complex to keep track of in any application. You can rely on nothing but an order by. Moral: Read the theory and use it. PS: This moral is for application design. Another thing is if something has gone realy wrong in your database and you deside that doing repair work is simpler than a restore from a backup. In that case these things may become an issue and may be usefull. > svario@zenacomp.zenacomp.com (Stefanie Vario) writes: > Stefanie Vario (svario@zenacomp.zenacomp.com) wrote: > : Spetc@aol.com wrote: > : : If you select from a table (with or without indexes) and don't include an > : : order by clause, what is the default order of the rows returned? Is this > : : order affected by the presece or absence of indexes? > > : The default is to return rows in rowid order in the absence of an order by > : clause in versions 5.x. The order is not affected by the presence or absence > : of indexes. I believe that version 7.x is the same unless the table is > : fragmented. > > I forgot to add a few things. > This only applies to sequential scans in which the columns in your SELECT > statement are not part of an index. If you select just columns from an index, > then it will come back in that index order (i.e. SELECT cola, colb FROM table > and you have an index on cola, colb, then it will come back in that order). > If you use a filter in your WHERE clause and the column is indexed, then it > will come back in indexed order. The order is dependent on what columns you > are selecting, whether those columns are part of an index, and whether any > filtered columns are part of indexes. and > stiglich@interserv.com writes: > If you don't have a clustered index, the data is stored in the order in which > it was inserted. However, custered indexes doesn't t keep the data ordered > continuously. Nils.Myklebust@ccmail.telemax.no NM Data AS, Postbox 9090, Gronland, 0133 Oslo, Norway My opinions are those of my company