溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹oracle中如何改寫exists降低邏輯讀，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！
通過將exists改寫成in或這inner join優化sql。
Sql_id：056bs9dzz8mwy
問題簡述：邏輯讀高。
Sql文本：
SELECT A.*, a.rowid FROM WBANK.WD_BANK_BASEINFOMATION A WHERE EXISTS (SELECT 1 FROM (select KEYWORD, TYPECODE, INNERCODE, COUNT(*) FROM WBANK.WD_BANK_BASEINFOMATION WHERE SUBSTR(TYPECODE, 1, 3) = '001' GROUP BY KEYWORD, TYPECODE, INNERCODE HAVING COUNT(*) <> 1) B WHERE A.KEYWORD = B.KEYWORD AND A.TYPECODE = B.TYPECODE AND A.INNERCODE = B.INNERCODE); |
執行計劃：
Execution Plan ---------------------------------------------------------- Plan hash value: 1318914978
------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 130 | 7930K (1)| 39:39:10 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | WD_BANK_BASEINFOMATION | 2640K| 327M| 6249 (2)| 00:01:53 | |* 3 | FILTER | | | | | | | 4 | SORT GROUP BY NOSORT| | 1 | 47 | 3 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | IDX_WD_B_BI | 1 | 47 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "WBANK"."WD_BANK_BASEINFOMATION" "WD_BANK_BASEINFOMATION" WHERE "TYPECODE"=:B1 AND "KEYWORD"=:B2 AND "INNERCODE"=:B3 AND SUBSTR("TYPECODE",1,3)='001' GROUP BY "KEYWORD","TYPECODE","INNERCODE" HAVING COUNT(*)<>1)) 3 - filter(COUNT(*)<>1) 5 - access("KEYWORD"=:B1 AND "TYPECODE"=:B2 AND "INNERCODE"=:B3) filter("INNERCODE"=:B1 AND SUBSTR("TYPECODE",1,3)='001')
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 2329554 consistent gets 13 physical reads 0 redo size 2507 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 |
可以發現邏輯讀高達200多萬。
剛看到這個sql的時候猜想會不會邏輯有問題，導致結果集為空。跑了一遍發現結果集確實為空。子查詢的innercode列全部為null。根據條件A.INNERCODE = B.INNERCODE外部表（雖然是同一張表）是不會有匹配結果的。轉念一想如果子查詢innercode列有非空的，那就不會有問題了。當然了還是要詢問開發結果集與該列為空是否有必然聯系，如果有聯系的話可以利用該邏輯關系改寫sql。當然，這是后話了。
看一下數據分布：
SQL> select count(*) from WBANK.WD_BANK_BASEINFOMATION;
COUNT(*) ---------- 2645546
SQL> select count(*) from (select KEYWORD, TYPECODE, INNERCODE, COUNT(*) 2 FROM WBANK.WD_BANK_BASEINFOMATION 3 WHERE SUBSTR(TYPECODE, 1, 3) = '001' 4 GROUP BY KEYWORD, TYPECODE, INNERCODE 5 HAVING COUNT(*) <> 1);
COUNT(*) ---------- 128 |
外層結果集是全表數據260多萬。子查詢結果集只有128條。而根據oracle對exists的處理，會以外部結果集為驅動，也就是說要執行260多萬次，這顯然是不合理的。如果外部結果集大，內部結果集小的話，這種情況下通常是要用in，以內部結果集為驅動，這樣也就執行128次。
驗證一下執行次數的問題：
SQL> alter session set statistics_level=all; SQL> SELECT A.*, a.rowid 2 FROM WBANK.WD_BANK_BASEINFOMATION A 3 WHERE EXISTS (SELECT 1 4 FROM (select KEYWORD, TYPECODE, INNERCODE, COUNT(*) 5 FROM WBANK.WD_BANK_BASEINFOMATION 6 WHERE SUBSTR(TYPECODE, 1, 3) = '001' 7 GROUP BY KEYWORD, TYPECODE, INNERCODE 8 HAVING COUNT(*) <> 1) B 9 WHERE A.KEYWORD = B.KEYWORD 10 AND A.TYPECODE = B.TYPECODE 11 AND A.INNERCODE = B.INNERCODE); no rows selected
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
Plan hash value: 1318914978
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:09.75 | 2329K| |* 1 | FILTER | | 1 | | 0 |00:00:09.75 | 2329K| | 2 | TABLE ACCESS FULL | WD_BANK_BASEINFOMATION | 1 | 2640K| 2645K|00:00:00.61 | 12226 | |* 3 | FILTER | | 2632K| | 0 |00:00:07.38 | 2317K| | 4 | SORT GROUP BY NOSORT| | 2632K| 1 | 1273K|00:00:06.64 | 2317K| |* 5 | INDEX RANGE SCAN | IDX_WD_B_BI | 2632K| 1 | 1273K|00:00:03.42 | 2317K| -----------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter( IS NOT NULL) 3 - filter(COUNT(*)<>1) 5 - access("KEYWORD"=:B1 AND "TYPECODE"=:B2 AND "INNERCODE"=:B3) filter(("INNERCODE"=:B1 AND SUBSTR("TYPECODE",1,3)='001'))
31 rows selected. |
可以看到starts列部分，內部子查詢2632k次，與外表數據量吻合。
用in改寫sql
SELECT A.*, a.rowid FROM WBANK.WD_BANK_BASEINFOMATION A WHERE (A.KEYWORD,A.TYPECODE,A.INNERCODE) in (SELECT B.KEYWORD,B.TYPECODE,B.INNERCODE FROM (select KEYWORD, TYPECODE, INNERCODE, COUNT(*) FROM WBANK.WD_BANK_BASEINFOMATION WHERE SUBSTR(TYPECODE, 1, 3) = '001' GROUP BY KEYWORD, TYPECODE, INNERCODE HAVING COUNT(*) <> 1) B ); |
執行計劃：
Set autotrace on 執行sql。
得到執行計劃： Execution Plan ---------------------------------------------------------- Plan hash value: 1385212545
------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 7008 | 6236 (2)| 00:01:53 | | 1 | NESTED LOOPS | | 3 | 7008 | 6236 (2)| 00:01:53 | | 2 | NESTED LOOPS | | 3 | 7008 | 6236 (2)| 00:01:53 | | 3 | VIEW | VW_NSO_1 | 55 | 118K| 6228 (2)| 00:01:53 | |* 4 | FILTER | | | | | | | 5 | HASH GROUP BY | | 1 | 2585 | 6228 (2)| 00:01:53 | |* 6 | TABLE ACCESS FULL | WD_BANK_BASEINFOMATION | 26410 | 1212K| 6226 (2)| 00:01:53 | |* 7 | INDEX RANGE SCAN | IDX_WD_B_BI | 1 | | 2 (0)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID| WD_BANK_BASEINFOMATION | 1 | 130 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
4 - filter(COUNT(*)<>1) 6 - filter(SUBSTR("TYPECODE",1,3)='001') 7 - access("A"."KEYWORD"="KEYWORD" AND "A"."TYPECODE"="TYPECODE" AND "A"."INNERCODE"="INNERCODE") filter("A"."INNERCODE" IS NOT NULL AND "A"."INNERCODE"="INNERCODE")
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 12226 consistent gets 0 physical reads 0 redo size 2507 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 |
執行計劃已經變成以內部子查詢為驅動表了。而且邏輯讀從200萬降低1萬。
下面再來驗證執行次數：
SQL> alter session set statistics_level=all; 執行sql SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 得到執行計劃（部分）： ------------------------------------------------------------------------------------------------------------------- -- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | | ------------------------------------------------------------------------------------------------------------------- -- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:02.28 | 12226 | | | |* 1 | HASH JOIN RIGHT SEMI | | 1 | 1311K| 0 |00:00:02.28 | 12226 | 391K| )| | 2 | VIEW | VW_NSO_1 | 1 | 80389 | 128 |00:00:02.28 | 12226 | | | |* 3 | FILTER | | 1 | | 128 |00:00:02.28 | 12226 | | | | 4 | HASH GROUP BY | | 1 | 4020 | 1607K|00:00:02.17 | 12226 | 710M| )| |* 5 | TABLE ACCESS FULL| WD_BANK_BASEINFOMATION | 1 | 1607K| 1607K|00:00:00.78 | 12226 | | | |* 6 | TABLE ACCESS FULL | WD_BANK_BASEINFOMATION | 0 | 1311K| 0 |00:00:00.01 | 0 | | | |
發現執行計劃并不一致，這個才是真正的執行計劃。
Predicate Information (identified by operation id): ---------------------------------------------------
1 - access("A"."KEYWORD"="KEYWORD" AND "A"."TYPECODE"="TYPECODE" AND "A"."INNERCODE"="INNERCODE") 3 - filter(COUNT(*)<>1) 5 - filter(SUBSTR("TYPECODE",1,3)='001') 6 - filter("A"."INNERCODE" IS NOT NULL)
Note
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----- - cardinality feedback used for this statement |
后面發現了基數反饋的東西。估計值是實際值差別還是很大的。說明統計信息是有問題的。
查看統計信息已經是4月份收集的了。
收集統計信息
SQL> exec dbms_stats.gather_table_stats(ownname => 'WBANK',tabname => 'WD_BANK_BASEINFOMATION',estimate_percent => 10,method_opt=> 'for all columns size repeat',no_invalidate=>false);
PL/SQL procedure successfully completed. |
收集完統計信息后的執行計劃
Plan hash value: 1385212545
-------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 2 |00:00:02.55 | 12232 | | | | | 1 | NESTED LOOPS | | 1 | 3 | 2 |00:00:02.55 | 12232 | | | | | 2 | NESTED LOOPS | | 1 | 3 | 2 |00:00:02.55 | 12230 | | | | | 3 | VIEW | VW_NSO_1 | 1 | 53 | 129 |00:00:02.55 | 12226 | | | | |* 4 | FILTER | | 1 | | 129 |00:00:02.55 | 12226 | | | | | 5 | HASH GROUP BY | | 1 | 1 | 1607K|00:00:02.40 | 12226 | 710M| 17M| 170M (0)| |* 6 | TABLE ACCESS FULL | WD_BANK_BASEINFOMATION | 1 | 26458 | 1607K|00:00:00.80 | 12226 | | | | |* 7 | INDEX RANGE SCAN | IDX_WD_B_BI | 129 | 1 | 2 |00:00:00.01 | 4 | | | | | 8 | TABLE ACCESS BY INDEX ROWID| WD_BANK_BASEINFOMATION | 2 | 1 | 2 |00:00:00.01 | 2 | | | | --------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
4 - filter(COUNT(*)<>1) 6 - filter(SUBSTR("TYPECODE",1,3)='001') 7 - access("A"."KEYWORD"="KEYWORD" AND "A"."TYPECODE"="TYPECODE" AND "A"."INNERCODE"="INNERCODE") filter(("A"."INNERCODE" IS NOT NULL AND "A"."INNERCODE"="INNERCODE"))
34 rows selected. |
可以看到確實是129次。而且也不存在基數反饋導致執行計劃改變了。邏輯讀還是在1萬多。
突然想到還可以使用inner join的方法來改寫sql
SELECT A.*, a.rowid FROM WBANK.WD_BANK_BASEINFOMATION A inner join (select KEYWORD, TYPECODE, INNERCODE, COUNT(*) FROM WBANK.WD_BANK_BASEINFOMATION WHERE SUBSTR(TYPECODE, 1, 3) = '001' GROUP BY KEYWORD, TYPECODE, INNERCODE HAVING COUNT(*) <> 1) B on A.KEYWORD = B.KEYWORD AND A.TYPECODE = B.TYPECODE AND A.INNERCODE = B.INNERCODE; |
執行計劃：
Plan hash value: 4254729379
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 2 |00:00:02.48 | 12232 | | | | | 1 | NESTED LOOPS | | 1 | 59 | 2 |00:00:02.48 | 12232 | | | | | 2 | NESTED LOOPS | | 1 | 59 | 2 |00:00:02.48 | 12230 | | | | | 3 | VIEW | | 1 | 59 | 129 |00:00:02.48 | 12226 | | | | |* 4 | FILTER | | 1 | | 129 |00:00:02.48 | 12226 | | | | | 5 | HASH GROUP BY | | 1 | 59 | 1607K|00:00:02.31 | 12226 | 710M| 17M| 168M (0)| |* 6 | TABLE ACCESS FULL | WD_BANK_BASEINFOMATION | 1 | 26466 | 1607K|00:00:00.76 | 12226 | | | |
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |* 7 | INDEX RANGE SCAN | IDX_WD_B_BI | 129 | 1 | 2 |00:00:00.01 | 4 | | | | | 8 | TABLE ACCESS BY INDEX ROWID| WD_BANK_BASEINFOMATION | 2 | 1 | 2 |00:00:00.01 | 2 | | | | --------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
4 - filter(COUNT(*)<>1) 6 - filter(SUBSTR("TYPECODE",1,3)='001') 7 - access("A"."KEYWORD"="B"."KEYWORD" AND "A"."TYPECODE"="B"."TYPECODE" AND "A"."INNERCODE"="B"."INNERCODE") filter(("A"."INNERCODE" IS NOT NULL AND "A"."INNERCODE"="B"."INNERCODE"))
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
34 rows selected. |
邏輯讀
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 12232 consistent gets 0 physical reads 0 redo size 3083 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed |
邏輯讀也是1萬多。
看一下執行計劃發現，瓶頸都在對表的全表掃且過濾條件filter(SUBSTR("TYPECODE",1,3)='001')。
可以考慮在這列上建函數索引，
SQL> select count(*) from wbank.WD_BANK_BASEINFOMATION WHERE SUBSTR(TYPECODE, 1, 3) = '001';
COUNT(*) ---------- 1607674 表的數據一共只有2645546，返回1607674，所以建了索引也沒用，所以不用建索引了。 |
綜上所述。優化建議是更改sql，將exists改成in或者inner join：
SELECT A.*, a.rowid FROM WBANK.WD_BANK_BASEINFOMATION A WHERE (A.KEYWORD,A.TYPECODE,A.INNERCODE) in (SELECT B.KEYWORD,B.TYPECODE,B.INNERCODE FROM (select KEYWORD, TYPECODE, INNERCODE, COUNT(*) FROM WBANK.WD_BANK_BASEINFOMATION WHERE SUBSTR(TYPECODE, 1, 3) = '001' GROUP BY KEYWORD, TYPECODE, INNERCODE HAVING COUNT(*) <> 1) B );
或者 SELECT A.*, a.rowid FROM WBANK.WD_BANK_BASEINFOMATION A inner join (select KEYWORD, TYPECODE, INNERCODE, COUNT(*) FROM WBANK.WD_BANK_BASEINFOMATION WHERE SUBSTR(TYPECODE, 1, 3) = '001' GROUP BY KEYWORD, TYPECODE, INNERCODE HAVING COUNT(*) <> 1) B on A.KEYWORD = B.KEYWORD AND A.TYPECODE = B.TYPECODE AND A.INNERCODE = B.INNERCODE;
|
邏輯讀將從200多萬將至1萬多。
以上是“oracle中如何改寫exists降低邏輯讀”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注億速云行業資訊頻道！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
