ITPub博客

首页 > 数据库 > Oracle > Oracle not exist子查询全扫的优化

Oracle not exist子查询全扫的优化

原创 Oracle 作者:maohaiqing0304 时间:2016-08-12 12:53:51 0 删除 编辑


作者:lōττéry©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]


前言:
 之前写过 Oracle 针对子查询里有group by 表全扫的优化(子查询和外层关系是left join);
 本次介绍 子查询与外层是not exists的关系是如何优化子查询全扫的;
  
优化前:
SQL> SET AUTOTRACE ON
SQL> set timing on
SQL> set line 1000
SQL> select nvl(l.colthno || ',' || l.size_no, 'N')
   from bill_asn_dtl_n_i l
 where l.nos = 'MPD038DA15050001'
   and not exists (select 'x'
          from item_barcode m, item it
         where m.item_no = it.item_no
           and it.item_bi_no = l.colthno
           and m.size_no = l.sys_no || l.size_no
           and m.package_id = 0)
   and rownum = 1; 

no rows selected

Elapsed: 00:00
:05.94     --->每天执行上万次

Execution Plan
----------------------------------------------------------
Plan hash value: 2989337734
-----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |      1 |     58 |        | 47004   (1)| 00:09:25 |
|*  1 |  COUNT STOPKEY          |             |        |        |        |         |        |
|*  2 |   HASH JOIN ANTI      |             |      3 |    174 |        | 47004   (1)| 00:09:25 |
|*  3 |    INDEX SKIP SCAN    | PK_BILL_ASN_DTL_N_I |     15 |    600 |        |     38   (0)| 00:00:01 |
|   4 |    VIEW           | VW_SQ_1         |  4844K|     83M|        | 46953   (1)| 00:09:24 |
|*  5 |     HASH JOIN          |             |  7264K|    353M|     42M| 70402   (1)| 00:14:05 |
|   6 |      TABLE ACCESS FULL| ITEM            |  1152K|     29M|        | 15882   (1)| 00:03:11 |
|*  7 |      TABLE ACCESS FULL| ITEM_BARCODE    |  7264K|    166M|        | 39988   (1)| 00:08:00 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
   2 - access("ITEM_1"="L"."COLTHNO" AND "ITEM_2"="L"."SYS_NO"||"L"."SIZE_NO")
   3 - access("L"."NOS"='MPD038DA15050001')
       filter("L"."NOS"='MPD038DA15050001')
   5 - access("M"."ITEM_NO"="IT"."ITEM_NO")
   7 - filter("M"."PACKAGE_ID"=0)
Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
  205744  consistent gets
      0  physical reads
      0  redo size
    362  bytes sent via SQL*Net to client
    513  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed
 
SQL>

分析:
 慢的部分是item/item_barcode 2个大表全扫;
 由于bill_asn_dtl_n_i表必要条件nos = 'MPD038DA15050001'过滤后量较小,且与2个全扫大表是not exist关系;
 所以可以将bill_asn_dtl_n_i过滤后的结果 与 not exist子查询里的2个大表做关联,满足相同后再与外层判断是否not exist;
 没必要将not exist 子查询2个大表全扫再与外层判断是否有相同的再过滤;

优化后:
SQL> with t_1 as (select /*+ materialize */ colthno,sys_no,size_no from bill_asn_dtl_n_i l
 where l.nos = 'MPD038DA15050001' )
select nvl(l.colthno || ',' || l.size_no, 'N')
   from t_1 l
 where  not exists (select 'x'
          from item_barcode m, item it,t_1
         where m.item_no = it.item_no
           and it.item_bi_no = l.colthno
           and m.size_no = l.sys_no || l.size_no
           and m.package_id = 0
           and it.item_bi_no = t_1.colthno
           and m.size_no = t_1.sys_no || t_1.size_no)
   and rownum = 1; 

no rows selected

Elapsed: 00:00:00.53

Execution Plan
----------------------------------------------------------
Plan hash value: 3572864059
----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                   |     1 |   143 | 15972     (1)| 00:03:12 |
|   1 |  TEMP TABLE TRANSFORMATION     |                   |       |       |        |           |
|   2 |   LOAD AS SELECT         | SYS_TEMP_0FD9D6DB8_595B6478 |       |       |        |           |
|*  3 |    INDEX SKIP SCAN         | PK_BILL_ASN_DTL_N_I           |     2 |    80 |    38     (0)| 00:00:01 |
|*  4 |   COUNT STOPKEY          |                   |       |       |        |           |
|*  5 |    HASH JOIN ANTI         |                   |    15 |  2145 | 15934     (1)| 00:03:12 |
|   6 |     VIEW             |                   |    15 |  1185 |     2     (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL         | SYS_TEMP_0FD9D6DB8_595B6478 |    15 |   330 |     2     (0)| 00:00:01 |
|   8 |     VIEW             | VW_SQ_1               |     1 |    64 | 15932     (1)| 00:03:12 |
|   9 |      NESTED LOOPS         |                   |     1 |   130 | 15932     (1)| 00:03:12 |
|  10 |       NESTED LOOPS         |                   |    15 |   130 | 15932     (1)| 00:03:12 |
|* 11 |        HASH JOIN         |                   |    15 |  1590 | 15887     (1)| 00:03:11 |
|  12 |     VIEW             |                   |    15 |  1185 |     2     (0)| 00:00:01 |
|  13 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6DB8_595B6478 |    15 |   330 |     2     (0)| 00:00:01 |
|* 14 |     TABLE ACCESS FULL     | ITEM                |   841K|    21M| 15883     (1)| 00:03:11 |
|* 15 |        INDEX RANGE SCAN      | PK_ITEM_BARCODE           |     1 |       |     2     (0)| 00:00:01 |
|* 16 |       TABLE ACCESS BY INDEX ROWID| ITEM_BARCODE            |     1 |    24 |     3     (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("L"."NOS"='MPD038DA15050001')
       filter("L"."NOS"='MPD038DA15050001')
   4 - filter(ROWNUM=1)
   5 - access("ITEM_0"="L"."COLTHNO" AND "ITEM_1"="L"."SYS_NO"||"L"."SIZE_NO")
  11 - access("IT"."ITEM_BI_NO"="T_1"."COLTHNO")
  14 - filter("IT"."ITEM_BI_NO" IS NOT NULL)
  15 - access("M"."ITEM_NO"="IT"."ITEM_NO" AND "M"."SIZE_NO"="T_1"."SYS_NO"||"T_1"."SIZE_NO")
  16 - filter("M"."PACKAGE_ID"=0)
Statistics
----------------------------------------------------------
      2  recursive calls
      8  db block gets
   58718  consistent gets
     16  physical reads
    532  redo size
    362  bytes sent via SQL*Net to client
    513  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      0  rows processed

SQL>  

  【源于本人笔记】 若有书写错误,表达错误,请指正...


此条目发表在   SQL、SQL优化篇  分类目录。将固定连接加入收藏夹。


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

请登录后发表评论 登录
全部评论
擅长SQL编写及SQL优化,分析瓶颈,性能调优、故障处理,根据实际情况定制备份策略; 擅长编写脚本来实现自动化功能,600+SQL优化经验案例,为人热爱学习,喜欢钻研技术,对工作认真负责。

注册时间:2013-03-13

  • 博文量
    121
  • 访问量
    2303704