Oracle not exist子查询全扫的优化

最新推荐文章于 2024-05-14 11:24:22 发布
最新推荐文章于 2024-05-14 11:24:22 发布
阅读量961 收藏
点赞数

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

前言：
  之前写过  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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28602568/viewspace-2123386/

cixiaobi8104
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
django ObjectDoesNotExist 和 DoesNotExist的用法
09-16
在 Django 框架中，`ObjectDoesNotExist` 和 `DoesNotExist` 是两种常见的异常类型，它们用于处理数据查询未找到结果的情况。了解并正确使用这些异常可以帮助开发者编写更健壮、容错性更强的代码。 `ObjectDoesNot...
SQL中对not in和not exist查询的替代算法.pdf
01-04
"SQL中对not in和not exist查询的替代算法.pdf" 本文主要讨论了SQL中对not in和not exist查询的替代算法。首先，作者简要介绍了SQL语言的基本概念和特点，然后讨论了not in和not exist查询的低效性及其原因。接着，...
Oracle表查询变换优化思路
xiaobinhxb的博客
08-12 883
Oracle表查询变换优化思路，包括子查询非嵌套、视图合并、谓词推入、让半连接和反连接作驱动表
Oracle 数据库查询优化
云满笔记
09-28 3372
Oracle 数据库查询优化
Oracle Exists、 NOT Exists用法
最新发布
K___End的博客
05-14 1655
EXISTS提供了一种高效的方式来判断行的存在性，是处理子查询时的一个强大工具，尤其是在需要基于条件判断数据是否存在，而不关心具体值的情况下。NOT EXISTS在Oracle数据库中，NOT EXISTS关键字用于子查询中，用来检查子查询是否有结果。与EXISTS相反，NOT EXISTS用于筛选出那些在子查询中找不到匹配项的记录。当子查询没有返回任何行时，NOT EXISTS子句的条件为真，从而外层查询的行会被包含在结果集中。以下是NOT EXISTSNOT EXISTS。
oracle语法优化总结（个人实战总结）
CSDN86363160的博客
12-22 1057
oracle语法优化总结
oracle not exist优化,求助优化一条SQL,关于Not Exists
weixin_39785400的博客
04-04 431
----------------------------------------------------------------------------------------------------| Id| Operation | Name | Rows| Bytes |TempSpc| Cost...
oracle not exists not in,Oracle IN&EXISTS与NOT IN&NOT EXISTS 的优化原则的讨论
weixin_35755562的博客
04-03 349
Oracle IN&EXISTS与NOT IN&NOT EXISTS 的优化原则的讨论[@more@]uestion:-----------------------------------------------------------1.EXISTS的执行流程select * from t1 where exists ( select null from t2 where y =...
Code::Blocks – The file does not exist.
01-09
Code::Blocks – The file does not exist. 1. Code::Blocks Could not open the file '/home/strong/codeblocks_work/Fire-master/src/bubble/config.cpp'. The file does not exist. 2. Build log ...... g++: ...
does not exist.解决/root/.Xauthority does not exist
06-18
标题 "does not exist. 解决 /root/.Xauthority does not exist" 涉及到的是一个常见的 Linux 系统问题，通常在使用图形界面（如 X Window System）或通过 SSH 远程连接时遇到。这个问题指出，系统找不到 `/root/....
运行Sqoop出现hcatalog does not exist!…accumulo does not exist!解决方案
01-07
Warning: /opt/module/sqoop/bin/…/…/hcatalog does not exist! HCatalog jobs will fail. Please set $HCAT_HOME to the root of your HCatalog installation. Warning: /opt/module/sqoop/bin/…/…/accumulo ...
NOT EXISTS真的不走索引么?如何优化NOT EXISTS!
guanfengliang的专栏
06-19 1万+
在一些业务场景中，会使用NOT EXISTS语句确保返回数据不存在于特定集合，部分同事会发现NOT EXISTS有些场景性能较差，甚至有些网上谣言说”NOT EXISTS不走索引”，哪对于NOT EXISTS语句，我们如何优化呢？ ##========================================================## 以今天优化的SQL为...
oracle+in改exists,in, not in, exists, not exists, join 改写
weixin_27298377的博客
04-03 372
create or replace view v1 as select ename,job,sal from emp where deptno = 30;create or replace view v2 as select ename,job,sal from emp;-- 查找两个表中匹配的行/*使用内连接*/select v1.* from v1,v2 where v1.ename=v2.e...
Oracle not exists的等价写法
关注系统性能调优
11-18 4591
not exists可以改为left join + is null，可以看到改写前后执行计划一样，消耗资源一样，说明完全等价。 SQL> drop table test purge; SQL> drop table test1 purge; SQL> create table test as select * from dba_objects; SQL> create table test
ORACLE:EXISTS优化
Super菜的专栏
06-29 3184
原sql： SELECT a.id, a.orderid, a.keyword, a.begindate, a.enddate, a.providerid FROM searchengine_keyword a WHERE EXISTS (SELECT * FROM ( S
Oracle中 NOT IN 优化
Stephen.G
02-28 1万+
针对一个 NOT IN 语句： SELECT OBJECT_ID FROM HSP_OBJECT T WHERE T.OBJECT_ID NOT IN ( SELECT DISTINCT FORM_ID FROM HSP_FORM TT ) ORDER BY T.OBJECT_ID ; 如果数据量不大，那么没有问题。 但是数据量过大的时候，NOT IN 效率差的特点会十分明显。 此
oracle exists有用到索引,oracle数据库sql的优化总结
weixin_30531997的博客
04-06 635
一：使用where少使用having;二：查两张以上表时，把记录少的放在右边；三：减少对表的访问次数；四：有where子查询时，子查询放在最前；五：select语句中尽量避免使用*(执行时会把*依次转换为列名)；六：尽量多的使用commit；七：Decode可以避免重复扫描相同的记录或重复连接相同的表；八：通过内部函数也可提高sql效率；九：连接多个表时，使用别名并把别名前缀于每个字段上；十：用e...
不会使用索引，导致全表扫描情况
phantomes的专栏
09-25 5489
不会使用索引，导致全表扫描情况 1.不要使用in操作符，这样数据库会进行全表扫描， 推荐方案：在业务密集的SQL当中尽量不采用IN操作符 2.not in 使用not in也不会走索引 推荐方案：用not exists或者（外联结+判断为空）来代替 3同样不会使用索引，因此对它的处理只会产生全表扫描 推荐方案：用其它相同功能的操作运算代替，如 a0 or a a’’
MySQL NOT EXISTS优化的一个案例
热门推荐
刀刀的专栏
10-22 2万+
原始语句：SELECT * FROM dcf_account.t_posting_transaction t1 WHERE NOT EXISTS ( SELECT * FROM dcf_loan.t_account_posting_detail t2
oracle not exist
07-28
在Oracle中，NOT EXISTS是一种用于查询的条件语句。它用于检查子查询的结果集是否为空，如果为空，则返回True，否则返回False。\[1\] NOT EXISTS的执行方式是，首先执行子查询，并将获取的结果存在一个有索引的临时表中。在执行子查询之前，主查询处于挂起状态，直到子查询执行完毕并将结果存放在临时表中后，主查询才会执行。\[2\] NOT EXISTS通常用于在查询中排除满足某个条件的记录。例如，如果要查询所有部门信息中平均工资不小于1500的部门，可以使用NOT EXISTS语句。\[3\] #### 引用[.reference_title] - *1* *3* [oracle中的exists 和not exists 用法详解](https://blog.csdn.net/a200822146085/article/details/120843893)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insert_down1,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* [Oracle exists与not exists的用法总结](https://blog.csdn.net/Weixiaohuai/article/details/84659482)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insert_down1,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值