[sqlalchemy] Please advice with new oracle 'OFFSET x ROWS FETCH NEXT x ROWS' approach in 12c

[Ralph Heinkel]

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

[Mike Bayer]

Post by Ralph Heinkel
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.

wow!

Post by Ralph Heinkel
SELECT val FROM some_table ORDER BY val OFFSET 4 ROWS FETCH NEXT 4
ROWS ONLY;

it's like OK we can add a LIMIT/OFFSET feature, but first! Let's send
it off to the Oracle department of "make this syntax as awkward and
obtuse as possible - OraTuse! (tm)"

Post by Ralph Heinkel
and is so much faster than the nested approach that has been used so
far (at least on my system).

I am sure.

Post by Ralph Heinkel
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.
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Select
from sqlalchemy.dialects.oracle.base import OracleCompiler
@compiles(Select, 'oracle')
# The following line basically suppresses the original, nested
element._oracle_visit = True
return compiler.visit_select(element, **kw)
my_limit_clause = ""
my_limit_clause += " OFFSET %d ROWS" % select._offset
my_limit_clause += " FETCH FIRST %d ROWS ONLY" % select._limit
return my_limit_clause
OracleCompiler.limit_clause = limit_clause
the 'limit_clause()' method - is there a way to properly do this?

Well first off, SQLAlchemy *has* to add this properly, so let me add a
ticket:
https://bitbucket.org/zzzeek/sqlalchemy/issues/3543/support-new-oracle-12c-offset-top-n

Secondly, OK I think the monkeypatch you're doing is almost as good as
we can do for the moment, though we can make this a little cleaner since
we can in fact make a new compiler class totally and associate it with a
dialect:

from sqlalchemy.sql import compiler


def patch_engine(engine):
class Oracle12CCompiler(engine.dialect.statement_compiler):
def visit_select(self, element, **kw):
return compiler.SQLCompiler.visit_select(self, 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
engine.dialect.statement_compiler = Oracle12CCompiler


if __name__ == '__main__':
from sqlalchemy import create_engine, select, table, column
import mock

e = create_engine(
"oracle://",

# only because I don't have cx_oracle installed here, this
# is not part of the example
module=mock.Mock(version="10.2", paramstyle="named")
)
patch_engine(e)

t = table('foo', column('bar'))
stmt = select([t]).limit(5).offset(12)

"""
SELECT foo.bar
FROM foo OFFSET 12 ROWS FETCH FIRST 5 ROWS ONLY
"""
print stmt.compile(e)


thanks for bringing this to my attention!

Post by Ralph Heinkel
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
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Ralph Heinkel]

Post by Ralph Heinkel
SELECT val
FROM some_table
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;
it's like OK we can add a LIMIT/OFFSET feature, but first! Let's send it
off to the Oracle department of "make this syntax as awkward and obtuse as
possible - OraTuse! (tm)"
+1 ;-) You are right, the syntax is really awkward - they should have

just used something like Postgresql does, very concise, very easy to read.
But hey - it's Oracle, it must be complicated to justify the price.

Post by Ralph Heinkel
and is so much faster than the nested approach that has been used so far (at least on my system).
I am sure.
OK, that was a bit too optimistic, the speed increase depends very much on

the table/view I'm applying it to. There is some increase in speed, never a
decrease.
The real difference in speed that I believed (hoped) I saw seems to be
related on our db load at the time when I measured it. So I think we have
to collect experiences from different side whether or not it really makes a
difference.

But anyway, even though the syntax is slightly strange I prefer it much
over the original approach which required to nest two select statements.

Ralph