We are trying to confirm the expected behavior of Oracle for how it handles sorting on a column where many of the data points are the same value. For example if I queried the following table and sorted on the Data column I might get changing result order on each query like the two iterations below.
(First query)
| ID | Data |
-----------------
| 1 | James |
| 2 | James |
| 3 | James |
(Second query)
| ID | Data |
-----------------
| 3 | James |
| 1 | James |
| 2 | James |
We believe it is expected that this can happen and that Oracle does not guarantee the order of row for this type of query. Regardless,we know we can update the application to add a second sort on a unique column, but that is a huge development effort. So wanted to know if perhaps there is something we can do on the database side to avoid making large application changes.?
Correct, when sorting on non-unique values the database can return rows with the same value in any order.
The only way to guarantee that you'll get the rows in the order you want is to add a unique column to the sort.