Method with_hint

lib/sqlalchemy/sql/selectable.py:543–600

r"""Add an indexing or other executional context hint for the given selectable to this :class:`_expression.Select` or other selectable object. .. tip:: The :meth:`_expression.Select.with_hint` method adds hints that are **specific to a single table**

(
        self,
        selectable: _FromClauseArgument,
        text: str,
        dialect_name: str = "*",
    )

    @_generative
    def with_hint(
        self,
        selectable: _FromClauseArgument,
        text: str,
        dialect_name: str = "*",
    ) -> Self:
        r"""Add an indexing or other executional context hint for the given
        selectable to this :class:`_expression.Select` or other selectable
        object.

        .. tip::

            The :meth:`_expression.Select.with_hint` method adds hints that are
            **specific to a single table** to a statement, in a location that
            is **dialect-specific**.  To add generic optimizer hints to the
            **beginning** of a statement ahead of the SELECT keyword such as
            for MySQL or Oracle Database, use the
            :meth:`_expression.Select.prefix_with` method.  To add optimizer
            hints to the **end** of a statement such as for PostgreSQL, use the
            :meth:`_expression.Select.with_statement_hint` method.

        The text of the hint is rendered in the appropriate
        location for the database backend in use, relative
        to the given :class:`_schema.Table` or :class:`_expression.Alias`
        passed as the
        ``selectable`` argument. The dialect implementation
        typically uses Python string substitution syntax
        with the token ``%(name)s`` to render the name of
        the table or alias. E.g. when using Oracle Database, the
        following::

            select(mytable).with_hint(mytable, "index(%(name)s ix_mytable)")

        Would render SQL as:

        .. sourcecode:: sql

            select /*+ index(mytable ix_mytable) */ ... from mytable

        The ``dialect_name`` option will limit the rendering of a particular
        hint to a particular backend. Such as, to add hints for both Oracle
        Database and MSSql simultaneously::

            select(mytable).with_hint(
                mytable, "index(%(name)s ix_mytable)", "oracle"
            ).with_hint(mytable, "WITH INDEX ix_mytable", "mssql")

        .. seealso::

            :meth:`_expression.Select.with_statement_hint`

            :meth:`_expression.Select.prefix_with` - generic SELECT prefixing
            which also can suit some database-specific HINT syntaxes such as
            MySQL or Oracle Database optimizer hints

        """

        return self._with_hint(selectable, text, dialect_name)