Why does Oracle return specific sequence if 'orderby' values are identical?

Question

I'm bewildered by a query in Oracle which is returning in a seemingly random order.

SELECT
  Date,
  Amount
FROM MyTable
WHERE Date = '26-OCT-2010'
ORDER BY Date

This returns the following data:

   |  Date       |  Amount
--------------------------
1  |  26-OCT-10  |  85
2  |  26-OCT-10  |  9
3  |  26-OCT-10  |  100

I cannot fathom why the database returns the data in this specific order, or why, since the original table would return the data this way.

Casting Date to TIMESTAMP confirms that all Date values are the same value - 26-OCT-10 00.00.00.000000000, therefore, I can' rule out that there is a difference in the values. However, when I do this, the rows return in the order of 1, 3, 2.

This is driving me mad so it would really help soothe me if someone could provide an explanation as to why this is.

I would expect this to return in a different order every time the query is run, given that the order conditional is identical on every row (thus leaving the ordering to pure chance).

Many thanks in advance.

Answer 1

Sounds like Oracle uses a stable sorting algorithm for doing sorting.

Answer 2

The ordering is not down to "pure chance" - unless you amend the query to:

SELECT
  Date,
  Amount
FROM MyTable
WHERE Date = '26-OCT-2010'
ORDER BY Date, DBMS_RANDOM.VALUE;

The ordering is "arbitrary". Rather than "throw dice" to decide the arbitrary order (which would incur some unnecessary cost), Oracle just returns the data in the order it encountered it - which is likely to be the same from run to run in the short term. (In the long term, something may change in the environment to make the ordering different - but still arbitrary).

Answer 3

while I know nothing about the implimentation of Oracle, if all things being equal, in priorty, then they won't be in a Random order: to put them in a random order would require randomising there order each time, (which would be n randomising operations, where n is number of items (using big O notation it would be O(n)), which is an computationally expensive thing, if the queery would return Huge (millions) of resaults. returned by the querry there is no need for them to be randomised in this way,. instead it just returns them in (what i'm guessing is) the order they are stored on disk

Answer 4

Read this nice piece from Tom Kyte's blog regarding ordering.

When you say the rows come back in "1,3,2" order then you should order by the column(s) that tell you that it's in 1,3,2 order to fix it.