Why does Oracle return a specific sequence if the orderby values ​​are identical?

Question

I am confused by a query in Oracle that returns randomly.

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 understand why the database returns data in this particular order or why, since the original table will return the data in this way.

Dropping the Date in TIMESTAMP confirms that all Date values ​​have the same value - 26-OCT-10 00.00.00.000000000 , so I can exclude that there is a difference in the values. However, when I do this, the rows are returned in the order 1, 3, 2.

It drives me crazy, so it really helps reassure me if anyone can explain why this is so.

I would expect this to be returned in a different order each time the query is executed, given that the order legend is the same for each row (thus leaving the order clean).

Thank you very much in advance.

Answer 1

An order is not limited to “pure coincidence” - unless you change the request to:

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

The order is "arbitrary." Instead of “throwing dice" to solve an arbitrary order (which would entail excessive cost), Oracle simply returns the data in the order in which it ran into it, which is likely to be the same for launch in the short term. (In the long run, something can change in the environment to make order different, but still arbitrary).

Answer 2

So far, I don’t know anything about the Oracle implementation, if all things are equal, in priorty, then they will not be in random order: to place them in a random order, you will need to randomly order this order every time (that will be n random operations, where n is the number of elements (using a large O record, this will be O (n)), which is an expensive purchase if the query returns huge (millions) of results.

Returns upon request

they do not need to be randomized in such a way

instead, it simply returns them (which I assume), the order they store on disk

Answer 3

Read this nice little thing from Tom Kyte's blog on order.

When you say that the rows are returned in "1,3,2", then you should order by the column (s) that will tell you that it is in 1,3,2 order to fix it.

Answer 4

It seems that Oracle uses a stable sorting algorithm for sorting.