Select without order by

Question

It is my understanding that select is not guaranteed to always return the same result.

Following query is not guaranteed to return the same result every time:

select * from myTable offset 10000 limit 100

My question is if myTable is not changed between executions of select (no deletions or inserts) can i rely on it returning the same result set every time?

Or to put it in another way if my database is locked for changes can I rely on select returning the same result?

I am using postgresql.

Answer 1

Tables and result sets (without order by) are simply not ordered. It really is that simple.

In some databases, under some circumstances, the order will be consistent. However, you should never depend on this. Subsequent releases, for instance, might invalidate the query.

For me, I think the simplest way to understand this is by thinking of parallel processing. When you execute a query, different threads might go out and start to fetch data; which values are returned first depends on non-reproducible factors.

Another way to think of it is to consider a page cache that already has pages in memory -- probably from the end of the table. The SQL engine could read the pages in any order (although in practice this doesn't really happen).

Or, some other query might have a row or page lock, so that page gets skipped when reading the records.

So, just accept that unordered means what ordered means. Add an order by if you want data in a particular order. If you use a clustered index key, then there is basically no performance hit.