In SQL queries, the hints related to the join order include the ORDERED hint and the LEADING hint.
Hint type |
Description |
|---|---|
LEADING |
Joins are performed in the order of tables specified in the hint. When only some tables in the FROM clause are specified, the specified tables act as driving tables and are joined with other tables. |
ORDERED |
The table join order is determined by the order of 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 particular join order, and this order may be affected by query rewrite.
LEADING Hint
The LEADING hint instructs the optimizer to join tables in a specified order, offering greater flexibility than the ORDERED hint. The LEADING hint allows you to explicitly define any legal expected join order. In contrast, the ORDERED hint can only specify a particular join order, and this order is subject to rewrite. It is recommended to use the LEADING hint to directly and explicitly specify the desired join order.
Syntax
/*+ LEADING ( [ @queryblock ] tablespec [ tablespec ]... ) */
Usage instructions and considerations
The LEADING hint takes effect only when the specified table name exactly matches a table name that appears in the query.
If the
table_namespecified in the hint does not exist, theLEADINGhint becomes invalid.If a duplicate table is found in the hint, the
LEADINGhint becomes invalid.If a table cannot be found during the join by the optimizer, the join order specified for that table and subsequent tables becomes invalid. The order specified for tables preceding it remains valid.
The
LEADINGhint is invalid if the specified tables cannot be joined in the specified order due to dependencies in the join graph.If two or more conflicting
LEADINGhints are specified, theLEADINGhint becomes invalid.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 execution plans
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 first considers table e (employees) and then table j (job_history).
ORDERED Hint
The default join order is used in the query. The ORDERED hint has a higher priority than the LEADING hint. When the ORDERED hint is present, the LEADING hint becomes invalid. When the ORDERED hint is used, it is not displayed in the OUTLINE DATA; instead, the complete LEADING hint is printed.
Syntax
/*+ ORDERED */
Examples
Assume we have three tables, employees, departments, and job_history, that need 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 will join these three tables in the order of employees, departments, and job_history.