In SQL queries, join order-related hints include the ORDERED and LEADING hints.
| Hint Type | Description |
|---|---|
LEADING |
Joins tables in the order specified by the hint. If only some tables in the FROM clause are specified, those tables are used as the driving tables for the joins. |
ORDERED |
Joins tables in the order specified by the FROM clause. |
Notice
We recommend that you use the LEADING hint to explicitly specify the desired join order. This is because the ORDERED hint can only specify a specific join order, and the join order may be affected by rewriting.
LEADING Hint
The LEADING hint indicates the optimizer to join tables in a specified order. It is more flexible than the ORDERED hint, which can only specify a specific join order that may be affected by rewriting. We recommend that you use the LEADING hint to explicitly specify the expected join order.
Syntax
/*+ LEADING ( [ @queryblock ] tablespec [ tablespec ]... ) */
Considerations
The LEADING hint takes effect only when the specified table name matches exactly with the table name in the query.
If the specified
table_namedoes not exist, theLEADINGhint is ignored.If the hint contains duplicate tables, the
LEADINGhint is ignored.If the optimizer cannot find the corresponding table during join optimization, the join order specified for this table and subsequent tables is ignored, while the join order specified for tables before this one remains valid.
If the join graph has dependencies that prevent the specified tables from being joined in the specified order, the
LEADINGhint is ignored.If two or more conflicting
LEADINGhints are specified, theLEADINGhint is ignored.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 the e (employees) table first and the j (job_history) table next.
ORDERED Hint
The ORDERED hint specifies that the optimizer should use the join order specified in the query. The ORDERED hint takes precedence over the LEADING hint. If the ORDERED hint is specified, the LEADING hint is ignored. When the ORDERED hint is specified, the ORDERED hint is not displayed in the outline data, and the complete LEADING hint is printed.
Syntax
/*+ ORDERED */
Examples
Assume that you need to join the employees, departments, and job_history tables:
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 of employees, departments, and job_history.