when you execute sql queries , oracle returns the data in the order according to your order by clause in the sql query. if no order by clause is used in the query and if the sql query is using an index and normally the data is returned according to the order of the index. However if your sql query has many tables and many different joins and you dont specify a order by clause then oracle will not order the data and in that case when you execute the query at different times you might get result sets in different orders. so dont be sure that your queries will return the data always in the same order unless you specify a order by clause.
below metalink article explains briefly about this behaviour.
| Subject: | Ordering of Result Data | |||
| Doc ID: | 344135.1 | Type: | BULLETIN | |
| Modified Date: | 02-APR-2009 | Status: | MODERATED | |
In this Document
Purpose
Scope and Application
Ordering of Result Data
References
| This document is being delivered to you via Oracle Support’s Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review. |
Applies to:
Oracle Server – Enterprise Edition – Version: 8.0.3.0 to 11.1.0.6
Information in this document applies to any platform.
Purpose
Describe how Orders result data.
Scope and Application
Basic
Ordering of Result Data
There is no default ordering in Oracle. If no ordering is specified then the data will be returned as it is found in the objects it is retrieved from. Pre-ordered data sources (such as indexes) would return sorted data but unordered data would be returned unsorted (like full table scans). If the access path for your queries have changed then it is possible for the order the data is returned in to be different compared with before. The only way that Oracle guarantees the row order is if you supply an order by clause in your statements. There are 2 parameters which can affect the order in some queries , these are _newsort_enabled and _gby_hash_aggregation_enabled. However, any effect they have on sorting is purely as a result of the fact that with them set and without them set will result in different sorting code being used whose intention is purely performance related and a change in order is coincidental. Such parameters may be depricated in future releases.
References
Note 345048.1 – ‘Group By’ Does Not Sort If You Don’T Use Order By In 10g
Note 456707.1 – Order Of Data Retrieval Differs after upgrading 9i To 10g
Note 468361.1 – DISTINCT May Remove Sorting From Pre-Sorted Data
