Q
Problem solve Get help with specific problems with your technologies, process and projects.

What is Oracle's default sorting behavior?

I have a case where I need to compare data from Oracle and SQL Server. My question is what is the default sorting behavior for Oracle?

Published: 04 May 2006

I have a case where I need to compare data from Oracle and SQL Server. My question is what is the default sorting behavior for Oracle? I have a select statement like this:

 Select <column> from <table>

This table doesn't have a primary key. It doesn't have an index either. It has two columns: one is NUMBER and the other is VARCHAR2 (50). No DML statements have been done on this table after I inserted data in it. When I retrieve data from Oracle, the sorting order is peculiar in the sense that it is sorting according to ROWID. As per my understanding, ROWID is in the same order in which data was loaded in the table. In one case, I have found that ROWID is different from the data load order. My requirement is to order both Oracle data and SQL Server data so that I can compare them row by row. In the absence of a key and an index, what is the parameter based on which Oracle sorts the data for a table?

Neither Oracle nor SQL Server will guarantee a sort order in your SELECT statement unless you also include an ORDER BY clause. You have discovered that the order the records were inserted into the table is not necessarily the order in which the data comes out. Even SQL Server can show this "problem." Use the ORDER BY clause in both Oracle and SQL Server if you want to ensure the ordering of your result set: 
 SELECT <column> FROM <table> ORDER BY <column>;

Dig Deeper on Oracle and SQL

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close