在Oracle数据库中，两个表之间的表衔接要领有排序兼并衔接、嵌套轮回衔接、哈希衔接和笛卡尔衔接四种
1.排序兼并衔接（sort merge join）
排序兼并衔接是一种两表在做表衔接时用排序（SORT）操纵和兼并（MERGE）操纵来获得衔接结果集的表衔接要领
假如t1表和t2表在做表衔接时运用的是排序兼并衔接，那末Oracle会顺次实行以下步骤：
a.以目的SQL中指定的谓词前提接见t1表，然后对接见结果根据t1表的衔接列排序，排好序后的结果集记为s1
b.以目的SQL中指定的谓词前提接见t2表，然后对接见结果根据t2表的衔接列排序，排好序后的结果集记为s2
c.对s1和s2举行兼并操纵，从中掏出婚配纪录作为终究的结果集
排序兼并衔接的优缺点及实用场景：
a.通常状况下hash join的结果都比sort merge join要好，然则，假如行源已被排过序，在实行sort merge join时不须要再排序，这时候sort merge join的机能会优于hash join
b.通常状况下，只要在以下状况发作时，才会运用排序兼并衔接：
1)RBO形式
2)不等值衔接（>,<,>=,<=）
3)哈希衔接被禁用时（_HASH_JOIN_ENABLED=false）
示例
SQL> select * from scott.emp t1,scott.emp t2 where t1.empno > t2.mgr;
89 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3950110903
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 62 | 4712 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 62 | 4712 | 6 (17)| 00:00:01 |
| 2 | SORT JOIN | | 14 | 532 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 532 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(INTERNAL_FUNCTION("T1"."EMPNO")>INTERNAL_FUNCTION("T2"."MGR"))
filter(INTERNAL_FUNCTION("T1"."EMPNO")>INTERNAL_FUNCTION("T2"."MGR"))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
6612 bytes sent via SQL*Net to client
575 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
89 rows processed
SQL>
2.嵌套轮回衔接（nested loops join）
嵌套轮回衔接是一种两表在做表衔接时依托两层嵌套轮回（外层轮回/内层轮回）来获得衔接结果集的表衔接要领
假如t1表和t2表在做表衔接时运用的是嵌套轮回衔接，那末Oracle会顺次实行以下步骤：
a.起首，优化器会根据肯定的划定规矩来决议t1和t2谁是驱动表谁是被驱动表，驱动表用于外层轮回，被驱动表用于内存轮回。假定t1是驱动表
b.以目的SQL中指定的谓词前提接见驱动表t1，获得结果集s1
c.遍历s1，同时遍历被驱动表t2，即掏出s1中的纪录根据衔接前提和被驱动表t2做婚配。终究将获得的结果集返回
嵌套轮回衔接的优缺点及实用场景：
a.可以完成疾速响应，即可以第一时候先返回已衔接过且满足衔接前提的纪录，而没必要守候一切的衔接操纵悉数做完后才返回衔接结果
b.实用于驱动表所对应的驱动结果集的纪录数较少，同时在被驱动表的衔接列上又存在唯一性索引（或许在被驱动表的衔接列上存在选择性很好的非唯一性索引）的状况
示例
SQL> select /*+ gather_plan_statistics use_nl(t1,t2)*/* from scott.emp t1,scott.dept t2 where t1.deptno = t2.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,0,'allstats,last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dcsf9m1rzzga5, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_nl(t1,t2)*/* from scott.emp
t1,scott.dept t2 where t1.deptno = t2.deptno
Plan hash value: 4192419542
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 32 |
| 1 | NESTED LOOPS | | 1 | 14 | 14 |00:00:00.01 | 32 |
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 7 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 4 | 14 |00:00:00.01 | 25 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- filter("T1"."DEPTNO"="T2"."DEPTNO")
rows selected.
SQL>
3.哈希衔接（hash join）
哈希衔接是一种两表在做表衔接时依托哈希运算来获得衔接结果集的表衔接要领，oracle 7.3以后引入
Hash join的事情体式格局是将一个表（通常是小一点的谁人表）做hash运算并存储到hash列表中，从另一个表中抽取纪录，做hash运算，到hash 列表中找到响应的值，做婚配
哈希衔接只实用于CBO，也只能用于等值衔接前提
哈希衔接很适合于小表和大表做衔接，特别是在小表的衔接列的可选择性异常好的状况下，这时候候哈希衔接的实行时候就可以近似看做是和全表扫描谁人大表所消耗的时候相称
哈希衔接时，驱动结果集对应的Hash Table可以完整被包容在内存中（PGA的事情区），此时的哈希衔接的实行效力异常高
哈希衔接的机能题目可以经由过程10104事宜来诊断，相干申明以下：
Number of in-memory partitions (may have changed): Hash Partition
Final number of hash buckets: Hash Bucket数目
Total buckets: Empty buckets: Non-empty buckets: Hash Bucket中空纪录及非空纪录的状况
Total number of rows: 驱动结果集的纪录数
Maximum number of rows in a bucket: 包括纪录数最多的Hash Bucket所含纪录的数目
Disabled bitmap filtering: 是不是启用位图过滤
示例
SQL> select /*+ gather_plan_statistics use_hash(t1,t2)*/* from scott.emp t1,scott.dept t2 where t1.deptno = t2.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,0,'allstats,last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0j83q86ara5u2, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_hash(t1,t2)*/* from scott.emp
t1,scott.dept t2 where t1.deptno = t2.deptno
Plan hash value: 615168685
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 13 | | | |
|* 1 | HASH JOIN | | 1 | 14 | 14 |00:00:00.01 | 13 | 1321K| 1321K| 1070K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 7 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."DEPTNO"="T2"."DEPTNO")
21 rows selected.
SQL>
【相干引荐：SQL教程】
以上就是Oracle表衔接体式格局的优化要领（附示例）的细致内容，更多请关注ki4网别的相干文章！
