Skip to content

oracle order of the result set returned by a sql query – knowledge article for the new oracle dba




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

Keywords

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.