Oracle not exist子查询全扫的优化



作者: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优化篇  分类目录。将固定连接加入收藏夹。


本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
【故障处理】 ORA-12545: Connect failed because target host or object does not exist 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ①  错误 ORA-12545: Connect failed because target host or object does not exis
今天写了个脚本,虽然实现的功能不多,但是个人感觉是一个好的开始,架子出来了,后面要补充的细节加进来就逐步完善了。 这个脚本的运行效果如下: OS     Version  is :[ RHEL_6.3 ] Oracle Version  is :[ 11.2.0.3.0] Oracle Instance is :[ dgtest ] dgtest ORACLE_HOME     is :[ /U01/app/oracle/product/11.2.0.2/db_1  ] Oracle  status  is

DUAL系列 - 2016-08-12 17:08:18

DUAL 系列   1    BLOG 文档结构图     2    前言部分   2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ① DUAL遭到破坏后的重建(重点) ②  关于参数replication_dependency_tracking简介 ③ DUAL简介     Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园 ( http://www.
今天在处理一个工单的时候发现了一个奇怪的现象,开发同学需要创建一个存储过程,目前的架构类似这样的形式 数据库中存在一个属主用户,表,存储过程等对象都创建在这个用户上,而另外有一些连接用户,根据业务和功能可能访问的对象权限也有所不同。所以就会出现一个owner,多个connect user的情况。这种方式可以减少很多误操作,权限控制更为细粒度。 现在的问题是在owner用户上创建存储过程,存储过程会引用若干张表,都在owner用户下,而connect user下则没有这些表相关的任何同义词。看起来好像是不大合

Oracle如何删除表中重复记录 - 2016-08-11 14:08:09

Oracle如何删除表中重复记录 1    引言 在对数据库进行操作过程中我们可能会遇到这种情况,表中的数据可能重复出现,使我们对数据库的操作过程中带来 读诸 多不便,那么怎么删除这些重复没有用的数据呢 ? 平时工作中可能会遇到当试图对库表中的某一列或几列创建唯一索引时,系统提示 ORA-01452  :不能创建唯一索引,发现重复记录。 2    处理过程 重复的数据可能有这样两种情况 : 第一种 是 表中只有某些字段一样,第二种是两行记录完全一样 。删除重复记录后的结果也分为 2 种, 第一种 是重复的

外连接转换为内连接的情况 - 2016-08-10 04:08:06

一般的情况下外连接如下a right join b on a.id=b.id 那么b一定要作为驱动表,原因在于只有b作为驱动表才能得到完整的结果集,如果a作为驱动,那么返回的结果集 可能不完整,但是在特殊的情况的,可能将外连接转换为内连接 考虑如下的情况 b    id  name   1   g1   1   g2   2   g3   2   g4 a   id name   2  gname2 使用如下语句: select b.id,a.id from  a right join b on a.id=
今天总算抽了些时间把半自动化的脚本完成了大半,目前还缺少两部分的脚本,一部分是安装前的检查脚本,可以做一个预检查。虽然目前来看还不是必须,但是这些是标准和规范的地方,这些条件不满足,失败的概率会加大。另外一部分是安装后的补充脚本,其实安装后还有很多需要注意的地方。 大体想了下,补充的脚本包含下面的部分。 配置crontab,目前的常用job是定期删除归档,定期检查监听的情况 配置iptables ,把主库的防火墙信息拷贝过来,或者作为静态备份,需要是启用 配置大页,这个可以在优化的基础上进行计算,在内核参数

MongoBD 日常操作小节 - 2016-08-09 17:08:09

一、开启Mongodb 密码验证功能 默认安装完mongodb是不用密码验证的,直接输入mongo就可以登入数据库进行相关操作,设置参数auth=true启动mongodb密码验证功能,开启改功能步骤如下: ①、 修改参数文件auth=false,并重启mongodb ②、登入数据库,创建管理员用户(默认是没有管理员账户的) [ root@mon godb ~]  # mongo user admin   db.createUser(     {       user: "admin",       pw

在Buffer Cache中自动大表缓存 - 2016-08-09 17:08:09

约翰,在A银行做DBA。凯特琳,新的首席技术官,她正在为数据库性能问题由于长的I/O响应时间而沮丧。为了提高性能,她希望增加数据库实例的缓冲区缓存,以便在缓存中缓存更多的数据,从而减少了频繁需要去读磁盘。戴比,系统开发工程师,经历了很多这样的问题在她的职业生涯中,她解释说,“我们的应用有很多全表扫描,数据库的全表扫描使用的是直接路径读取(DPR)在表中的数据块,不做缓冲区高速缓存。”因此,添加物理内存到服务器后,增加缓冲区缓存,是不会帮助,因为缓冲区没有用。应用程序可以通过加hits跳过DPRs,使用缓冲

MySQL案例-mysqld got signal 11 - 2016-08-09 14:08:35

-------------------------------------------------------------------------------------------------正文--------------------------------------------------------------------------------------------------------------- 背景: MySQL-5.7.12, debian 8核16G虚拟机 , 业务方反馈在某一