On Tuesday, July 11, 2017 at 6:49:37 AM UTC-7, [email protected] wrote: > > 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? >
There is already code for getting the database version. Overriding Dataset#select_limit_sql in the shared oracle adapter is probably the best way to enable the limit/offset syntax if the database version is high enough, but you'll also need to disable the emulation in select_sql in that case. Check the shared mssql adapter for a similar change (MSSQL <2012 needs offset emulation, 2012+ does not). Guess I'll need to setup an Oracle 12 VM so I can start testing it. :) Thanks, Jeremy -- 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.
