The optimizer's default join order can produce suboptimal query plans when it lacks sufficient statistics or misjudges selectivity. LEADING hints let you override that decision by specifying which tables the optimizer must start with, while leaving it free to determine the best order for the remaining tables.
The driving table — the first table in a join sequence — has an outsized effect on performance. Choose a table whose filter eliminates the most rows early. Fewer rows passed to later joins means less work overall. When the optimizer picks the wrong driving table, use a LEADING hint to correct it.
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.
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
Before you begin, ensure that you have:
A PolarDB for PostgreSQL (Compatible with Oracle) instance with the hint feature enabled (enabled by default)
To enable the hint feature manually, run:
set enable_hints = true;Syntax
Hints follow this format:
/*+ hint_name(parameters) */Syntax rules:
Start the hint with
/*+and end it with*/. No spaces are allowed between*and+.Separate parameters with spaces.
Place the hint immediately after a DML keyword: SELECT, UPDATE, INSERT, MERGE, or DELETE.
Hints are case-insensitive.
When LEADING hints are ignored
The optimizer silently ignores LEADING hints in the following situations:
| Situation | Result |
|---|---|
| The specified tables cannot be joined in the given order due to dependency relationships | All LEADING hints are ignored |
| More than one LEADING hint is specified | All LEADING hints are ignored |
| Both ORDERED and LEADING hints are specified | ORDERED overrides all LEADING hints |
A table name or alias contains a period (.), such as s.t | All LEADING hints are ignored |
| The hint does not immediately follow SELECT, UPDATE, INSERT, MERGE, or DELETE | The LEADING hint is ignored |
| The LEADING hint is used inside a nested SQL statement or subquery | The LEADING hint is ignored |
Examples
The following examples use four tables — a, b, c, and d — where each table can be joined to any other.
Valid syntax
| Hint | Possible join orders |
|---|---|
/*+ 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) |
((a b) c) means a and b are joined first, then the result is joined to c. (c (a b)) means c is joined first, then a and b. The more tables you specify, the more constrained the join order becomes. Specifying all tables fixes a single join order.Invalid syntax
The following examples are all invalid:
/* + leading(a) */— space between/and*/*+ leading(a b) leading(a b) */— duplicate LEADING hint/*+ leading(a b a) */— repeated table in parameter list/*+ leading(a b) leading(a) *//*+ leading(a b) leading(c d) *//*+ leading(a b e) */— tableedoes not exist in the database*+ leading(a b) leading(a c) */— missing/at the start/*+ leading() */— empty parameter list