Hi all,
First of all, I'd like to thank Jeremy and all the contributors for the
sequel gem.
We're using it for five years now, it helps us getting job done!
tl;dr: Oracle 12 has limit/offset feature and I want to learn Sequel to use
it if available, any hints?
I have a table in oracle, for 16M rows, and I'm paging through
with Dataset#limit method.
DB[:table].limit(10) produces following query:
SELECT * FROM table WHERE ROWNUM <= 10
So far so good. Now, let's add offset parameter to that:
DB[:table].limit(10, 0)
This results in following query:
SELECT [list of columns of table] FROM (
SELECT "t1".*, ROWNUM "x_sequel_row_number_x" FROM (
SELECT * FROM table
) "t1"
) "t1"
WHERE (("x_sequel_row_number_x" > 0) AND ("x_sequel_row_number_x" <= (0 +
10)))
The first query in my case completes in 0.03 secs
The second query (which does esentially the same) runs for 3 minutes (187
secs)
Comparing execution plans for these queries I have noted, that for the
first query database understands in needs no more than ten rows, and stops
after receiving them.
For the second query it enumerates all of the table rows, and then extra
rows are filtered out.
The fix for this case is obvious (compare offset with zero, and use the
first query then).
But for other values of offset there were no other way, until in version 12
oracle added support for limit/offset using following syntax:
SELECT * FROM table OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
This query takes the same amount of time as the first one (ROWNUM <= 10).
So, here is my question:
What would be appropriate place to check the version for database server
and enable new limit/offset syntax if database supports it?
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
