源语句如下：
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中没有重复的记录。