Hints related to join orders

Hints related to join orders are ORDERED and LEADING.

ORDERED Hint

The ORDERED hint instructs the database to join tables in the order in which they appear in the FROM clause.

Syntax of the ORDERED hint:

/*+ ORDERED */

If you omit the ORDERED hint in an SQL statement that requires a join, the optimizer chooses the table join order. If the number of rows to select from each table is unknown to the optimizer, you can use the ORDERED hint to specify the join order. This allows you to select left-side and right-side tables better than the optimizer. If the statement is rewritten after you specify the ORDERED hint, the join order specified in the FROM clause in the new statement is used.

LEADING Hint

The LEADING hint instructs the optimizer to use the specified table as the driving table in the execution plan to specify the join order for tables. We recommend that you use the LEADING hint, which is more versatile than the ORDERED hint.

Syntax of the LEADING hint:

/*+ LEADING ( [ @queryblock ] tablespec [ tablespec ]...  ) */

The LEADING hint strictly checks the join order against the order specified by the user. The rules are:

• If table_name specified by the hint does not exist, the LEADING hint becomes invalid.

• If a duplicate table exists in the hint, the LEADING hint becomes invalid.

• If the corresponding table cannot be found when the optimizer joins tables, the join order specified for the table and its subsequent tables become invalid, but the order before the table is still valid.

• If the specified tables cannot be joined first in the specified order due to dependencies in the join graph, the LEADING hint becomes invalid.

• If you specify two or more conflicting LEADING hints, the LEADING hints become invalid.

• If you specify the ORDERED hint, it overwrites all LEADING hints.

Sample code of the LEADING hint:

SELECT /*+ LEADING(e j) */ *
    FROM employees e, departments d, job_history j
    WHERE e.department_id = d.department_id
      AND e.hire_date = j.start_date;