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

Order of selected rows without an ORDER BY clause

When selecting data without an order by clause, Oracle makes no guarantee to the ordering of rows. My coworker argues that he and I should be able to create a table (separate instances), insert data (in an identical fashion), query it with a simple select statement and get identical results. I concede that it works most of the time but I disagree that it will always work.

Am I wrong? Is there a scenario I can use to prove my case? For example, will the ordering change if only part of the results are cached in the SGA?


According to relational database theory, there is no ordering of rows in a table, just as their is no ordering of columns. That is why no RDBMS vendor makes a guarantee of ordering unless you use an ORDER BY clause.

That being said, the database does put the data into tables in a particular order. In other words, there is a physical order to the data on disk. If the table is empty, and you put rows of data into it, then with a simple "SELECT * FROM table" command, you will get the rows out in the order they were inserted into the table. This is only due to the fact of how Oracle retrieves rows based on physical ordering for a full table scan. Now if rows are deleted and inserted among existing rows, then the physical ordering can be changed and the results will change as well.

For More Information


This was last published in June 2002

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

"If the table is empty, and you put rows of data into it, then with a simple "SELECT * FROM table" command, you will get the rows out in the order they were inserted into the table. "

Not necessarily true. If you store a small row, followed by a large row followed by a small one, you may get small, small, large out. Also a parallel query will invalidate your answer. There is NO guarantee without an ORDER BY, even in the simplest of cases.
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close