How does "select ... limit x offset y" work? Is it deterministic?

[ch4143]

Hello,

as far as I understand SQL there is no garantee that a "select top x"-statement would always return the same resultset. As there is no explicit "order by" the resultset is not deterministic.
Is this the same with "select ... limit x offset"?
Do I need to use an "order by" to be sure to get the same results every time (no chages to the table)? Or is there an implicit order? If so: On which columns (index?, what if no primary key on the table?)

I am sorry for that bunch of questions on that topic, but I cannot find anything on that in the documentation.

[lbreddemann · Accepted solution]

Yes, you need to provide an order by to get a deterministic result set order; limiting the returned records does not change that. Why would it?

To deliver the limited result set, the database has to first build the complete result set and then provide the chosen part of it to the client. The main savings in terms of processing efforts here lie with the final materialization step, which only needs to be performed for the rows that get to be delivered to the client.

This is observable by using EXPLAIN PLAN and PlanViz on statements with and without the LIMIT clause.

[ch4143 · Accepted solution]

Thank you very much for that information.

I was wondering why my idea did not work. I query a table in chuncks of 10 Mio. (in parallel)

select some_columns
from sometable
limit 10000000 offset x0000000
order by combined_primary_key_on_some_columns

In fact it did what it should, but the table was under replication from another system with an intervall of 1 second. So the chuncks are not what I suposed them to be. Performace was very poor in this case (you mentioned why - Thanks).

Puting an identity column on that table and a query on this column did the job.

select some_columns
from sometable
where my_id between x0000000 and y0000000