A basic simple question for all of you DBA.
When I do a select, is it always guaranteed that my result will be ordered by the primary key, or should I specify it with an 'order by'?
I'm using Oracle as my DB.
Asked
Active
Viewed 1.8k times
19
-
Possible duplicate of [What is MySQL row order for "SELECT \* FROM table\_name;"?](https://stackoverflow.com/questions/1949641/what-is-mysql-row-order-for-select-from-table-name) – philipxy Jun 05 '19 at 03:22
6 Answers
45
No, if you do not use "order by" you are not guaranteed any ordering whatsoever. In fact, you are not guaranteed that the ordering from one query to the next will be the same. Remember that SQL is dealing with data in a set based fashion. Now, one database implementation or another may happen to provide orderings in a certain way but you should never rely on that.
BobbyShaftoe
- 28,337
- 7
- 52
- 74
-
1Any chance the records will be returned in the order of the clustered index? – Christian Loris May 05 '09 at 13:33
-
1A very good chance, but not guaranteed. Complicated queries involving joins, views, sub queries, and the like can leave the result set ordered very differently. – Joel Coehoorn May 05 '09 at 13:36
-
ANd it may return sometimes inthe clustered index order and sometimes not. Always use an order by if you want a specific order. – HLGEM May 05 '09 at 13:38
-
5"Any chance?" Yes, somewhere between 0 and 100%, a range large enough that you shouldn't rely on it. Use standard SQL syntax and behavior as much as possible, it eases the task of moving between and/or supporting multiple DBMS vendors greatly (I'm not saying don't optimize for a specific vendor, just do go mad about it - use some common sense, no matter how uncommon it seems to be :-). – paxdiablo May 05 '09 at 13:58
-
I've inherited software that would fail if Oracle returned data not ordered by primary key, and it never did. Of course that was bad software, just mentioning it because Oracle seems very consistent. – Andomar May 05 '09 at 16:12
7
When I do a select, is it always guaranteed that my result will be ordered by the primary key, or should I specify it with an 'order by'?
No, it's by far not guaranteed.
SELECT *
FROM table
most probably will use TABLE SCAN which does not use primary key at all.
You can use a hint:
SELECT /*+ INDEX(pk_index_name) */
*
FROM table
, but even in this case the ordering is not guaranteed: if you use Enterprise Edition, the query may be parallelized.
This is a problem, since ORDER BY cannot be used in a SELECT clause subquery and you cannot write something like this:
SELECT (
SELECT column
FROM table
WHERE rownum = 1
ORDER BY
other_column
)
FROM other_table
Quassnoi
- 413,100
- 91
- 616
- 614
-
Not a problem: SELECT DISTINCT FIRST_VALUE(column) OVER (ORDER BY other_column) FROM table; – Jeffrey Kemp May 25 '09 at 02:56
-
@Jeffrey: it's possible, but the subquery will select all FIRST_VALUE's and HASH UNIQUE them. This will kill all performance it the subquery returns lots of rows. – Quassnoi May 25 '09 at 10:51
3
No, ordering is never guaranteed unless you use an ORDER BY.
The order that rows are fetched is dependent on the access method (e.g. full table scan, index scan), the physical attributes of the table, the logical location of each row within the table, and other factors. These can all change even if you don't change your query, so in order to guarantee a consistent ordering in your result set, ORDER BY is necessary.
Dave Costa
- 47,262
- 8
- 56
- 72
1
It depends on your DB and also it depends on indexed fields.
For example, in my table Users every user has unique varchar(20) field - login, and primary key - id.
And "Select * from users" returns rowset ordered by login.
Roman
- 64,384
- 92
- 238
- 332
0
If you desire specific ordering then declare it specifically using ORDER BY.
What if the table doesn't have primary key?
pmcilreavy
- 3,076
- 2
- 28
- 37
-
If the table doesn't have a primary key, you have other problems than the order in which the data is returned. – Jonathan Leffler May 05 '09 at 13:58
-
