Default order behavior in oracle database if column values are the same-CodePudding

Imagine a table (my_table):

id  modified_date
--  -------------------
5   2022-05-12 07:00:00
1   2022-05-12 07:00:00
2   2022-05-12 07:00:00
22  2022-05-12 07:00:00
4   2022-05-12 07:00:00
3   2022-05-12 07:00:00

My select statement is as follow:

SELECT * FROM my_table ORDER BY modified_date ASC, id ASC;

Which gives me:

id  modified_date
--  -------------------
1   2022-05-12 07:00:00
2   2022-05-12 07:00:00
3   2022-05-12 07:00:00
4   2022-05-12 07:00:00
5   2022-05-12 07:00:00
22  2022-05-12 07:00:00

By default, how does Oracle order things if id ASC was omitted when the sort column values are the same?

CodePudding user response：

If you don't specify order by, then the outcome is unknown. As Oracle stores data into table in unordered way (think of it as of a basket full of apples - which apple was first added? Which was the 5th? The last? You don't know), selecting rows might end up with a different sort every time you run the select statement.

You - most probably - won't notice that, especially on small tables which aren't modified. But, for large tables that are frequently modified (rows being deleted, new rows added, ...), "today's" select might (and probably will) return result which is different from "yesterday's" select.

Basically, you don't know what you'll get.

Back to apples? If you say "give me apples and sort them by their weight", then yes - you'll first get the smallest apple, while the last one will be the largest. That's what order by does.