Oracle 表连接查询

Oracle 表连接查询主要分为3类：
□ NESTED LOOP：以遍历的方式将驱动表（Inner 小表）与基础班（Outer 大表）进行关联运算，将最终的结果集返回给用户；
□ HASH JOIN：适用于等值连接查询，主要规则如下：
- 在内存中构建小表匹配键（Key）的 Hash Table；
- 系统对大表匹配键（Key）进行 Hash 算法；
- 对大表匹配键 Hash 值与小表 Hash Table 中的匹配键进行匹配；
- 返回连接查询结果集（Data Rows）。
□ SORT MERGE：适用于两表无序的连接，需要先对两表进行排序操作，然后进行连接查询。
在介绍表连接查询之前，需要对基础环境进行准备，如下：

SQL> desc dept;
Name Type Nullable Default Comments
------ ----------------- -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14 BYTE) Y
LOC VARCHAR2(13 BYTE) Y

COUNT(*)
----------
4

1、NESTED LOOPS

NESTED LOOPS 表连接查询，如下所示：

SQL> select /*+ use_nl(emp,dept) */ emp.sal, dept.loc from emp,dept where emp.deptno=dept.deptno;
......

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 658 | 10 | 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 658 | 10 | 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 84 | 3 | 00:00:01 |
| * 3 | TABLE ACCESS FULL | EMP | 4 | 104 | 2 | 00:00:01 |
----------------------------------------------------------------------

可以看到，当使用 use_nl(emp,dept) 提示（hint）后，强制 CBO 将小表（emp）作为驱动表与大表（dept）进行 NESTED LOOPS 连接。

2、HASH JOIN

HASH JOIN 表连接查询，如下所示：

SQL> select /*+ use_hash(emp,dept) */ emp.sal, dept.loc from emp,dept where emp.deptno=dept.deptno;
......

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 658 | 7 | 00:00:01 |
| * 1 | HASH JOIN | | 14 | 658 | 7 | 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 84 | 3 | 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 | 00:00:01 |
----------------------------------------------------------------------

可以看到，当使用 use_hash(emp,dept) 提示（hint）后，强制 CBO 将大表（dept）与小表（dept）进行 HASH JOIN 连接。

3、SORT MERGE

SORT MERGE 表连接查询，如下所示：

SQL> select /*+ use_merge(emp,dept) */ emp.sal, dept.loc from emp,dept where emp.deptno=dept.deptno;
......

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 658 | 8 | 00:00:01 |
| 1 | MERGE JOIN | | 14 | 658 | 8 | 00:00:01 |
| 2 | SORT JOIN | | 4 | 84 | 4 | 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 84 | 3 | 00:00:01 |
| * 4 | SORT JOIN | | 14 | 364 | 4 | 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 | 00:00:01 |
-----------------------------------------------------------------------

可以看到，当使用 use_merge(emp,dept) 提示（hint）后，强制 CBO 将大表（dept）与小表（dept）进行排序操作（SORT），然后再进行 MERGE 连接。