SQL> select /*+ leading(e) */ e.*,d.* from emp e,dept d where e.deptno=d.deptno;
执行计划
----------------------------------------------------------
Plan hash value: 1123238657
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 812 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
SQL> select /*+ leading(d) */ e.*,d.* from emp e,dept d where e.deptno=d.deptno;
执行计划
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
上面是一个内联结的执行计划,可以看见可以使用/*+ leading(e) */和/*+ leading(d) */来执行最先访问的表。
SQL> select /*+ leading(e) */ e.*,d.* from emp e,dept d where e.deptno=d.deptno(+);
执行计划
----------------------------------------------------------
Plan hash value: 3387915970
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 812 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO"(+))
SQL> select /*+ leading(d) */ e.*,d.* from emp e,dept d where e.deptno=d.deptno(+);
执行计划
----------------------------------------------------------
Plan hash value: 3387915970
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 812 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO"(+))
上面是一个外联结,其中第一行的“OUTER”表明这个是一个外联结,可以看见使用/*+ leading(e) */和/*+ leading(d) */都不能改变执行计划的驱动表(emp)!
SQL> select e.*,d.* from dept d,emp e where e.deptno=d.deptno(+);
执行计划
----------------------------------------------------------
Plan hash value: 3387915970
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 812 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO"(+))
可以看见改变from后面的表顺序,驱动表还是emp!
SQL> select e.*,d.* from emp e,dept d where e.deptno(+)=d.deptno;
执行计划
----------------------------------------------------------
Plan hash value: 2251696546
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"(+)="D"."DEPTNO")
filter("E"."DEPTNO"(+)="D"."DEPTNO")
把左外联结改为右外联结,驱动表就改变了(dept)。规律就是:左外联结,驱动表就是左边表。右外联结,驱动表就是右边的表。
SQL> select e.*,d.* from emp e full join dept d on e.deptno=d.deptno;
执行计划
----------------------------------------------------------
Plan hash value: 51889263
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 1755 | 7 (15)| 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | 15 | 1755 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER| | 15 | 870 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."DEPTNO"="D"."DEPTNO")
可以看见第二行的“FULL OUTER”指明这是一个全外联结!