表的联结方法

1.联结方法

如果再你查询中有多张表，在优化器确定了每个表最恰当的访问方法之后，下一步就是确定将这些表联结起来的最佳方法以及最恰当的顺序。任何时候当在FROM子句中有多个表时，就需要进行联结。表之间的关系通过where子句中的一个条件来定义。如果没有指定任何条件，联结就会隐含地定义为一个表中每一行将与另一个表的所有行匹配，笛卡联结。

联结发生在一对表或者数据行源之间。当FORM子句存在多张表时，优化器将会决定哪种联结运算对于每一对表来说效率最高。联结的方法有：嵌套循环联结，散列联结，排序-合并联结和笛卡儿联结。

1.1  嵌套循环联结

嵌套循环联结使用一次访问运算所得到的结果集中的每一行与另一个表进行对碰，如果结果集大小是有限的并且在用来联结的列上建有索引的话，这种联结的效率通常是最高的，嵌套循环联结的运算成本主要是读取外层表中每一行并将其与所匹配的内存表中的行联结所需的成本。

嵌套循环联结就是一个循环嵌在另一个循环当中。外层循环基本上来说就是一个只使用where子句中属于驱动表的条件对它进行查询。当数据行进过外层条件筛选并确认匹配条件后，这些行为就会逐个进入到内存循环中，然后再基于联结列进行逐行查询看是否与被联结的表中的某一行匹配。如果这个一行与第二行相匹配，就会将被传递到查询计划的下一步或者如果没有更多步骤的话直接被包含在最终结果集中。

这种类型的联结的强大之处在于所使用的内存非常少的。因为数据行集一次加工一行，所需要的开支也是非常小的。由于这个原因，除了使用一次加工一行这种方式来建立一个很大的数据集需要较长时间这一点以外，他也是适合进行大数据集加工的。这就是为什么前面提到嵌套循环联结在结果集较小的时候是最好的。嵌套联结的基本度量就是为了准备最终结果集所需要访问的数据块数目。

1.2 排序--合并联结

排序-合并联结独立地读取需要联结的两张表，对每张表中的数据行（仅是那些满足where子句中条件的数据行）按照联结键进行排序，然后对排序后的数据行集进行合并。对这种联结方法来说排序的开销是非常大的。对于不能够放入内存中的大数据来说，可能会使用临时磁盘空间来排序。这是非常消耗内存和是时间资源的。但是一旦数据行排序完成了，合并过程非常快的。为了进行合并，数据库轮流操作两个列表。比较最上面的数据行，丢弃在排序列中比另一列表的最上面一行出现得早的数据行，并只返回匹配的行。

SQL> select empno,ename,dname,loc from emp a,dept b where a.deptno=b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |    14 |   462 |     6 (17)| 00:00:01 |
|   1 |  MERGE JOIN     |       |    14 |   462 |     6 (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2 (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN     | PK_DEPT |     4 |       |     1 (0)| 00:00:01 |
|*  4 |   SORT JOIN     |       |    14 |   182 |     4 (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL     | EMP     |    14 |   182 |     3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"."DEPTNO"="B"."DEPTNO")
       filter("A"."DEPTNO"="B"."DEPTNO")

首先要关注是对dept表所使用的索引扫描，这个例子中，因为索引将按顺序后顺序返回数据，优化器选择使用索引来读取表数据，这就意味着可以避免一次单独的排序运算。对于emp表必须进行全表扫描然后单独排序，因为的盘太浓这一列上没有索引可以用。在两个数据行集都准备好并排序后，他们讲被合并到一起。

排序--合并联结将会访问所需的数据块然后再内存中（或者如果没有足够内存的话使用临时磁盘空间）进行排序和合并。因此，当你对排序-合并和嵌套循环联结进行逻辑读取比较的时候，尤其是对于一个更大的数据行源的查询。你可能发现嵌套循环联结所需访问的块更多。此时，排序-合并也并非更好的选择，你必须把所有需要进行排序已经合并等步骤的工作都考虑进去，并意识到这些工作最终可能比做较多的块访问花费的时候跟多。

排序-合并联结一般最时候于数据筛选条件有限并返回有限数据行的查询，如果没有可用的更直接访问数据行的索引时排序-合并联结通常是最好的选择。总的来说，在条件为非等式的时候排序-合并联结通常是最好的选择，例如 where table.column1 between table2。column1 and table2.columns这样联结条件就比较适合排序-合并联结。

1.3 散列联结

散列联结，与排序-合并联结类似，首先应用where子句中的筛选标准来独立地读取要进行联结的两个表。基于表和索引的统计信息，被确定为返回最少行数的表完全散列化到内存中。这个散列表包含原表的所有数据行并被基于讲联结键转化为散列值的随机函数载人到散列桶中。只有有足够的内存空间，这个散列表将一直放在内存中。然而，如果没有足够的内存，散列表被写入到临时磁盘空间。

下一步就是读取另一张较大的表并对联结键列应用散列函数。然后利用得到的散列值对较小在内存中的散列表进行探测以寻找匹配的第一个表的行数据所在散列桶，每个散列桶都有一个放在其中的数据行列表（通过一个位图来表示）。这个列表被用来与探测进行匹配，如果匹配成功，则返回这一行数据，否则丢弃。较大的表只读取一次，并检查其中的每一行来寻找匹配。这个与嵌套循环联结的不同之处在于此处内存表被多次读取。因此事实上在这个例子中，较大的表是驱动表，因此仅读取一次。而较小的散列表则被探测很多次，与嵌套回路联结计划不同，在执行计划的输出中较小的散列表放在前面而较大的探测表放在后面。

在散列联结的执行计划中，较小的散列表列在前面而探测表列在后面。记住这一点就是决定哪个表是最小的不仅取决于数据行还取决于这些行的大小，因为整个行都必须要存放在散列表中。

当数据行源较大并且结果集也较大的情况下将更倾向于考虑散列联结。或者，如果要散结的其中一张表确定总是返回同一数据行源，也可能会选用散列联结因为这样仅访问一次这张表，如果在这种情况下选用嵌套循环联结，这个数据行源就会被一遍又一遍地访问，需要比单独访问一次多做很多工作。最后，如果较小的表可以放到内存中，散列联结也是适用的。

散列联结中访问数据块的方式类似于排序-合并联结中访问方式。建立散列表所需的数据块将被读取，然后剩下的工作将会针对存放的内存中（如果没有足够内存的话将会是临时磁盘空间）的散列数据进行。因此，当你对散列联结和排序-合并联结的逻辑读取进行比较的时候，访问的数据块几乎是相等的。但相比于嵌套循环联结来说逻辑读取比较少。因为数据块仅被读取了一次，然后或者存放在内存中（对于散列表）再进行访问或只读取一次（探测表）

散列联结只有在相等联结的情况下才能进行，如前面所提到的，排序-合并联结可以用来处理特定的非等试的条件。散列联结只有在相等联结时才能选用的原因在于匹配是针对散列值来进行的，而在一个范围内来考虑散列值是没有意义的。

散列值并不必然等于散列键值：

SQL> select distinct deptno,ora_hash(deptno,1000) hv from emp order by deptno;

    DEPTNO   HV
---------- ----------
10  547
20  486
30  613

SQL> select deptno from (select distinct deptno,ora_hash(deptno,1000) hv from emp order by deptno) where hv between 100 and 500;
    DEPTNO
----------
20

SQL> select distinct deptno,ora_hash(deptno,1000,50) hv from emp order by deptno;

    DEPTNO   HV
---------- ----------
10  839
20  850
30  290

SQL> select deptno from (select distinct deptno,ora_hash(deptno,1000,50) hv from emp order by deptno) where hv between 100 and 500;
    DEPTNO
----------
30

我利用ora_hash函数来说明散列值是如何生成的。ora_hash函数有3个参数：一个任何基本类型的输入，最大散列桶值（最小值为0）以及一个种子值（默认值也是0）。

上述例子为了理解散列联结不适用非等式联结。

1.4  笛卡儿联结

笛卡儿联结发生在当一张表中的所有行与另一张表中所有行联结的时候。因此，这种联结所得到的结果集的总行数等于一张表（A)中的数据行数乘以另一张表（B)中的数据行数，也就是A*B=结果集中总数的数据行数。通常当联结条件被忽略或忽视以致没有指定的联结列，所能做的唯一可能的运算就是将一张表的所有内容与另一张表的所有内容联结起来的时候会进行笛卡儿联结。

SQL> select empno,ename,dname,loc from emp,dept;
Execution Plan
----------------------------------------------------------
Plan hash value: 2034389985
-----------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |    | 56 |  1568 | 10   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|    | 56 |  1568 | 10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | DEPT |  4 | 72 |  3   (0)| 00:00:01 |
|   3 |   BUFFER SORT     |    | 14 | 140 |  7   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | EMP  | 14 | 140 |  2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

关于笛卡儿联结执行计划需要注意的一点就是BUFFER SORT运算的出现，这并不是一次真正的排序。而是由于oracle将每一行与每一行进行联结，使用缓冲区排序机制将第二张的数据块从缓冲区缓存复制出并放入到私有内存中，避免缓冲区缓存中的同一个数据块被一次又一次的访问。这些重复访问需要大量的逻辑读取，并且也会增加更多对缓冲区缓存中的这些数据块进行争夺的几率。因此，将这些数据块缓存到一个私有内存区域可能是一种更有效的实现重复联结的方式。

1.5 外联结

外联结返回一张表的所有行以及另一张联结表中满足联结条件的行数据。oracle使用+字符来表明进行外联结，+号放在一对圆括号中，位于只有匹配才会返回数据行的表联结条件旁。这意味着有可能选用更加优化的联结执行顺序。因此，使用外联结的时候要格外小心，因为它的选用有可能会影响到整个执行计划的性能。

SELECT c.cust_last_name, nvl(SUM(o.order_total), 0) tot_orders
  FROM customers c, orders o
 WHERE c.customer_id = o.customer_id
 GROUP BY c.cust_last_name
HAVING nvl(SUM(o.order_total), 0) BETWEEN 0 AND 5000
  6   ORDER BY c.cust_last_name;
CUST_LAST_NAME     TOT_ORDERS
-------------------- ----------
Alexander    309
Chandar    510
George    220
Hershey     48
Higgins    416
Kazan   1233
Sen   4797
Stern  969.2
Weaver    600
9 rows selected.

SELECT COUNT(*) ct
  FROM (SELECT c.cust_last_name, nvl(SUM(o.order_total), 0) tot_orders
          FROM customers c, orders o
         WHERE c.customer_id = o.customer_id
         GROUP BY c.cust_last_name
        HAVING nvl(SUM(o.order_total), 0) BETWEEN 0 AND 5000
  7           ORDER BY c.cust_last_name);

CT
----------
9

SELECT COUNT(*) ct
  FROM (SELECT c.cust_last_name, nvl(SUM(o.order_total), 0) tot_orders
          FROM customers c, orders o
         WHERE c.customer_id = o.customer_id(+)
         GROUP BY c.cust_last_name
        HAVING nvl(SUM(o.order_total), 0) BETWEEN 0 AND 5000
  7           ORDER BY c.cust_last_name);

CT
----------
       140

Execution Plan
----------------------------------------------------------
Plan hash value: 2278331185

-----------------------------------------------------------------------------------------------------
| Id  | Operation | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |    |  1 |    |  5  (20)| 00:00:01 |
|   1 |  SORT AGGREGATE |    |  1 |    | |    |
|   2 |   VIEW |    |  1 |    |  5  (20)| 00:00:01 |
|*  3 |    FILTER |    |    |    | |    |
|   4 |     HASH GROUP BY |    |  1 | 22 |  5  (20)| 00:00:01 |
|*  5 |      HASH JOIN OUTER |    | 319 |  7018 |  4   (0)| 00:00:01 |
|   6 |       VIEW | index$_join$_002 | 319 |  3828 |  2   (0)| 00:00:01 |
|*  7 |        HASH JOIN |    |    |    | |    |
|   8 | INDEX FAST FULL SCAN | CUSTOMERS_PK     | 319 |  3828 |  1   (0)| 00:00:01 |
|   9 | INDEX FAST FULL SCAN | CUST_LNAME_IX    | 319 |  3828 |  1   (0)| 00:00:01 |
|  10 |       TABLE ACCESS BY INDEX ROWID| ORDERS    | 105 |  1050 |  2   (0)| 00:00:01 |
|* 11 |        INDEX RANGE SCAN | ORD_CUSTOMER_IX  | 105 |    |  1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

从上面的例子可以看到最初没有使用外联结的结果不是十分正确，因为还没有下单的顾客在订单order表中是没有记录的，他们将不会被包含在最终的查询结果集中。将查询修改为外联结将会把这些顾客包含进来，同时还要注意执行计划的第5步指定了HASH JOIN OUTER。外联结可以与任何联结方法（嵌套循环，散列，排序--合并）一起使用。并通过扎起常规运算名称后面加上outer命令来表示。

通过ANSI联结语法的外联结：

SELECT COUNT(*) ct
  FROM (SELECT c.cust_last_name, nvl(SUM(o.order_total), 0) tot_orders
          FROM customers c
          LEFT OUTER JOIN orders o
            ON (c.customer_id = o.customer_id)
         GROUP BY c.cust_last_name
        HAVING nvl(SUM(o.order_total), 0) BETWEEN 0 AND 5000
         ORDER BY c.cust_last_name);

在ANSI语法中，你只需要使用关键字left outer join。这表明了左侧的表（也就是说所列出的第一表）是你需要的即使没有满足联结条件的数据行也要将所有的行包含在结果集中的表。如果你想使用即使customers表中没有对应匹配，也要返回orders表中的所有数据行，你可以使用right outer join关键字

当使用oracle的（+）运算时，会有一些在使用ANSI语法时没有的局限性，如果你尝试将通一个表与另外多张表进行外联结oracle将会抛出一个错误，ORA-014117:一张表最多只能与另外一张表进行外联结。而使用ANSI语法对表的数目没有限制。即使是一张表可以外联结。

oracle外联结语法的另一局限性在于它不支持全外联结。

SELECT e1.empno, e1.deptno, e1.job, e2.ename, e2.deptno, e2.job
  FROM e1
  FULL OUTER JOIN e2
  4      ON (e1.empno = e2.empno);

     EMPNO     DEPTNO JOB ENAME       DEPTNO JOB
---------- ---------- --------- ---------- ---------- ---------
      7369   20 CLERK SMITH   20 CLERK
ALLEN   30 SALESMAN
WARD   30 SALESMAN
      7566   20 MANAGER JONES   20 MANAGER
MARTIN   30 SALESMAN
BLAKE   30 MANAGER
      7788   20 ANALYST SCOTT   20 ANALYST
TURNER   30 SALESMAN
      7876   20 CLERK ADAMS   20 CLERK
JAMES   30 CLERK
      7902   20 ANALYST FORD   20 ANALYST

     EMPNO     DEPTNO JOB ENAME       DEPTNO JOB
---------- ---------- --------- ---------- ---------- ---------
      7839   10 PRESIDENT
      7782   10 MANAGER
      7934   10 CLERK

14 rows selected.

----------------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | |    11 |   638 |     6   (0)| 00:00:01 |
|   1 |  VIEW      | VW_FOJ_0 |    11 |   638 |     6   (0)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER| |    11 |   396 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | E1 |     8 |   120 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | E2 |    11 |   231 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

使用oracle的等价写法：

SELECT e1.empno, e1.deptno, e1.job, e2.ename, e2.deptno, e2.job
  FROM e1, e2
 WHERE e1.empno(+) = e2.empno
UNION ALL
SELECT e1.empno, e1.deptno, e1.job, e2.ename, e2.deptno, e2.job
  FROM e1, e2
  7   WHERE e1.empno = e2.empno(+)
  8  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3338363451
----------------------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Bytes | Cost (%CPU)| Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |   | 19 |   684 | 12   (0)| 00:00:01 |
|   1 |  UNION-ALL    |   |   |   | |   |
|*  2 |   HASH JOIN OUTER   |   | 11 |   396 | 6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| E2   | 11 |   231 | 3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| E1   | 8 |   120 | 3   (0)| 00:00:01 |
|*  5 |   HASH JOIN OUTER   |   | 8 |   288 | 6   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL| E1   | 8 |   120 | 3   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL| E2   | 11 |   231 | 3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E1"."EMPNO"(+)="E2"."EMPNO")
   5 - access("E1"."EMPNO"="E2"."EMPNO"(+))

全外联结就其执行所需要的资源来说成本会很高。使用的时候需要小心地理解编写这样的查询所带来的影响并注意其对性能的影响。

小结：在确定SQL语句的执行计划的时候，优化器 必须做出几个关键的选择。首先，要确定查询胡总所用到的每个表最适合的访问方法。基本上有两种选择：索引扫描和全表扫描。每种访问方法用来访问包含SQL语句所需数据的实现方式是不同的。一旦优化器选定了访问方法，就必须选定联结方法。表将会被逐对联结，前一次联结的结果的数据行被用来与下一个表联结。直到所有表都被联结并获得最终的结果集。