oracle优化之改写exists降低逻辑读

来源：这里教程网时间：2026-03-03 11:50:56 作者：

通过将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

执行计划：

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;