Hi everybody,
oracle 12c has received the long awaited possibility to properly provide
LIMIT and OFFSET parameters to a select query.
See
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1
for details.
In short this looks like:
SELECT val
FROM some_table
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;
and is so much faster than the nested approach that has been used so far (at
least on my system).
I've successfully implemented some code for SA in order to apply the new paging
method but I'm not sure whether this is the best way to go. It looks like:
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Select
from sqlalchemy.dialects.oracle.base import OracleCompiler
@compiles(Select, 'oracle')
def compile_select(element, compiler, **kw):
# The following line basically suppresses the original, nested approach
in SA for limit/offset:
element._oracle_visit = True
return compiler.visit_select(element, **kw)
def limit_clause(self, select):
# This code overrides the empty method in OracleCompiler:
my_limit_clause = ""
if select._offset:
my_limit_clause += " OFFSET %d ROWS" % select._offset
if select._limit:
my_limit_clause += " FETCH FIRST %d ROWS ONLY" % select._limit
return my_limit_clause
OracleCompiler.limit_clause = limit_clause
I did not find out how to use the @compile directive for overriding the
'limit_clause()' method - is there a way to properly do this?
Thanks,
Ralph
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" 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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
