NOT IN 一次優化

       
源語句如下：
select t.id,
               t.fundapplyno,
               t.applycomcode,
               t.transferdate,
               t.extaccountno,
               t.amount,
               t.extaccountname
          from ts_transfer t
         where t.importtypeid = 3
           and t.status in (4, 6)
           and t.transferdate > to_date('2012-09-29', 'yyyy-mm-dd')
           and t.transferdate < to_date('2012-11-01', 'yyyy-mm-dd')
           and t.applycomcode  NOT in
               (select b.fundapplyno
                  from gppaymentfund_bz b
                 where b.amount > 0
                   and b.fundstatus = 6
                   and b.fundapplydate > to_date('2012-09-29', 'yyyy-mm-dd')
                   and b.fundapplydate < to_date('2012-11-01', 'yyyy-mm-dd'))
執行計劃：
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1031211341
--------------------------------------------------------------------------------
| Id  | Operation           | Name             | Rows  | Bytes |TempSpc| Cost (%
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  | 33064 |  3099K|       |    56M
|   1 |  SORT ORDER BY      |                  | 33064 |  3099K|  7800K|    56M
|*  2 |   FILTER            |                  |       |       |       |
|*  3 |    TABLE ACCESS FULL| TS_TRANSFER      | 33064 |  3099K|       |  5436
|*  4 |    TABLE ACCESS FULL| GPPAYMENTFUND_BZ |     1 |    38 |       |  1876
--------------------------------------------------------------------------------

這裡雖然有全表掃描但是問題的根源不是全表掃描，也不是在排序。而在於執行計劃選擇了filter，
而filter類似於nestloop的方式進行訪問，效率低下這個語句跑不出結果集或者說要等很久。
通過分析，使用minus來代替了這樣的訪問方式改寫語句如下：
select *
  from (select t.id,
               t.fundapplyno,
               t.applycomcode,
               t.transferdate,
               t.extaccountno,
               t.amount,
               t.extaccountname
          from ts_transfer t
         where t.importtypeid = 3
           and t.status in (4, 6)
           and t.transferdate > to_date('2012-3-31', 'yyyy-mm-dd')
           and t.transferdate < to_date('2012-11-01', 'yyyy-mm-dd')
        minus
        select t.id,
               t.fundapplyno,
               t.applycomcode,
               t.transferdate,
               t.extaccountno,
               t.amount,
               t.extaccountname
          from ts_transfer t
         where t.importtypeid = 3
           and t.status in (4, 6)
           and t.transferdate > to_date('2012-3-31', 'yyyy-mm-dd')
           and t.transferdate < to_date('2012-11-01', 'yyyy-mm-dd')
           and t.applycomcode  in
               (select b.fundapplyno
                  from gppaymentfund_bz b
                 where b.amount > 0
                   and b.fundstatus = 6
                   and b.fundapplydate > to_date('2012-2-29', 'yyyy-mm-dd')
                   and b.fundapplydate < to_date('2012-11-01', 'yyyy-mm-dd')))
 order by transferdate desc;
同時建立了索引
SQL> create index gaopengtest2 on gppaymentfund_bz(fundapplydate,fundstatus,amount,fundapplyno);
 
Index created
SQL> create index gaopengtest1 on ts_transfer(transferdate,IMPORTTYPEID);
 
Index created
所以test2的意思在於讓訪問走INDEX FAST FULL SCAN，而索引TEST1的意思在於當日期取值範圍較小時可以走索引範圍掃描。但是這裡是用不到的。

改寫後執行計劃如下：
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 480831536
--------------------------------------------------------------------------------
| Id  | Operation                 | Name         | Rows  | Bytes |TempSpc| Cost
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |              | 33064 |    10M|       | 14513
|   1 |  SORT ORDER BY            |              | 33064 |    10M|    21M| 14513
|   2 |   VIEW                    |              | 33064 |    10M|       | 12258
|   3 |    MINUS                  |              |       |       |       |
|   4 |     SORT UNIQUE           |              | 33064 |  3099K|  7800K|  6154
|*  5 |      TABLE ACCESS FULL    | TS_TRANSFER  | 33064 |  3099K|       |  5424
|   6 |     SORT UNIQUE           |              |  2922 |   382K|   872K|  6104
|*  7 |      HASH JOIN            |              |  2922 |   382K|  2856K|  6014
|*  8 |       INDEX FAST FULL SCAN| GAOPENGTEST2 | 58355 |  2165K|       |   284
|*  9 |       TABLE ACCESS FULL   | TS_TRANSFER  | 30125 |  2824K|       |  5436
--------------------------------------------------------------------------------
最後ORACLE使用HASHjion 代替了IN ，語句的執行時間也優化到9秒。

一句話優化SQL必須找到問題的根源，並不是平空猜測的。如果憑空猜測也許很多人要把這裡的NOT IN 改為NOT EXISTS  實際上ORACLE 10G 後這樣的代替沒有什麼意義。NOT IN和NOT EXISTS 是等價的。（不知道9I怎麼樣）

當然這裡的代替只適合結果集1中沒有重複的記錄。