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 

SQL> desc emp;
Name     Type              Nullable Default Comments
-------- ----------------- -------- ------- -------- 
EMPNO    NUMBER(4)
ENAME    VARCHAR2(10 BYTE) Y
JOB      VARCHAR2(9 BYTE)  Y
MGR      NUMBER(4)         Y
HIREDATE DATE              Y
SAL      NUMBER(7,2)       Y
COMM     NUMBER(7,2)       Y
DEPTNO   NUMBER(2)         Y

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> select count(*) from dept;

  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;
......

14 rows selected

 Plan Hash Value  : 4192419542 

----------------------------------------------------------------------
| 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;
......

14 rows selected

 Plan Hash Value  : 615168685 

----------------------------------------------------------------------
| 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;
......

14 rows selected

 Plan Hash Value  : 1407029907 

-----------------------------------------------------------------------
| 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 |
-----------------------------------------------------------------------