In SQL queries, join order hints include the LEADING and ORDERED hints.
| Hint type | Description |
|---|---|
LEADING |
Joins tables in the order specified in the hint. If only some tables are specified in the FROM clause, those tables are used as the driving tables for the joins. |
ORDERED |
Joins tables in the order specified in the FROM clause. |
Notice
We recommend that you use the LEADING hint to explicitly specify the expected join order. This is because the ORDERED hint can only specify a specific join order, and this order may be affected by rewriting.
LEADING Hint
The LEADING hint indicates the optimizer to join tables in the specified order, offering greater flexibility compared to the ORDERED hint. The LEADING hint allows you to explicitly define any valid expected join order, while the ORDERED hint only specifies a particular join order that may be affected by rewriting. It is recommended to use the LEADING hint to directly and explicitly specify the desired join order.
Syntax
/*+ LEADING ( [ @queryblock ] tablespec [ tablespec ]... ) */
Considerations
The LEADING hint is effective only if the specified table name exactly matches the table name in the query.
If the specified
table_namedoes not exist, theLEADINGhint is ineffective.If there are duplicate tables in the hint, the
LEADINGhint is ineffective.If the optimizer cannot find the corresponding table during the join optimization, the join order specified for that table and subsequent tables is ineffective, while the join order specified for tables before it remains valid.
If the join graph's dependencies prevent the specified tables from being joined in the given order, the
LEADINGhint is ineffective.If two or more conflicting
LEADINGhints are specified, theLEADINGhint is ineffective.If you specify the
ORDEREDhint, it will override allLEADINGhints.
Examples
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;
-- View the execution plan
EXPLAIN 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;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =================================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------- |
| |0 |HASH JOIN | |3 |16 | |
| |1 |├─HASH JOIN | |3 |10 | |
| |2 |│ ├─TABLE FULL SCAN|E |3 |5 | |
| |3 |│ └─TABLE FULL SCAN|J |3 |4 | |
| |4 |└─TABLE FULL SCAN |D |3 |4 | |
| =================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([E.EMPLOYEE_ID], [E.FIRST_NAME], [E.LAST_NAME], [E.HIRE_DATE], [E.DEPARTMENT_ID], [D.DEPARTMENT_ID], [D.DEPARTMENT_NAME], [J.EMPLOYEE_ID], |
| [J.START_DATE], [J.END_DATE], [J.JOB_ID]), filter(nil), rowset=16 |
| equal_conds([E.DEPARTMENT_ID = D.DEPARTMENT_ID]), other_conds(nil) |
| 1 - output([E.EMPLOYEE_ID], [E.FIRST_NAME], [E.LAST_NAME], [E.HIRE_DATE], [E.DEPARTMENT_ID], [J.EMPLOYEE_ID], [J.START_DATE], [J.END_DATE], [J.JOB_ID]), filter(nil), rowset=16 |
| equal_conds([E.HIRE_DATE = J.START_DATE]), other_conds(nil) |
| 2 - output([E.EMPLOYEE_ID], [E.DEPARTMENT_ID], [E.HIRE_DATE], [E.FIRST_NAME], [E.LAST_NAME]), filter(nil), rowset=16 |
| access([E.EMPLOYEE_ID], [E.DEPARTMENT_ID], [E.HIRE_DATE], [E.FIRST_NAME], [E.LAST_NAME]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([E.EMPLOYEE_ID]), range(MIN ; MAX)always true |
| 3 - output([J.START_DATE], [J.EMPLOYEE_ID], [J.END_DATE], [J.JOB_ID]), filter(nil), rowset=16 |
| access([J.START_DATE], [J.EMPLOYEE_ID], [J.END_DATE], [J.JOB_ID]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([J.__pk_increment]), range(MIN ; MAX)always true |
| 4 - output([D.DEPARTMENT_ID], [D.DEPARTMENT_NAME]), filter(nil), rowset=16 |
| access([D.DEPARTMENT_ID], [D.DEPARTMENT_NAME]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([D.DEPARTMENT_ID]), range(MIN ; MAX)always true |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
28 rows in set
In the execution plan above, LEADING(e j) indicates that the optimizer considers table e (employees) first and then table j (job_history).
ORDERED Hint
The ORDERED hint specifies that the join order in the query should follow the default join order. The ORDERED hint has a higher priority than the LEADING hint. If the ORDERED hint is specified, the LEADING hint is ignored. When the ORDERED hint is specified, it is not displayed in the OUTLINE DATA, but the complete LEADING hint is printed.
Syntax
/*+ ORDERED */
Examples
Assume that you have three tables, employees, departments, and job_history, to be joined:
SELECT /*+ ORDERED */ e.employee_id, d.department_name, j.job_id
FROM employees e, departments d, job_history j
WHERE e.department_id = d.department_id
AND e.hire_date = j.start_date;
In this example, the database joins the three tables in the order employees, departments, and job_history.