ORACLE数据库SQL优化---)Oracle表连接方法

在ORACLE数据库中，两个表之间的表连接方法有合并排序连接，嵌套循环连接，哈希连接和笛卡尔连接这四种，这四种表连接方法各有优缺点。下面分别来简单介绍下。

1，排序合并连接

排序合并连接 (Sort Merge Join)是一种两个表在做连接时用排序操作(Sort)和合并操作(Merge)来得到连接结果集的连接方法。

对于排序合并连接的优缺点及适用场景如下：

a,通常情况下，排序合并连接的执行效率远不如哈希连接，但前者的使用范围更广，因为哈希连接只能用于等值连接条件，而排序合并连接还能用于其他连接条件(如<,<=,>.>=)

b,通常情况下，排序合并连接并不适合OLTP类型的系统，其本质原因是对于因为OLTP类型系统而言，排序是非常昂贵的操作，当然，如果能避免排序操作就例外了。

2，嵌套循环连接

嵌套循环连接(Nested Loops Join)是一种两个表在做表连接时依靠两层嵌套循环（分别为外层循环和内存循环）来得到连接结果集的表连接方法。即外层循环对应的驱动结果集有多少条记录，遍历被驱动表的内层循环就要做多少次，这就是所谓的“嵌套循环”的含义。

对于嵌套循环连接的优缺点及适用场景如下：

a,如果驱动表所对应的驱动结果集的记录数较少，同时在被驱动表的连接列上又存在唯一性索引（或者在被驱动表的连接列上存在选择性好的非唯一性索引），那么此时使用嵌套循环连接的执行效率就会非常高；但如果驱动表所对应的驱动结果集的记录数很多，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也不会很高。

b,大表也可以作为嵌套循环连接的驱动表，关键是看目标SQL中指定的谓词条件(如果有的话)能否将驱动结果集的记录集数量大幅度的降下来。

c,嵌套循环连接有嵌套连接方法所没有的一个优点：嵌套循环连接可以实现快速响应。因为排序合并连接需要等到排序完后做合并操作时才能开始返回数据，而哈希连接则也等到驱动结果集所对应的HASH TABLE全部构建完后才能开始返回数据。

3，哈希连接

哈希连接（HASH JOIN）是一种两个表在做表连接时主要依靠哈希运算来得到连接结果集的表连接方法。

对于排序合并连接，如果两个表在施加了目标SQL中指定的谓词条件后得到的结果集很大而且需要排序，则排序合并连接的执行效率一定不高；而对于嵌套循环连接，如果驱动表所对应的驱动结果集的记录数很大，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也会同样不高。为了解决这个问题，于是ORACLE引进了哈希连接。在ORACLE 10g及其以后的版本中，优化器 （实际上是CBO，因为哈希连接仅适用于CBO）在解析目标SQL的时候是否考虑哈希连接受限于隐含参数_HASH_JOIN_ENABLED,默认值是TRUE.

对于哈希连接的优缺点及适用场景如下：

a,哈希连接不一定会排序，或者说大多数情况下都不需要排序

b,哈希连接的驱动表所对应的连接列的选择性尽可能好。

c,哈希只能用于CBO，而且只能用于等值连接的条件。（即使是哈希反连接，ORACLE实际上也是将其换成等值连接）。

c,哈希连接很适用小表和大表之间做连接且连接结果集的记录数较多的情形，特别是小表的选择性非常好的情况下，这个时候哈希连接的执行时间就可以近似看做和全表扫描个个大表的费用时间相当。

e,当两个哈希连接的时候，如果在施加了目标SQL中指定的谓词条件后得到的数据量较小的那个结果集所对应的HASH TABLE能够完全被容纳在内存中（PGA的工作区），此时的哈希连接的执行效率非常高。

4，反连接

反连接(Anti Join)是一种特殊的连接类型，与内连接和外连接不同，ORACLE数据库里并没有相关的关联词可以在SQL文本中反映。当做子查询展开时，ORACLE经常会吧那些外部WHERE 条件为NOT EXISTS,NOT IN 或者<> ALL的子查询转换成对应的反连接。

注意：NOT IN 和<>ALL 对NULL 值敏感，这意味着NOT IN 后面的子查询或者常量集合一旦有NULL值出现，则整个SQL的执行结果就会是NULL，即此时的执行结果将不包含任何记录。但是NOT EXISTS对NULL不敏感，这意味着NULL值对NOT EXISTS的执行结果不会有什么影响。

5，半连接

半连接(Semi Join)是一种特殊的连接类型，与反连接一样，ORACLE数据库里么有相关的关键字可以在SQL文本中专门表示半连接。当做子查询展开时，ORACLE经常会吧那些外部WHERE条件为EXISTS,IN或者=ANY的子查询转换成对应的半连接。

SQL> select * from t1 where col2 in (select col2 from t2);

COL1 COL2
---------- ----------
1 A
2 B

Execution Plan
----------------------------------------------------------
Plan hash value: 1713220790

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 81 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 3 | 81 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 60 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 3 | 21 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("COL2"="COL2")

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
504 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2 rows processed

6，星型连接

星型连接(Star Join)通常用于数据仓库类型的应用，它是一种单个事实表(FACT TABLE)和多个维度表(Dimension table)之间的连接。星型连接的各个维度表之间没有直接的关联条件，其事实表和各维度表之间是基于事实表的外键和对应维度表的主键列之间的连接，并且通常在事实表的外键上还会存在对应的位图索引。