选择最合适的连接方式

声明：本例十分简单，只适合新手DBA走马，老司机请自觉绕道~

背景：跑批中一条SQL“卡住”了三个小时.....

SQL以及执行计划如下：

SELECT A.*
  FROM SSS_DDD_ACCT_DDD_DDD a
WHERE exists (select nameFrom
          from (select Acct_Num as nameFrom
                  from SSS_DDD_ACCT
                UNION
                select Acct_Num as nameFrom
                  from SSS_OOO_ACCT
                UNION
                select Bcs_Acct_Num as nameFrom
                  from SSS_NNNN_ACCT
                UNION
                select to_char(OLDACCNO) as nameFrom
                  from OLDNEW
                UNION
                select to_char(NEWACCNO) as nameFrom from OLDNEW)
         where a.Cntrpty_Acct_Num = nameFrom)
 AND (Exter_Sys_Cd = '000' or Exter_Sys_Cd = '099' or
       Exter_Sys_Cd = '063')    
   AND Db_Cr_Ind = '0'
   AND CUST_NO IS NOT NULL
   AND txn_dt = DATE '2018-06-11';

Plan hash value: 761150565
 
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     1 |   667 |       |   296K  (1)| 00:59:13 |       |       |
|   1 |  NESTED LOOPS                       |                      |     1 |   667 |       |   296K  (1)| 00:59:13 |       |       |
|   2 |   VIEW                              |                      |  5928K|   576M|       |   157K  (1)| 00:31:36 |       |       |
|   3 |    SORT UNIQUE                      |                      |     1 |   115M|   154M|   157K  (1)| 00:31:36 |       |       |
|   4 |     UNION-ALL                       |                      |       |       |       |            |          |       |       |
|   5 |      TABLE ACCESS FULL              | SSS_DDD_ACCT         |  1491K|    27M|       | 22862   (1)| 00:04:35 |       |       |
|   6 |      TABLE ACCESS FULL              | SSS_OOO_ACCT         |  3178K|    54M|       | 66443   (1)| 00:13:18 |       |       |
|   7 |      TABLE ACCESS FULL              | SSS_NNNN_ACCT        |   707K|    12M|       | 30184   (1)| 00:06:03 |       |       |
|   8 |      TABLE ACCESS FULL              | OLDNEW               |   275K|    12M|       |   889   (1)| 00:00:11 |       |       |
|   9 |      TABLE ACCESS FULL              | OLDNEW               |   275K|  9676K|       |   889   (1)| 00:00:11 |       |       |
|  10 |   PARTITION RANGE ALL               |                      |     1 |   565 |       |   192   (0)| 00:00:03 |     1 |    48 |
|* 11 |    TABLE ACCESS BY LOCAL INDEX ROWID| SSS_DDD_ACCT_DDD_DDD |     1 |   565 |       |   192   (0)| 00:00:03 |     1 |    48 |
|* 12 |     INDEX SKIP SCAN                 | INDEX_NUM_4          |     1 |       |       |   191   (0)| 00:00:03 |     1 |    48 |
------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  11 - filter("TXN_DT"=TO_DATE(' 2018-06-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CUST_NO" IS NOT NULL)
  12 - access("A"."CNTRPTY_ACCT_NUM"="NAMEFROM" AND "DB_CR_IND"='0')
       filter("DB_CR_IND"='0' AND ("EXTER_SYS_CD"='000' OR "EXTER_SYS_CD"='063' OR "EXTER_SYS_CD"='099') AND 
              "A"."CNTRPTY_ACCT_NUM"="NAMEFROM")


SQL执行了三个小时不出结果......

执行计划Id=1 SQL语句的连接方式是 NEST LOOP

从执行计划来看  有几处不合理 

1.Id = 5、6、7、8、9这几个全表扫描(TABLE_ACCESS_FULL) 返回的行数(Rows) 至少也是27W行

2.Id = 3 排序去重(Sort Unique)之后 Rows变成1（也就是优化器评估返回0行，遇到过很多次这种情况）

3.Id = 2 排序去重的结果集（只有0-1行数据）作为一个VIEW ，预估VIEW返回的结果为592W行

4.Id = 1嵌套循环的驱动表为Id =2 的view结果集。驱动表返回的结果为500w+ 优化器却选择了NEST LOOP

NEST LOOP 和 HASH 没有特别分明的临界点 常识是驱动表返回1w-10w（看服务器性能）走NL速度会明显优于HASH

驱动表返回数据量到达百万级以后  HASH的优势越来越明显，NL的速度会几何状降低，我画个图如下：

这时候需要验证 驱动表返回的行数是不是500w+

1).问业务人员  这几张表的大小

2).查统计信息视图 如果统计信息没过期 num_rows就是基本上接近准确的数据量

3).上面俩方法行不通 就select COUNT(*)

我这边通过开发人员那边反馈这个几个表的大小合计在1000w左右

所以这个语句使用NEST LOOP ，被驱动表会被访问1000w次（1000w次的index skip scan + 回表） 如果查看AWR SQL会发现逻辑读会大的"不像话"。 所以整个SQL语句只要使用HASH代替NEST LOOP就好

SELECT /*+ use_hash(a)*/A.*
  FROM SSS_DDD_ACCT_DDD_DDD a
WHERE exists (select nameFrom
          from (select Acct_Num as nameFrom
                  from SSS_DDD_ACCT
                UNION
                select Acct_Num as nameFrom
                  from SSS_OOO_ACCT
                UNION
                select Bcs_Acct_Num as nameFrom
                  from SSS_NNNN_ACCT
                UNION
                select to_char(OLDACCNO) as nameFrom
                  from OLDNEW
                UNION
                select to_char(NEWACCNO) as nameFrom from OLDNEW)
         where a.Cntrpty_Acct_Num = nameFrom)
 AND (Exter_Sys_Cd = '000' or Exter_Sys_Cd = '099' or
       Exter_Sys_Cd = '063')    
   AND Db_Cr_Ind = '0'  
   AND CUST_NO IS NOT NULL   
   AND txn_dt = DATE '2018-06-11';
select * from table(dbms_xplan.display);
Plan hash value: 3514384469
 
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     1 |   667 |       |   799K  (1)| 02:39:59 |       |       |
|*  1 |  HASH JOIN SEMI                     |                      |     1 |   667 |    19M|   799K  (1)| 02:39:59 |       |       |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SSS_DDD_ACCT_DDD_DDD | 34532 |    18M|       |   608K  (1)| 02:01:48 | ROWID | ROWID |
|*  3 |    INDEX SKIP SCAN                  | SELECT_DEPINFO_NUM   |   621K|       |       |   472K  (1)| 01:34:36 |       |       |
|   4 |   VIEW                              |                      |  5928K|   576M|       |   157K  (1)| 00:31:36 |       |       |
|   5 |    SORT UNIQUE                      |                      |     1 |   115M|   154M|   157K  (1)| 00:31:36 |       |       |
|   6 |     UNION-ALL                       |                      |       |       |       |            |          |       |       |
|   7 |      TABLE ACCESS FULL              | SSS_DDD_ACCT         |  1491K|    27M|       | 22862   (1)| 00:04:35 |       |       |
|   8 |      TABLE ACCESS FULL              | SSS_OOO_ACCT         |  3178K|    54M|       | 66443   (1)| 00:13:18 |       |       |
|   9 |      TABLE ACCESS FULL              | SSS_NNNN_ACCT        |   707K|    12M|       | 30184   (1)| 00:06:03 |       |       |
|  10 |      TABLE ACCESS FULL              | OLDNEW               |   275K|    12M|       |   889   (1)| 00:00:11 |       |       |
|  11 |      TABLE ACCESS FULL              | OLDNEW               |   275K|  9676K|       |   889   (1)| 00:00:11 |       |       |
------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."CNTRPTY_ACCT_NUM"="NAMEFROM")
   2 - filter("DB_CR_IND"='0' AND ("EXTER_SYS_CD"='000' OR "EXTER_SYS_CD"='063' OR "EXTER_SYS_CD"='099') AND "CUST_NO" IS 
              NOT NULL)
   3 - access("TXN_DT"=TO_DATE(' 2018-06-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter("TXN_DT"=TO_DATE(' 2018-06-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

修改完之后SQL只用了30秒就响应结果！！！

这里总结一下连接方式的应用场景(包含排序合并连接，排序合并连接和HASH 有单独的案例--> 半连接、反连接的优化案例)
1.　哈希连接只适用于等值连接。
2.　嵌套循环是行源连接方式，只适合小量数据连接。
    　哈希连接是集合连接方式，适合大量数据连接。
3.　在等值连接方式下，返回少量记录（<10000）且内部表在连接列上存在索引，适合嵌套循环连接。若返回大量记录则适合哈希连接。
4.　在等值连接方式下，两个行源集合都很大，若连接列是高基数列，则适合哈希连接，否则适合排序合并连接。
5.　嵌套循环连接可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据。而其它两种连接方式则不行。
6.    排序合并连接的两个数据集可以并行处理,而嵌套循环和哈希连接不能.

 

后记：SORT UNIQUE导致Rows的估算变成了1,从而导致了数据库在选择连接方式的时候出错。而且,这里的执行计划也存在问题，给人的感觉是明明知道那几个表UNION的结果集的行数，却在SORT UNIQUE那一步翻车了,这种问题我遇到过不止1次，10g、11g都存在。至于SORT UNIQUE为什么导致Rows的估算就变成1了,目前尚不清楚。