Oracle getting started Tutorial: leading vs ordered hint

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@www.bkjia.com> 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@www.bkjia.com> 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@www.bkjia.com> 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@www.bkjia.com> 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 ")

1 2 Next Page