In SQL queries, the join order hints include the LEADING and ORDERED hints.
Hint type |
Description |
|---|---|
LEADING |
Specifies the join order based on the tables specified in the hint. If only some tables in the FROM clause are specified, those tables are used as the driving tables for the joins. |
ORDERED |
Specifies the join order based on the tables in 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 only specifies a specific join order, which may be affected by rewriting.
LEADING Hint
The LEADING hint instructs the optimizer to join tables in the specified order, offering greater flexibility compared to the ORDERED hint. With LEADING, you can explicitly define any valid expected join order. In contrast, the ORDERED hint only allows specifying a specific join order, which can be affected by rewriting. It is recommended to use the LEADING hint to directly and explicitly define the desired join order.
Syntax
/*+ LEADING ( [ @queryblock ] tablespec [ tablespec ]... ) */
Considerations
The LEADING hint takes effect only when the specified table name matches the table name in the query.
If the
table_namespecified in the hint does not exist, theLEADINGhint is ineffective.If the hint contains duplicate tables, the
LEADINGhint is ineffective.If the optimizer cannot find the corresponding table during join optimization, the join order specified for this table and subsequent tables is ineffective, while the join order specified before this table remains valid.
If the join dependencies in the join graph prevent the specified tables from being joined in the specified 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 higher priority than 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, but 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 employees, departments, and job_history tables in that order.