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 |--------------------------------------------------------------------------------------------欢送关注我的公众号，一起学习。