diff options
Diffstat (limited to 'lib/sqlalchemy')
| -rw-r--r-- | lib/sqlalchemy/dialects/mssql/base.py | 23 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/oracle/base.py | 82 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 45 |
3 files changed, 106 insertions, 44 deletions
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 65f37cfb5..e33ae4dbb 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -1994,9 +1994,6 @@ class MSSQLCompiler(compiler.SQLCompiler): ) ) - def fetch_clause(self, cs, **kwargs): - return "" - def limit_clause(self, cs, **kwargs): return "" @@ -2028,21 +2025,13 @@ class MSSQLCompiler(compiler.SQLCompiler): if self.dialect._supports_offset_fetch and not self._use_top(select): self._check_can_use_fetch_limit(select) - text = "" - - if select._offset_clause is not None: - offset_str = self.process(select._offset_clause, **kw) - else: - offset_str = "0" - text += "\n OFFSET %s ROWS" % offset_str - - limit = self._get_limit_or_fetch(select) + return self.fetch_clause( + select, + fetch_clause=self._get_limit_or_fetch(select), + require_offset=True, + **kw, + ) - if limit is not None: - text += "\n FETCH FIRST %s ROWS ONLY" % self.process( - limit, **kw - ) - return text else: return "" diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 9957417b4..c06da6ffe 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -260,23 +260,38 @@ impact of this change has been mitigated. LIMIT/OFFSET/FETCH Support -------------------------- -Methods like :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset` currently -use an emulated approach for LIMIT / OFFSET based on window functions, which -involves creation of a subquery using ``ROW_NUMBER`` that is prone to -performance issues as well as SQL construction issues for complex statements. -However, this approach is supported by all Oracle versions. See notes below. - -When using Oracle 12c and above, use the :meth:`_sql.Select.fetch` method -instead; this will render the more modern -``FETCH FIRST N ROW / OFFSET N ROWS`` syntax. +Methods like :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset` make +use of ``FETCH FIRST N ROW / OFFSET N ROWS`` syntax assuming +Oracle 12c or above, and assuming the SELECT statement is not embedded within +a compound statement like UNION. This syntax is also available directly by using +the :meth:`_sql.Select.fetch` method. + +.. versionchanged:: 2.0 the Oracle dialect now uses + ``FETCH FIRST N ROW / OFFSET N ROWS`` for all + :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset` usage including + within the ORM and legacy :class:`_orm.Query`. To force the legacy + behavior using window functions, specify the ``enable_offset_fetch=False`` + dialect parameter to :func:`_sa.create_engine`. + +The use of ``FETCH FIRST / OFFSET`` may be disabled on any Oracle version +by passing ``enable_offset_fetch=False`` to :func:`_sa.create_engine`, which +will force the use of "legacy" mode that makes use of window functions. +This mode is also selected automatically when using a version of Oracle +prior to 12c. + +When using legacy mode, or when a :class:`.Select` statement +with limit/offset is embedded in a compound statement, an emulated approach for +LIMIT / OFFSET based on window functions is used, which involves creation of a +subquery using ``ROW_NUMBER`` that is prone to performance issues as well as +SQL construction issues for complex statements. However, this approach is +supported by all Oracle versions. See notes below. Notes on LIMIT / OFFSET emulation (when fetch() method cannot be used) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -If using :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset`, -or with the ORM the :meth:`_orm.Query.limit` and :meth:`_orm.Query.offset` methods, -and the :meth:`_sql.Select.fetch` method **cannot** be used instead, the following -notes apply: +If using :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset`, or with the +ORM the :meth:`_orm.Query.limit` and :meth:`_orm.Query.offset` methods on an +Oracle version prior to 12c, the following notes apply: * SQLAlchemy currently makes use of ROWNUM to achieve LIMIT/OFFSET; the exact methodology is taken from @@ -296,10 +311,6 @@ notes apply: :ref:`change_4808`. -* A future release may use ``FETCH FIRST N ROW / OFFSET N ROWS`` automatically - when :meth:`_sql.Select.limit`, :meth:`_sql.Select.offset`, :meth:`_orm.Query.limit`, - :meth:`_orm.Query.offset` are used. - .. _oracle_returning: RETURNING Support @@ -1001,6 +1012,33 @@ class OracleCompiler(compiler.SQLCompiler): return "RETURNING " + ", ".join(columns) + " INTO " + ", ".join(binds) + def _row_limit_clause(self, select, **kw): + """ORacle 12c supports OFFSET/FETCH operators + Use it instead subquery with row_number + + """ + + if ( + select._fetch_clause is not None + or not self.dialect._supports_offset_fetch + ): + return super()._row_limit_clause( + select, use_literal_execute_for_simple_int=True, **kw + ) + else: + return self.fetch_clause( + select, + fetch_clause=self._get_limit_or_fetch(select), + use_literal_execute_for_simple_int=True, + **kw, + ) + + def _get_limit_or_fetch(self, select): + if select._fetch_clause is None: + return select._limit_clause + else: + return select._fetch_clause + def translate_select_structure(self, select_stmt, **kwargs): select = select_stmt @@ -1017,6 +1055,7 @@ class OracleCompiler(compiler.SQLCompiler): # if fetch is used this is not needed if ( select._has_row_limiting_clause + and not self.dialect._supports_offset_fetch and select._fetch_clause is None ): limit_clause = select._limit_clause @@ -1375,6 +1414,8 @@ class OracleDialect(default.DefaultDialect): supports_alter = True max_identifier_length = 128 + _supports_offset_fetch = True + insert_returning = True update_returning = True delete_returning = True @@ -1435,6 +1476,7 @@ class OracleDialect(default.DefaultDialect): use_binds_for_limits=None, use_nchar_for_unicode=False, exclude_tablespaces=("SYSTEM", "SYSAUX"), + enable_offset_fetch=True, **kwargs, ): default.DefaultDialect.__init__(self, **kwargs) @@ -1442,6 +1484,9 @@ class OracleDialect(default.DefaultDialect): self.use_ansi = use_ansi self.optimize_limits = optimize_limits self.exclude_tablespaces = exclude_tablespaces + self.enable_offset_fetch = ( + self._supports_offset_fetch + ) = enable_offset_fetch def initialize(self, connection): super(OracleDialect, self).initialize(connection) @@ -1458,6 +1503,9 @@ class OracleDialect(default.DefaultDialect): self.use_ansi = False self.supports_identity_columns = self.server_version_info >= (12,) + self._supports_offset_fetch = ( + self.enable_offset_fetch and self.server_version_info >= (12,) + ) def _get_effective_compat_server_version_info(self, connection): # dialect does not need compat levels below 12.2, so don't query diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 87d031cc2..08b876e69 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -4266,19 +4266,44 @@ class SQLCompiler(Compiled): text += " OFFSET " + self.process(select._offset_clause, **kw) return text - def fetch_clause(self, select, **kw): + def fetch_clause( + self, + select, + fetch_clause=None, + require_offset=False, + use_literal_execute_for_simple_int=False, + **kw, + ): + if fetch_clause is None: + fetch_clause = select._fetch_clause + fetch_clause_options = select._fetch_clause_options + else: + fetch_clause_options = {"percent": False, "with_ties": False} + text = "" + if select._offset_clause is not None: - text += "\n OFFSET %s ROWS" % self.process( - select._offset_clause, **kw - ) - if select._fetch_clause is not None: + offset_clause = select._offset_clause + if ( + use_literal_execute_for_simple_int + and select._simple_int_clause(offset_clause) + ): + offset_clause = offset_clause.render_literal_execute() + offset_str = self.process(offset_clause, **kw) + text += "\n OFFSET %s ROWS" % offset_str + elif require_offset: + text += "\n OFFSET 0 ROWS" + + if fetch_clause is not None: + if ( + use_literal_execute_for_simple_int + and select._simple_int_clause(fetch_clause) + ): + fetch_clause = fetch_clause.render_literal_execute() text += "\n FETCH FIRST %s%s ROWS %s" % ( - self.process(select._fetch_clause, **kw), - " PERCENT" if select._fetch_clause_options["percent"] else "", - "WITH TIES" - if select._fetch_clause_options["with_ties"] - else "ONLY", + self.process(fetch_clause, **kw), + " PERCENT" if fetch_clause_options["percent"] else "", + "WITH TIES" if fetch_clause_options["with_ties"] else "ONLY", ) return text |