This topic describes how to use LEADING hints to specify multiple tables for table joins. LEADING hints instruct the optimizer to join tables in a specified order before the tables are joined to other tables.

Usage notes

  • LEADING hints are not supported in nested SQL statements. To minimize the chance of unpredictable results, do not use LEADING hints in clauses.
  • When the optimizer generates the join order for multiple tables, the tables configured with join conditions are prioritized. The system tries to generate Cartesian products only if no execution plans can be generated. Therefore, when you use LEADING hints for two adjacent tables configured with join conditions, one table that immediately follows the other table is joined to the preceding table. Otherwise, the optimizer ignores the LEADING hints because no execution plans can be generated.

Prerequisites

By default, the hint feature is enabled for PolarDB for PostgreSQL(Compatible with Oracle). You can also run the following command to enable this feature:
set enable_hints = true;

Syntax

  • A hint notation starts with /*+ and ends with */. No spaces are allowed between the asterisk (*) and the plus sign (+).
  • A hint consists of a hint name followed by parameters that are enclosed in a pair of parentheses. The parameters are separated by spaces.
  • A hint must immediately follow a keyword such as SELECT, UPDATE, INSERT, MERGE, or DELETE.
  • Hints are case-insensitive.
Note In the following situations, conflicts exist. As a result, the LEADING hints become invalid.
  • If the specified tables cannot be joined in a specified order due to dependency relationships, LEADING hints are ignored.
  • If more than one LEADING hint is specified, all LEADING hints are ignored.
  • If both ORDERED and LEADING hints are specified, the ORDERED hints override all LEADING hints.
  • If the specified name or alias of a table contains a period (.), such as "s.t", all LEADING hints are ignored.
  • If a LEADING hint does not immediately follow a keyword such as SELECT, UPDATE, INSERT, MERGE, or DELETE, the LEADING hint is ignored.

Examples

Suppose that a database contains four tables. The names or aliases of the tables are a, b, c, and d. Each table can be joined to another table.

  • Valid syntax
    ExamplePossible join
    /*+ leading(a) */(((a b) c) d), (((a b) d) c), (((a c) b) d), (((a c) d) b), (((a d) b) c), (((a c) c) b)
    /*+ leading(a b) */(((a b) c) d), (((a b) d) c)
    /*+ leading(a b c) */(((a b) c) d)
    /*+ leading(a b c d) */(((a b) c) d)
    Note ((a b) c) specifies the join priority in the following ascending order: a, b, and c. (c (a b)) specifies the join priority in the following ascending order: c, a, and b.
  • Invalid syntax
    Examples:
    • /* + leading(a) */
    • /*+ leading(a b) leading(a b) */
    • /*+ leading(a b a) */
    • /*+ leading(a b) leading(a)*/
    • /*+ leading(a b) leading(c d) */
    • /*+ leading(a b e) */
    • *+ leading(a b) leading(a c) */
    • /*+ leading() */
    Note The database does not contain a table whose name or alias is e.