优化必备基础:Oracle中常见的三种表连接方式

在Oracle SQL语句中，如果from后面有多个表时，表的连接方式是一个很重要的考量。

从Oracle 6开始，优化器就支持下面4种表连接方式：

— 嵌套循环连接（Nested Loop Join）

— 群集连接（Cluster Join）

— 排序合并连接（Sort-Merge Join）

— 笛卡尔连接（Cartesian Join）

在Oracle 7.3中，新增加了哈希连接（Hash Join）。

在Oracle 8中，新增加了索引连接（Index Join）。

在这些表连接的方法中，Nested Loop Join和Hash Join及Sort-Merge Join是比较常见的。

（1）Nested Loop Join

这种场景一般适用于大表和小表的关联，准确来说应该是大的行集与小的行集，一般小表适用为驱动表，对于小表中的匹配记录和大表做关联，此时小表是在外部循环，大表在内部循环，小表中的记录都和大表做一个关联。

SQL> create table t as select *from dba_objects where object_id is not null;

SQL> create table t1 as select *from user_objects where object_id is not null;

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>TRUE);

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>TRUE);

SQL> create unique index ind_t on t(object_id);

SQL> create unique index int_t1 on t1(object_id) ;

SQL> select count(*)from t;

COUNT(*)

----------

74552

下面的例子，表t1中的数据较少，表t中的数据多，就以表t1为驱动表，走了全索引扫描查取到t1的数据，然后对于t1中的数据和t做匹配，匹配时走了唯一性扫描。

.select t.object_id,t.object_name,t.object_type,t.status from t,t1 where t.object_id=t1.object_id;

（2）Hash Join

这种场景适用于大表和大表之间的关联。通过Hash算法来做两个表之间的匹配映射。

SQL> create table t as select *from dba_objects where object_id is not null;

SQL> create table t1 as select *from dba_objects where object_id is not null;

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>TRUE);

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>TRUE);

SQL> create unique index ind_t on t(object_id);

SQL> create unique index int_t1 on t1(object_id) ;

SQL> select count(*)from t;

COUNT(*)

----------

74552

对于表t1中的记录，都是通过Hash映射来匹配表t中的记录。对于CPU的资源消耗还是相对较多的，因为内部做了大量的计算。从生产环境中的实践来说，Hash Join还是不错的，特别是在和并行结合之后。

select t.object_id,t.object_name,t.object_type,t.status from t,t1 where t.object_id=t1.object_id;

（3）Sort-Merge Join

对于Sort-Merge Join来说，可能略微有些陌生。

在数据库中有一个隐含参数对应，默认是开启的，见下表。

Sort-Merge Join相关参数

因为这种连结方式使用不当会消耗大量的系统资源，在一些生产系统中都选择手动禁用这种连结。

这种连结的运行原理相比Nested Loop Join和Hash Join而言没有驱动表，所以Sort-Merge Join可能会产生大量的随机读。

比如我们有表emp、dept。

查询语句为：

select empno,ename,dname,loc from emp,dept where emp.deptno =dept.deptno

如果采用Sort-Merge Join，就会对emp、dept表进行order by 的操作。

类似下面两个操作：

select empno,ename ,deptno from emp order by deptno;

select deptno,dname,loc from dept order by deptno;

因为排序后的数据都是有序的，然后对两个子结果集根据deptno进行匹配。

选择两端的数据列，根据列的要求筛选数据。

我们先来看一个使用Sort-Merge Join的执行计划，实际中需要用到Sort-Merge Join的场景就是在类似下面形式的查询中

where tab1.column1 between tab2.column2 and tab2.column3

我们可以使用Hint ordered来指定连接方式驱动，或者使用hint use_merge来引导查询走Sort-Merge Join，下面简单模拟一下。

（1）使用Hint ordered，语句如下。

SQL> select /*+ordered*/ empno,ename,dname,loc from emp,dept where emp.deptno between dept.deptno-10 and dept.deptno+10;

语句的执行计划如下：

Predicate Information (identified by operation id):

---------------------------------------------------

4 - filter("EMP"."DEPTNO"<="DEPT"."DEPTNO"+10)

5 - access(INTERNAL_FUNCTION("EMP"."DEPTNO")>="DEPT"."DEPTNO"-10)

filter(INTERNAL_FUNCTION("EMP"."DEPTNO")>="DEPT"."DEPTNO"-10)

可以看到对emp和dept都做了全表扫描，对数据进行了排序，然后根据deptno对结果集进行了匹配和关联，最后把结果集输出。

（2）使用Hint use_merge来实现相同的效果。

SQL> select /*+use_merge(dept,emp)*/ empno,ename,dname,loc from emp,dept where emp.deptno between dept.deptno-10 and dept.deptno+10;

语句的执行计划如下：

Predicate Information (identified by operation id):

---------------------------------------------------

4 - filter("EMP"."DEPTNO"<="DEPT"."DEPTNO"+10)

5 - access("EMP"."DEPTNO">="DEPT"."DEPTNO"-10)

filter("EMP"."DEPTNO">="DEPT"."DEPTNO"-10)

合并排序的思路和数据结构中的合并排序算法相似，适合在数据筛选条件有限或者返回结果已经排序的场景中使用。如果本身表中的数据量很大，做Sort-Merge Join就会耗费大量的CPU资源，临时表空间相比来说不是很划算，完全可以通过其他的连接来实现。