Oracle Table连接方式分析

Oracle Table连接方式分析

表连接基本知识：

1、哪张表将驱动查询（即访问的第一张表）？按照指定的路径查询，何时将访问到没一张表？可选的驱动路径有哪些？

2、可能出现哪些oracle连接？记住：在Oracle中，连接顺序、可选的索引、用于排序和建立散列表的可用内存的不同都会导致不同的结果。

3、哪些索引是可用的?哪些索引是可选的？索引的选择不仅仅将导致优化器使用或者限制一个索引，还将改变驱动查询的方式，并可能决定使用或者限制查询中其他的索引。

4、哪些提示提供了可选的路径？哪些提示限制或强制使用索引？这些提示不仅仅改变了表的驱动顺序，还改变了Oracle中连接的性能，并可决定限制或强制使用哪些索引。

5、您在使用哪一个版本的Oracle？你的选择取决于你使用的Oracle的版本。不同的版本优化器的工作方式也不一样。

表连接方式：

在查看sql执行计划时，我们会发现表的连接方式有多种，本文对表的连接方式进行介绍以便更好看懂执行计划和理解sql执行原理。 

一、连接方式：

       嵌套循环（Nested  Loops （NL））

      （散列）哈希连接（Hash Join （HJ））

       排序合并连接(Sort Merge Join (SMJ) )

二、连接说明：

    1.Oracle一次只能连接两个表。不管查询中有多少个表，Oracle 在连接中一次仅能操作两张表。

    2.当执行多个表的连接时，优化器从一个表开始，将它与另一个表连接；然后将中间结果与下一个表连接，以此类推，直到处理完所有表为止。

ORACLE从6的版本开始，优化器使用4种不同的表的连接方式：

嵌套循环连接（NESTEDLOOPJOIN）

群集连接（CLUSTERJOIN）

排序合并连接（SORTMERGEJOIN）

笛卡尔连接（CARTESIANJOIN）

哈希连接（HASHJOIN）

索引连接（INDEXJOIN）

　　这六种连接方式都有其独特的技术特点，在一定的条件下，可以充分发挥高效的性能。

　　但是也都有其局限性，如果使用不当，不仅不能提高效率，反而会严重影响系统的性能。因此，深入地探讨连接方式的内部运行机制对于性能优化是必要的。

　　1、嵌套循环连接

　　嵌套循环连接的内部处理的流程：

　　1）Oracle优化器根据基于规则RBO（rulebasedoptimizer）或基于成本CBO（costbasedoptimizer）的原则，选择两个表中的一个作为驱动表，并指定其为外部表。

　　2）Oracle优化器再将另外一个表指定为内部表。

　　3）Oracle从外部表中读取第一行,然后和内部表中的数据逐一进行对比，所有匹配的记录放在结果集中。

　　4）Oracle读取外部表中的第二行，再和内部表中的数据逐一进行对比，所有匹配的记录添加到结果集中。

　　5）重复上述步骤，直到外部表中的所有纪录全部处理完。

　　6）最后产生满足要求的结果集。

    使用嵌套循环连接是一种从结果集中提取第一批记录最快速的方法。在驱动行源表（就是正在查找的记录）较小、或者内部行源表已连接的列有惟一的索引或高度可选的非惟一索引时，嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势，它可以快速地从结果集中提取第一批记录，而不用等待整个结果集完全确定下来。这样，在理想情况下，终端用户就可以通过查询屏幕查看第一批记录，而在同时读取其他记录。不管如何定义连接的条件或者模式，任何两行记录源可以使用嵌套循环连接，所以嵌套循环连接是非常灵活的。

　　然而，如果内部行源表（读取的第二张表）已连接的列上不包含索引，或者索引不是高度可选时，嵌套循环连接效率是很低的。如果驱动表的记录非常庞大时，其他的连接方法可能更加有效。

　　可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生嵌套循环连接的执行计划。

　　selecta.user_name,b.dev_nofromuser_infoa,dev_infobwherea.user_id=b.user_id;

案例：

12:00:44 SCOTT@ prod> select  e.ename,e.sal,d.deptno,d.dname 
12:01:50   2    from emp e,dept d
12:01:50   3    where d.deptno=e.deptno and d.deptno=10 ;
ENAME             SAL     DEPTNO DNAME
---------- ---------- ---------- --------------
CLARK            2450         10 ACCOUNTING
KING             5000         10 ACCOUNTING
MILLER           1300         10 ACCOUNTING
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 568005898
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     5 |   105 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     5 |   105 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | EMP     |     5 |    50 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate InfORMation (identified by operation id):
---------------------------------------------------
   3 - access("D"."DEPTNO"=10)
   4 - filter("E"."DEPTNO"=10)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        835  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
12:01:51 SCOTT@ prod>
11:59:48 SCOTT@ prod>select  e.ename,e.sal,d.deptno,d.dname 
11:59:49   2    from dept d,emp e
11:59:49   3    where d.deptno=e.deptno ;
14 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   294 |    10   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    14 |   294 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    44 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     4 |    40 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         37  consistent gets
          0  physical reads
          0  redo size
       1038  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

排序合并连接（SORTMERGEJOIN）

　　排序合并连接内部处理的流程：

　　1）优化器判断第一个源表是否已经排序，如果已经排序，则到第3步，否则到第2步。

　　2）第一个源表排序

　　3）优化器判断第二个源表是否已经排序，如果已经排序，则到第5步，否则到第4步。

　　4）第二个源表排序

　　5）已经排过序的两个源表进行合并操作，并生成最终的结果集。

　　在缺乏数据的选择性或者可用的索引时，或者两个源表都过于庞大（所选的数据超过表记录数的5%）时，排序合并连接将比嵌套循环连更加高效。

　　排列合并连接需要比较大的临时内存块，以用于排序，这将导致在临时表空间占用更多的内存和磁盘I/O。

可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生排序合并连接的执行计划。

　　selecta.user_name,b.dev_nofromuser_infoa,dev_infobwherea.user_id>b.user_id;

案例：

11:57:04 SCOTT@ prod> select  e.ename,e.sal,d.deptno,d.dname 
  from dept d,emp e
11:57:13   2  11:57:13   3    where d.deptno=e.deptno ;
14 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   294 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   294 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    44 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   140 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   140 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1042  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed
11:57:14 SCOTT@ prod>select  e.ename,e.sal,d.deptno,d.dname 
11:58:14   2    from dept d,emp e
11:58:14   3    where d.deptno=e.deptno ;
14 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   294 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   294 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    44 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   140 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   140 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1042  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed
11:58:23 SCOTT@ prod>
11:59:48 SCOTT@ prod>

哈希连接

　　当内存能够提供足够的空间时，哈希（HASH）连接是Oracle优化器通常的选择。哈希连接中，优化器根据统计信息，首先选择两个表中的小表，在内存中建立这张表的基于连接键的哈希表；优化器再扫描表连接中的大表，将大表中的数据与哈希表进行比较，如果有相关联的数据，则将数据添加到结果集中。

　　当表连接中的小表能够完全cache到可用内存的时候，哈希连接的效果最佳。哈希连接的成本只是两个表从硬盘读入到内存的成本。

　　但是，如果哈希表过大而不能全部cache到可用内存时，优化器将会把哈希表分成多个分区，再将分区逐一cache到内存中。当表的分区超过了可用内存时，分区的部分数据就会临时地写到磁盘上的临时表空间上。因此，分区的数据写磁盘时，比较大的区间（EXTENT）会提高I/O性能。ORACLE推荐的临时表空间的区间是1MB。临时表空间的区间大小由UNIFORMSIZE指定。

　　当哈希表构建完成后，进行下面的处理：

　　1）第二个大表进行扫描

　　2）如果大表不能完全cache到可用内存的时候，大表同样会分成很多分区

　　3）大表的第一个分区cache到内存

　　4）对大表第一个分区的数据进行扫描，并与哈希表进行比较，如果有匹配的纪录，添加到结果集里面5）与第一个分区一样，其它的分区也类似处理。

　　6）所有的分区处理完后，ORACLE对产生的结果集进行归并，汇总，产生最终的结果。

　　当哈希表过大或可用内存有限，哈希表不能完全CACHE到内存。随着满足连接条件的结果集的增加，可用内存会随之下降，这时已经CACHE到内存的数据可能会重新写回到硬盘去。如果出现这种情况，系统的性能就会下降。

　　当连接的两个表是用等值连接并且表的数据量比较大时，优化器才可能采用哈希连接。哈希连接是基于CBO的。只有在数据库初始化参数HASH_JOIN_ENABLED设为True,并且为参数PGA_AGGREGATE_TARGET设置了一个足够大的值的时候,Oracle才会使用哈希连接。HASH_AREA_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当使用HASH_AREA_SIZE。当使用ORDERED提示时,FROM子句中的第一张表将用于建立哈希表。

　　可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生哈希连接的执行计划。

　　selecta.user_name,b.dev_nofromuser_infoa,dev_infobwherea.user_id=b.user_id;

　　当缺少有用的索引时，哈希连接比嵌套循环连接更加有效。哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表比检索B_树索引更加迅速。

案例：

11:59:56 SCOTT@ prod> select  e.ename,e.sal,d.deptno,d.dname 
12:00:34   2    from dept d,emp e
12:00:34   3    where d.deptno=e.deptno ;
14 rows selected.
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   294 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   294 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    44 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
       1107  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

HASH JOIN与NETSTED LOOP、SORT MERGE JOIN连接方式对比：

介绍Hash Join算法步骤： 

1、Hash Join连接对象依然是两个数据表，首先选择出其中一个“小表”。这里的小表，就是参与连接操作的数据集合数据量小。对连接列字段的所有数据值，进行Hash函数操作。Hash函数是计算机科学中经常使用到的一种处理函数，利用Hash值的快速搜索算法已经被认为是成熟的检索手段。Hash函数处理过的数据特征是“相同数据值的Hash函数值一定相同，不同数据值的Hash函数值可能相同”；

2、经过Hash处理过的小表连接列，连同数据一起存放到Oracle PGA空间中。PGA中存在一块空间为hash_area，专门存放此类数据。并且，依据不同的Hash函数值，进行划分Bucket操作。每个Bucket中包括所有相同hash函数值的小表数据。同时建立Hash键值对应位图。

3、之后对进行Hash连接大表数据连接列依次读取，并且将每个Hash值进行Bucket匹配，定位到适当的Bucket上（应用Hash检索算法）；

4、在定位到的Bucket中，进行小规模的精确匹配。因为此时的范围已经缩小，进行匹配的成功率精确度高。同时，匹配操作是在内存中进行，速度较Merge Sort Join时要快很多；

案例：

12:16:16 SYS@ prod>create table sgtb as select * from dba_segments where owner='SYS';
Table created.
Elapsed: 00:00:00.73
12:17:05 SYS@ prod>create table obtb as select * from dba_objects where owner='SYS';
Table created.
Elapsed: 00:00:01.02
12:17:30 SYS@ prod>SELECT count(*) from sgtb;
  COUNT(*)
----------
      2312
Elapsed: 00:00:00.02
12:17:41 SYS@ prod>SELECT count(*) from obtb;
  COUNT(*)
----------
     30928
Elapsed: 00:00:00.04
12:17:51 SYS@ prod>
12:17:51 SYS@ prod>create index seg_name_ind on sgtb (segment_name);
Index created.
Elapsed: 00:00:00.27
12:19:00 SYS@ prod>create index ob_name_ind on obtb(object_name);
Index created.
Elapsed: 00:00:00.32
12:19:29 SYS@ prod>
12:19:29 SYS@ prod>exec dbms_stats.gather_table_stats(user,'SGTB',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.46
12:20:49 SYS@ prod>exec dbms_stats.gather_table_stats(user,'OBTB',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.33

HASH JOIN:

12:21:03 SYS@ prod>SET autotrace trace
12:21:32 SYS@ prod>select * from sgtb a,obtb b where a.segment_name=b.object_name;
2528 rows selected.
Elapsed: 00:00:00.14
Execution Plan
----------------------------------------------------------
Plan hash value: 1028776806
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2401 |   515K|   134   (1)| 00:00:02 |
|*  1 |  HASH JOIN         |      |  2401 |   515K|   134   (1)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| SGTB |  2312 |   279K|    13   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| OBTB | 30928 |  2899K|   121   (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."SEGMENT_NAME"="B"."OBJECT_NAME")
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        650  consistent gets
          0  physical reads
          0  redo size
     223156  bytes sent via SQL*Net to client
       2371  bytes received via SQL*Net from client
        170  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2528  rows processed

NETSTED LOOP:

12:22:41 SYS@ prod>select  * from sgtb a,obtb b where a.segment_name=b.object_name;
2528 rows selected.
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 2080873268
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  2401 |   515K|  4638   (1)| 00:00:56 |
|   1 |  NESTED LOOPS                |             |       |       |            |          |
|   2 |   NESTED LOOPS               |             |  2401 |   515K|  4638   (1)| 00:00:56 |
|   3 |    TABLE ACCESS FULL         | SGTB        |  2312 |   279K|    13   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | OB_NAME_IND |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| OBTB        |     1 |    96 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"."SEGMENT_NAME"="B"."OBJECT_NAME")
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3065  consistent gets
          0  physical reads
          0  redo size
     213135  bytes sent via SQL*Net to client
       2371  bytes received via SQL*Net from client
        170  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2528  rows processed

SORT MERGE JOIN:

12:24:30 SYS@ prod>select  * from sgtb a,obtb b where a.segment_name=b.object_name;
2528 rows selected.
Elapsed: 00:00:00.16
Execution Plan
----------------------------------------------------------
Plan hash value: 2191280214
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  2401 |   515K|       |   883   (1)| 00:00:11 |
|   1 |  MERGE JOIN         |      |  2401 |   515K|       |   883   (1)| 00:00:11 |
|   2 |   SORT JOIN         |      |  2312 |   279K|   840K|    80   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| SGTB |  2312 |   279K|       |    13   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      | 30928 |  2899K|  8136K|   803   (1)| 00:00:10 |
|   5 |    TABLE ACCESS FULL| OBTB | 30928 |  2899K|       |   121   (1)| 00:00:02 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"."SEGMENT_NAME"="B"."OBJECT_NAME")
       filter("A"."SEGMENT_NAME"="B"."OBJECT_NAME")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        485  consistent gets
          0  physical reads
          0  redo size
     235884  bytes sent via SQL*Net to client
       2371  bytes received via SQL*Net from client
        170  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       2528  rows processed

成本对比：

       最后，Hash Join使用的场景是有限制的。其中最大的一个就是连接操作仅能使用“=”连接。因为Hash匹配的过程只能支持相等操作。还有就是连接列的数据分布要尽量做到数据分布均匀，这样产生的Bucket也会尽可能均匀。这样限制匹配的速度才有保证。如果数据列分布偏移严重，Hash Join算法效率会有退化趋势。

---以上内容整理自书籍和网络，感谢感谢各位作者！