In SQL queries, hints related to join order are ORDERED hints and LEADING hints.
| Hint | Description |
|---|---|
LEADING |
Joins tables in the order specified in the hint. If only some tables listed in the FROM clause are specified, the specified tables act as driving tables in the join with other tables. |
ORDERED |
Joins tables in the order listed in the FROM clause. |
Notice
It is recommended that you use the LEADING hint to explicitly specify the expected join order. The ORDERED hint specifies only a particular join order, and the join order is affected by query rewriting.
LEADING hint
The LEADING hint instructs the optimizer to join tables in the specified order. Compared with the ORDERED hint, the LEADING hint offers higher flexibility, as it allows you to explicitly define any valid expected join order. In contrast, the ORDERED hint can only specify a particular join order, and this order may be affected by rewriting. We recommend that you use the LEADING hint to explicitly define the expected join order.
Syntax
/*+ LEADING ( [ @queryblock ] tablespec [ tablespec ]... ) */
Considerations
The LEADING hint takes effect only when the specified table name in the hint exactly matches a table name in the query.
If the
table_namespecified in the hint does not exist, theLEADINGhint fails.If the hint contains duplicate tables, the
LEADINGhint fails.If the optimizer cannot locate a specified table during optimization, the join order specified for that table and all subsequent tables fails, but the join order specified for preceding tables remains valid.
If the specified tables cannot be joined in the order due to dependencies in the join graph, the
LEADINGhint fails.If you specify two or more conflicting
LEADINGhints, the hints fail.If you specify the
ORDEREDhint, it overrides 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 preceding execution plan, LEADING(e j) indicates to the optimizer to first consider table e (employees) and then consider table j (job_history).
ORDERED hint
Specifies to use the default join order in the query. The ORDERED hint has a higher priority than the LEADING hint. If the ORDERED hint is specified, the LEADING hint becomes invalid. When the ORDERED hint is used, the ORDERED hint is not displayed in the OUTLINE DATA, and instead, 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 will join the three tables in the order of employees, departments, and job_history.