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
- Dozens more answers to tough Oracle questions from Brian Peasland are available here.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.