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
-------------------------------------------------------------------------------------------------
| 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降低逻辑读”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注亿速云行业资讯频道！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
