oracle中如何改写exists降低逻辑读

这篇文章主要介绍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

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

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

| 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

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

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

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

| 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

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

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

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

得到执行计划（部分）：

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

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

| 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

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

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

| 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

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

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

| 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降低逻辑读”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注创新互联行业资讯频道！

网站栏目：oracle中如何改写exists降低逻辑读
网址分享：http://chengdu.cdxwcx.cn/article/jhospe.html

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

首页

网站建设

网站制作案例

解决方案

网站设计报价

网站制作动态

关于我们

联系我们

成都网站建设设计将想法与焦点和您一起共享

oracle中如何改写exists降低逻辑读

其他资讯

linux命令输入不显示 linux命令行输入

sap外围系统有哪些的简单介绍

c语言用函数画菱形 c语言写出菱形代码

wordpress被封 wordpressguan网被攻击

gis技术的应用ppt gis技术的应用范围

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

成都网站建设设计 将想法与焦点和您一起共享

oracle中如何改写exists降低逻辑读

其他资讯

linux命令输入不显示 linux命令行输入

sap外围系统有哪些的简单介绍

c语言用函数画菱形 c语言写出菱形代码

wordpress被封 wordpressguan网 被攻击

gis技术的应用ppt gis技术的应用范围

成都网站建设设计将想法与焦点和您一起共享

wordpress被封 wordpressguan网被攻击