1. 首页
  2. IT资讯

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中没有重复的记录。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-750120/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183302.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code