半连接、反连接的优化案例

先来理解几个概念：

半连接：两表关联，只返回匹配上的数据并且只会返回一张的表的数据，半连接一般就是指的在子查询中出现 IN 和 EXISTS

反连接：两表关联，只返回主表的数据，并且只返回主表与子表没关联上的数据，这种连接就叫反连接。反连接一般就是指的 NOT IN 和 NOT EXISTS

子查询展开：优化器将嵌套的子查询展开成一个等价的JOIN，然后去优化这个JOIN。如果不展开的情形是，从主表中获取的每1条数据，都要代入子查询进行匹配。一般情况下效率都是比较低的，这时候执行计划中会出现关键字FILTER。当子查询不展开，SQL无法从连接方式、连接顺序 等方面优化整个查询。因为这些都是固定的了

排序合并连接：排序合并连接原理是先对两个表/行源根据JOIN列进行排序(当然了排序的时候要踢出不符合where条件的列)，然后再进行连接。排序合并连接只适用于非等值JOIN。。根据排序合并的原理，我们知道排序合并连接其实很耗费资源，因为要对2个表/结果集进行排序，2个表都需要放入PGA，所以一般情况下，CBO是不会选择走SORT MERGE JOIN

案例背景：某系统新上上了一条SQL语句，结果跑不出来.......

INSERT INTO AAA_AAAA.AAA_MID_FFF
  SELECT S.AS_OF_DATE,
         S.CUST_ACCT_NO,
         S.GL_ACCT_ID,
         S.ORGIN_DT,
         S.DUE_DT,
         S.TRAN_METHOD,
         S.INTEREST_RATE,
         S.TRAN_END_RATE,
         S.INTEREST_RATE - S.TRAN_END_RATE MARGIN_RATE
    FROM AAA_AAAA.AAA_MID_INSTAAAAAA S
   WHERE S.CUST_ACCT_NO NOT IN
         (SELECT DISTINCT T.CUST_ACCT_NO FROM AAA_MID_FFF T)
     AND S.MA_ACCT_NO IN
         (SELECT
           MA_ACCT_NO
            FROM (SELECT R.MA_ACCT_NO,
                         ROW_NUMBER() OVER(PARTITION BY R.CUST_ACCT_NO ORDER BY R.ORGIN_DT) RN
                    FROM AAA_MID_FFF_RESULT R
                   WHERE R.USED_TRAN_METHOD = 'Repricing_Term') M
           WHERE M.RN = 1);
Plan hash value: 466080511
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |                    |     1 |   513 |   416   (1)| 00:00:05 |
|   1 |  LOAD TABLE CONVENTIONAL     | AAA_MID_FFF        |       |       |            |          |
|*  2 |   FILTER                     |                    |       |   513 |   416   (1)| 00:00:05 |
|*  3 |    HASH JOIN ANTI NA         |                    |     1 |   655 |   416   (1)| 00:00:05 |
|   4 |     TABLE ACCESS FULL        | AAA_MID_INSTAAAAAA |     1 |   495 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL