Oracle not exist子查询全扫的优化
链接：http://blog.itpub.net/28602568/viewspace-2123386/
标题： Oracle not exist子查询全扫的优化
作者：ｌōττéｒｙ©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
前言：
之前写过 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>
【源于本人笔记】 若有书写错误，表达错误，请指正...
正文到此结束
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
