oracle外联结无法改变驱动表！

来源：互联网发布：c语言动态心形代码编辑：程序博客网时间：2024/05/17 18:00

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”指明这是一个全外联结！