Re: Why does sqlplus "select" produce different order of data?

What indexes are on the table? If you want to live a little dangerously you can get a free 'order by' by building a unique key constraint on some columns in your table. Your queries will return the data in the order defined by the unique key constraint without an order by statement, however Oracle does not guarantee that this will work in any situation 100% of the time. I do find this to be the case though with 7.2.3 under the rule based optimizer. I don't know what the cost based optimizer will do in this situation -- if it will prefer a table scan or index scan when it has a choice.

Since it's such a small table, there is virtually no overhead of the order by statement, so just add it to your query, but your question does raise some interesting side issues about optimizer preferences.

Blair Received on Fri Dec 05 1997 - 00:00:00 CST