Oracle getting started Tutorial: leadingvsorderedhint

Oderedhint can indicate the sequence of tables following the from keyword used by oracle for join! Cbo performs join based on the order of the tables following from.

The odered hint can indicate the sequence of tables following the from keyword used by oracle for join! Cbo performs join based on the order of the tables following from.

Leading hint indicates that Oracle uses the table specified in leading as the driving table,
For example, the normal access plan is as follows:
SCOTT @> select e. ename, hiredate, B. comm
2 from emp e, bonus B
3 where e. ename = B. ename;
Execution Plan
----------------------------------------------------------
Plan hash value: 1125985041
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
----------------------------------------------------------------------------
| 0 | select statement | 1 | 34 | 6 (17) | 00:00:01 |
| * 1 | hash join | 1 | 34 | 6 (17) | 00:00:01 |
| 2 | table access full | BONUS | 1 | 20 | 2 (0) | 00:00:01 |
| 3 | table access full | EMP | 14 | 196 | 3 (0) | 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-access ("E". "ENAME" = "B". "ENAME ")

In the leading prompt, specify the emp table as the driver table.
SCOTT @> select/* + leading (e B) */e. ename, hiredate, B. comm

2 from emp e, bonus B
3 where e. ename = B. ename;
Execution Plan
----------------------------------------------------------
Plan hash value: 1842254584
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
----------------------------------------------------------------------------
| 0 | select statement | 1 | 34 | 6 (17) | 00:00:01 |
| * 1 | hash join | 1 | 34 | 6 (17) | 00:00:01 |
| 2 | table access full | EMP | 14 | 196 | 3 (0) | 00:00:01 |
| 3 | table access full | BONUS | 1 | 20 | 2 (0) | 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-access ("E". "ENAME" = "B". "ENAME ")
If emp is used as the driving table in the result execution plan!
1. If both ordered hint and leading prompt are used, the leading hint is invalid.
SCOTT @> select/* + leading (B e) ordered */e. ename, hiredate, B. comm

2 from emp e, bonus B
3 where e. ename = B. ename;
Execution Plan
----------------------------------------------------------
Plan hash value: 1842254584
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
----------------------------------------------------------------------------
| 0 | select statement | 1 | 34 | 6 (17) | 00:00:01 |
| * 1 | hash join | 1 | 34 | 6 (17) | 00:00:01 |
| 2 | table access full | EMP | 14 | 196 | 3 (0) | 00:00:01 |
| 3 | table access full | BONUS | 1 | 20 | 2 (0) | 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-access ("E". "ENAME" = "B". "ENAME ")
2. If two conflicting leading hints are used, oracle cbo ignores all leading prompts!
SCOTT @> select/* + leading (B e) leading (e B) */e. ename, hiredate, B. comm

2 from emp e, bonus B
3 where e. ename = B. ename;
Execution Plan
----------------------------------------------------------
Plan hash value: 1125985041
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
----------------------------------------------------------------------------
| 0 | select statement | 1 | 34 | 6 (17) | 00:00:01 |
| * 1 | hash join | 1 | 34 | 6 (17) | 00:00:01 |
| 2 | table access full | BONUS | 1 | 20 | 2 (0) | 00:00:01 |
| 3 | table access full | EMP | 14 | 196 | 3 (0) | 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-access ("E". "ENAME" = "B". "ENAME ")

,