Oracle 表连贯办法有四种：
- 排序合并连贯（Sort Merge Join）
- 嵌套循环连贯（Nested Loops Join）
- 哈希连贯（Hash Join）
- 笛卡尔积（Cartesian Product）
排序合并连贯（Sort Merge Join）
排序合并连贯是将连贯的两个表应用连贯列排序后，对排序后的后果集进行合并后再失去匹配记录。如果连贯列下面有索引，能够防止排序，那么优化器就有可能会抉择排序合并连贯。能够用于 =，>，>=，<，<= 连贯条件，不适用于 <>，like 连贯条件。对应执行打算为 SORT JOIN 和MERGE JOIN。
SQL> select e.employee_id,e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id;
106 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1343509718
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 3286 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 106 | 3286 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 107 | 1605 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
-- 如果没有索引，则会抉择哈希连贯：SQL> create table emp as select * from employees;
Table created.
SQL> create table dept as select * from departments;
Table created.
SQL> select e.employee_id,d.department_name from emp e,dept d where e.department_id=d.department_id;
106 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 5936 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 106 | 5936 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 27 | 810 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 107 | 2782 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
-- 如果应用不等连贯条件，则会抉择嵌套循环连贯：SQL> select e.employee_id,d.department_name from employees e,departments d where e.department_id!=d.department_id;
2756 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2968905875
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2757 | 63411 | 41 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2757 | 63411 | 41 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMPLOYEES | 102 | 714 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------嵌套循环连贯（Nested Loops Join）
嵌套循环连贯是 Oracle 将连贯的两个表依据后果集的大小，决定出驱动表和被驱动表。后果集小的作为驱动表，后果集大的作为被驱动表。对于驱动表的每一行，都要与被驱动表的所有行应用连贯条件进行匹配。实用于驱动表后果集很小，被驱动表在连贯列上有高效索引的表连贯。能够用于所有连贯条件。对应的执行打算为NESTED LOOPS。
SQL> select e.employee_id,e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id and d.department_id=80;
34 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1492013603
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 1054 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 34 | 1054 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 34 | 510 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
-- 这里 DEPARTMENTS 表加了条件过滤后后果集只有 1 行，所以被选为驱动表。SQL> select e.first_name,e.last_name,e.salary,d.department_name from employees e,departments d where d.department_name IN ('Marketing', 'Sales') and e.department_id = d.department_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1021246405
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 722 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 19 | 722 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 20 | 722 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------这里有 2 次 NESTED LOOPS，是因为 Oracle 11g 引入了引入了向量 I /O（Vector I/O），批量解决多个物理 I / O 申请来进步嵌套循环连贯的效率。
如果驱动表的限度条件的字段上有索引，被驱动表的连贯条件的字段上有索引，则应用嵌套循环连贯的效率就会很高。
哈希连贯（Hash Join）
如果两个表对应的后果集很大，应用排序合并的话，排序操作老本较高；应用嵌套循环的话，则循环次数又很多，须要屡次拜访被驱动表的后果集；为了进步这种状况下表连贯的效率，优化器提供了新的表连贯办法，即哈希连贯。哈希连贯是应用哈希运算来失去后果的表连贯办法。只实用于等值连贯条件。
SQL> create table emp as select * from employees;
Table created.
SQL> create table dept as select * from departments;
Table created.
SQL> select e.employee_id,d.department_name from emp e,dept d where e.department_id=d.department_id;
106 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 5936 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 106 | 5936 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 27 | 810 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 107 | 2782 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------笛卡尔积（Cartesian Product）
如果两个表做表连贯而没有连贯条件，就会产生笛卡尔积，在理论工作中应该尽可能防止笛卡尔积。笛卡尔积对应的执行打算中有关键字MERGE JOIN CARTESIAN。
SQL> select last_name,department_name from emp,dept;
2889 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2034389985
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2889 | 57780 | 41 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 2889 | 57780 | 41 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 27 | 324 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 107 | 856 | 38 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 107 | 856 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------其余
外连贯
后面连贯办法的示例都是内连贯，咱们晓得 Oracle 还有外连贯，包含左外连贯，右外连贯和全外连贯。执行打算中左外连贯和右外连贯对应的关键字是OUTER，全外连贯对应的关键字是FULL OUTER。
-- 内连贯：SQL> select e.employee_id,e.last_name,d.department_name from employees e join departments d on e.department_id=d.department_id;
106 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1343509718
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 3286 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 106 | 3286 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 107 | 1605 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
-- 左外连贯：SQL> select e.employee_id,e.last_name,d.department_name from employees e left join departments d on e.department_id=d.department_id;
107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2296652067
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 3317 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 107 | 3317 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
-- 右外连贯：SQL> select e.employee_id,e.last_name,d.department_name from employees e right join departments d on e.department_id=d.department_id;
122 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 514479674
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 3286 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 106 | 3286 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 107 | 1605 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
-- 全外连贯：SQL> select e.employee_id,e.last_name,d.department_name from employees e full join departments d on e.department_id=d.department_id;
123 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2631508678
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 122 | 5368 | 6 (0)| 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | 122 | 5368 | 6 (0)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER| | 122 | 3782 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------半连贯
如果 where 条件有 exists、in 或 =any 操作符 + 子查问，则 Oracle 会将其解决为半连贯，执行打算中对应的关键字为SEMI。
SQL> select department_id,department_name from departments d where exists (select 1 from employees e where d.department_id = e.department_id and e.salary > 2500);
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2188966913
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 230 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 10 | 230 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> select department_id,department_name from departments d where department_id in (select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2188966913
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 230 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 10 | 230 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> select department_id,department_name from departments d where department_id=any(select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2188966913
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 230 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 10 | 230 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------反连贯
如果 where 条件有 not exists、not in 或 <>all 操作符 + 子查问，则 Oracle 会将其解决为反连贯，执行打算中对应的关键字为ANTI。
SQL> select department_id,department_name from departments d where not exists (select 1 from employees e where d.department_id = e.department_id and e.salary > 2500);
16 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3822487693
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 391 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN ANTI | | 17 | 391 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> select department_id,department_name from departments d where department_id not in (select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);
16 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3822487693
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 391 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN ANTI | | 17 | 391 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> select department_id,department_name from departments d where department_id<>all(select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);
16 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3822487693
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 391 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN ANTI | | 17 | 391 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------欢送关注我的公众号，一起学习。