You have some order in the CREATE clause and most databases would retain that when you issue * in a SELECT. | [reply] |
Here is an exercise for you.
Create a table. Give it the fields (state, city, address). Populate it with, say, 500 records in random order, divided between, say, 20 states and 80 cities. Put an index on (state, city). Make sure that database statistics are properly populated. Now "select * from tbladdress where state = '$state'" for one of the less populated states.
If you did that as I described, your results are likely to come back grouped by city (in Oracle ordered that way as well), and not the order you inserted them in. That is because the database realizes that it is faster to access data through the index, and the index is organized differently from the table.
In Oracle specifically, if you select from the most populous state it may well come back in your original insertion order. That is because the cutoff between preferring an index to a full table scan is around 7% in Oracle. Your least populous state is below that magic figure. Your most populous one is above it.
In short, it is programmer error to make any assumptions about what order things will come back in if you do not have an order by clause. The order which things come back in is an internal implementation detail, and if you rely on it you deserve to be bitten by it some day.
Occasionally you may run across optimization advice that contradicts this. For instance in Oracle (and likely other databases) a group by also sorts data. So I had one instructor from Oracle point out to me that if you have a group by you can just specify the right group by and avoid having an order by, thereby saving yourself one sort in the database. However it turns out that even that you can't fully depend on - going from Oracle 9i to Oracle 10g the order in which NULL is placed in the result set changed. (But only if you have 2 or more columns in the group by.)
The moral remains: if you have not specified an order by clause, do not expect any particular order to result. If one does, it is a coincidence that you should not rely on.
| [reply] |
First I was not talking about the order of records but the order of columns. Second I can force the order of columns by using the columns sequence in the SELECT clause - so I can have any order I want and particularily I can have the same order that was in the CREATE query.
| [reply] |
| [reply] |