0

In the Oracle database, the select statement, select * from tablename, does not give output in the order of insertion. In few articles, we have found that the Oracle database stores the row information based on Rowid.

We are using Oracle in a web application based on Java and there is a requirement to display the data in order of insertion in each module. So, applying an order by clause on each table is not feasible and can degrade the application's performance. Is there any other way that the select statement returns data in insertion order?

Oracle version used is "Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.3.0.0.0"

Hima Patel
  • 11
  • 3
  • SQL data is an unordered set, [unless you have an `order by` clause](https://asktom.oracle.com/Misc/order-in-court.html). There *may* be edge cases (like IOTs) where it kind of works without, but you shouldn't rely on it, and that won't help you here anyway. – Alex Poole Jun 10 '21 at 14:00
  • in addition to the other comments, I'd like to address your reference to rowid. The rowid has nothing to do with 'order of insertion'. It is merely the 'physical' address of the row -- rowid values contain information necessary to locate a row: The data object number of the object; The data block in the datafile in which the row resides; The position of the row in the data block (first row is 0). The datafile in which the row resides (first file is 1). The file number is relative to the tablespace. https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm – EdStevens Jun 10 '21 at 15:12

2 Answers2

2

Oracle is a relational database. In it, rows don't have any particular order which means that select statement might return result in different order when you run it several times. Usually it doesn't, but - if there are a lot of inserts/deletes - sooner or later you'll notice such a behavior. Therefore, the only certain way to return rows in desired order is to use - ta-daaa! - order by clause.

Also, you'll have to maintain your own order of insertion. A simple way to do that is to use a column whose source is a sequence.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • 1
    A sequence won't necessarily reflect insertion order - e.g. cached on multiple RAC nodes. A timestamp is more reliable? – Alex Poole Jun 10 '21 at 14:02
  • Aha. Well, I have zero experience on multiple RAC nodes, @Alex, so - thank you for the comment, I hope Hima will consider what you said. – Littlefoot Jun 10 '21 at 14:05
0

I would check here first: previous-post

I would recommend not relying on any ordering unless you specify order by.

But what is the deficiency of adding something like ORDER BY ROWNUM ASC; to your queries? You can trim your result sets ( paginate ) or even only do it to the entities you want to 'maintain insertion order'.

Are you using anything for entity management? Hibernate has some defaults you could use as well. - Post some code examples and can provide additional help.

ABengier
  • 11
  • 3