All Products
Search
Document Center

Hints related to join orders

Last Updated: Jun 18, 2021

LEADING Hint

The LEADING hint instructs the optimizer to use the specified set of tables as the prefix in the execution plan. This hint can be used to specify the join order of tables. This hint is more versatile than the ORDERED hint.

The LEADING hint uses the following syntax:

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

The LEADING hint performs strict checks to ensure that tables are joined in the specified order. If the table_name that is specified in a LEADING hint does not exist, the hint is ignored. If duplicate tables are found in a LEADING hint, the hint is ignored. If the optimizer attempts to join tables but cannot locate a table to be joined, the join orders specified for this table and the tables after the table become invalid. The join orders specified for the tables before this table remain valid. If the specified table cannot be first joined in the specified order due to the dependencies in the join graph, the LEADING hint is ignored. If you specify two or more conflicting LEADING hints, these LEADING hints are ignored. If you specify an ORDERED hint, it overrides all LEADING hints.

The following statement provides an example:

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;

ORDERED Hint

The ORDERED hint instructs a database to join tables in the order in which they appear in the FROM clause. We recommend that you use the LEADING hint. It is more versatile than the ORDERED hint.

The ORDERED hint uses the following syntax:

/*+ ORDERED */

When you omit the ORDERED hint in an SQL statement that performs a join operation, the optimizer chooses the order in which the tables are joined. However, the optimizer does not know the number of rows to be selected from each table. In this case, you can use the ORDERED hint to specify a join order. This allows you to select internal and external tables in a better way than the optimizer. If you rewrite the specified ORDERED hint, the tables are joined in the order as you rewrite in the FROM clause of the statement.