Does 'Select' always order by primary key?

Question

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.

Answer 1

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.

Answer 2

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