]> git.ipfire.org Git - thirdparty/sqlalchemy/sqlalchemy.git/commit
Use FETCH FIRST N ROWS / OFFSET for Oracle LIMIT/OFFSET
authorMike Bayer <mike_mp@zzzcomputing.com>
Thu, 7 Jul 2022 16:07:39 +0000 (12:07 -0400)
committerMike Bayer <mike_mp@zzzcomputing.com>
Wed, 20 Jul 2022 15:02:24 +0000 (11:02 -0400)
commit5fc46b192b5005fa6962110a683abf1d296786d8
treee0988fe61c6d4b79c71dc84b265885a13df6f74f
parentd5e31d130808c94f09e51e9afb222c4efa63875c
Use FETCH FIRST N ROWS / OFFSET for Oracle LIMIT/OFFSET

Oracle will now use FETCH FIRST N ROWS / OFFSET syntax for limit/offset
support by default for Oracle 12c and above. This syntax was already
available when :meth:`_sql.Select.fetch` were used directly, it's now
implied for :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset` as
well.

I'm currently setting this up so that the new syntax renders
in Oracle using POSTCOMPILE binds.    I really have no indication
if Oracle's SQL optimizer would be better with params
here, so that it can cache the SQL plan, or if it expects
hardcoded numbers for these. Since we had reports that the previous
ROWNUM thing really needed hardcoded ints, let's guess
for now that hardcoded ints would be preferable.  it can be turned
off with a single boolean if users report that they'd prefer
real bound values.

Fixes: #8221
Change-Id: I812ec24ffc947199866947b666d6ec6e6a690f22
doc/build/changelog/unreleased_20/8221.rst [new file with mode: 0644]
lib/sqlalchemy/dialects/mssql/base.py
lib/sqlalchemy/dialects/oracle/base.py
lib/sqlalchemy/sql/compiler.py
test/dialect/oracle/test_compiler.py
test/dialect/oracle/test_dialect.py
test/orm/test_lockmode.py
test/requirements.py