Oracle not exist子查詢全掃的優化
作者:lōττéry©版權所有[文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任.]
前言:
本次介紹 子查詢與外層是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>
【源於本人筆記】 若有書寫錯誤,表達錯誤,請指正...
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28602568/viewspace-2123386/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORACLE全表掃描查詢Oracle
- exist-in和關聯子查詢-非關聯子查詢
- MySQL子查詢的優化薦MySql優化
- oracle優化:避免全表掃描Oracle優化
- Oracle in 查詢優化Oracle優化
- exists與in子查詢優化優化
- 優化Oracle with全表掃描的問題優化Oracle
- 查詢全表掃描的sqlSQL
- 優化Oracle with全表掃描的問題(二)優化Oracle
- 優化擁有謂詞or的子查詢優化
- mysql子查詢的缺陷以及5.6的優化MySql優化
- 優化全表掃描優化
- 查詢全表掃描語句
- 資料庫全表查詢之-分頁查詢優化資料庫優化
- 涉及子查詢sql的一次優化SQL優化
- Mysql優化系列之——優化器對子查詢的處理MySql優化
- oracle子查詢Oracle
- 一個NOT EXISTS含有OR條件子查詢的優化優化
- oracle sql tuning 8--優化全表掃描OracleSQL優化
- Oracle with重用子查詢Oracle
- oracle with 子查詢用法Oracle
- 一文終結SQL 子查詢優化SQL優化
- 一次內鏈子查詢優化 2優化
- 一次內鏈子查詢優化 1優化
- PostgreSQL 原始碼解讀(29)- 查詢語句#14(查詢優化-上拉子查詢)SQL原始碼優化
- 查詢優化優化
- 【oracle 效能優化】組合索引查詢。Oracle優化索引
- oracle效能優化(二)-調整查詢Oracle優化
- Oracle臨時表 優化查詢速度Oracle優化
- MySQL 的查詢優化MySql優化
- Oracle查詢優化器的相關引數Oracle優化
- MySQL 優化五(關聯查詢子查詢以及 in 的效率問題)(高階篇)MySql優化
- 優化-mysql子查詢索引失效問題解決優化MySql索引
- SQL 查詢 exist join in 的用法和相應的適用場景 (最佳化查詢)SQL
- pgsql查詢優化之模糊查詢SQL優化
- 子查詢-表子查詢
- MySQL查詢優化MySql優化
- join 查詢優化優化