oracle中如何改寫(xiě)exists降低邏輯讀

發(fā)布時(shí)間：2021-11-10 14:05:25 來(lái)源：億速云閱讀：130 作者：小新欄目：關(guān)系型數(shù)據(jù)庫(kù)

這篇文章主要介紹oracle中如何改寫(xiě)exists降低邏輯讀，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

通過(guò)將exists改寫(xiě)成in或這inner join優(yōu)化sql。

Sql_id：056bs9dzz8mwy

問(wèn)題簡(jiǎn)述：邏輯讀高。

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);

執(zhí)行計(jì)劃：

Execution Plan

----------------------------------------------------------

Plan hash value: 1318914978

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

| 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

可以發(fā)現(xiàn)邏輯讀高達(dá)200多萬(wàn)。

剛看到這個(gè)sql的時(shí)候猜想會(huì)不會(huì)邏輯有問(wèn)題，導(dǎo)致結(jié)果集為空。跑了一遍發(fā)現(xiàn)結(jié)果集確實(shí)為空。子查詢(xún)的innercode列全部為null。根據(jù)條件A.INNERCODE = B.INNERCODE外部表（雖然是同一張表）是不會(huì)有匹配結(jié)果的。轉(zhuǎn)念一想如果子查詢(xún)innercode列有非空的，那就不會(huì)有問(wèn)題了。當(dāng)然了還是要詢(xún)問(wèn)開(kāi)發(fā)結(jié)果集與該列為空是否有必然聯(lián)系，如果有聯(lián)系的話(huà)可以利用該邏輯關(guān)系改寫(xiě)sql。當(dāng)然，這是后話(huà)了。

看一下數(shù)據(jù)分布：

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

外層結(jié)果集是全表數(shù)據(jù)260多萬(wàn)。子查詢(xún)結(jié)果集只有128條。而根據(jù)oracle對(duì)exists的處理，會(huì)以外部結(jié)果集為驅(qū)動(dòng)，也就是說(shuō)要執(zhí)行260多萬(wàn)次，這顯然是不合理的。如果外部結(jié)果集大，內(nèi)部結(jié)果集小的話(huà)，這種情況下通常是要用in，以?xún)?nèi)部結(jié)果集為驅(qū)動(dòng)，這樣也就執(zhí)行128次。

驗(yàn)證一下執(zhí)行次數(shù)的問(wèn)題：

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

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------

| 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列部分，內(nèi)部子查詢(xún)2632k次，與外表數(shù)據(jù)量吻合。

用in改寫(xiě)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

);

執(zhí)行計(jì)劃：

Set autotrace on

執(zhí)行sql。

得到執(zhí)行計(jì)劃：

Execution Plan

----------------------------------------------------------

Plan hash value: 1385212545

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 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

執(zhí)行計(jì)劃已經(jīng)變成以?xún)?nèi)部子查詢(xún)?yōu)轵?qū)動(dòng)表了。而且邏輯讀從200萬(wàn)降低1萬(wàn)。

下面再來(lái)驗(yàn)證執(zhí)行次數(shù)：

SQL> alter session set statistics_level=all;

執(zhí)行sql

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

得到執(zhí)行計(jì)劃（部分）：

-------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------

| 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 | |

發(fā)現(xiàn)執(zhí)行計(jì)劃并不一致，這個(gè)才是真正的執(zhí)行計(jì)劃。

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

后面發(fā)現(xiàn)了基數(shù)反饋的東西。估計(jì)值是實(shí)際值差別還是很大的。說(shuō)明統(tǒng)計(jì)信息是有問(wèn)題的。

查看統(tǒng)計(jì)信息已經(jīng)是4月份收集的了。

收集統(tǒng)計(jì)信息

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.

收集完統(tǒng)計(jì)信息后的執(zhí)行計(jì)劃

Plan hash value: 1385212545

--------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------

| 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.

可以看到確實(shí)是129次。而且也不存在基數(shù)反饋導(dǎo)致執(zhí)行計(jì)劃改變了。邏輯讀還是在1萬(wàn)多。

突然想到還可以使用inner join的方法來(lái)改寫(xiě)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;

執(zhí)行計(jì)劃：

Plan hash value: 4254729379

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------

| 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萬(wàn)多。

看一下執(zhí)行計(jì)劃發(fā)現(xiàn)，瓶頸都在對(duì)表的全表掃且過(guò)濾條件filter(SUBSTR("TYPECODE",1,3)='001')。

可以考慮在這列上建函數(shù)索引，

SQL> select count(*) from wbank.WD_BANK_BASEINFOMATION WHERE SUBSTR(TYPECODE, 1, 3) = '001';

COUNT(*)

----------

1607674

表的數(shù)據(jù)一共只有2645546，返回1607674，所以建了索引也沒(méi)用，所以不用建索引了。

綜上所述。優(yōu)化建議是更改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多萬(wàn)將至1萬(wàn)多。

以上是“oracle中如何改寫(xiě)exists降低邏輯讀”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！

向AI問(wèn)一下細(xì)節(jié)

oracle中如何改寫(xiě)exists降低邏輯讀

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽