Skip to Content
0

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

Dec 15, 2017 at 12:44 PM

51

avatar image
Former Member

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.

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Lars Breddemann
Dec 17, 2017 at 12:17 AM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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

0