In SQL queries, hints related to join order include the LEADING and ORDERED 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 join. |
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 instructs 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. In contrast, the ORDERED hint can only specify a specific join order, which may be affected by rewriting. We recommend using 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 matches the table name in the query.
If the specified
table_namedoes 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 for tables before this one remains valid.
If the join graph's dependencies prevent the specified tables from being joined in the specified order first, the
LEADINGhint is ineffective.If two or more conflicting
LEADINGhints are specified, theLEADINGhint is ineffective.If the
ORDEREDhint is specified, 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, then the j (job_history) table.
ORDERED Hint
The connection sequence in the query uses the default connection sequence. The ORDERED hint has higher priority than the LEADING hint. When the ORDERED hint exists, the LEADING hint is ignored. When you use the ORDERED hint, the ORDERED hint does not appear in the OUTLINE DATA. Instead, the entire LEADING hint is displayed.
Syntax
/*+ ORDERED */
Examples
Let's assume that the employees, departments, and job_history tables 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 joins the three tables in this order: employees, departments, and job_history.