Oracle exists子查询出现rownum引起性能问题的优化

生产环境中有一支SQL消耗cpu资源很大，逻辑读为299361，相关问题描述:Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "7ptu1y0d95s2r" was executed 3209 times and
had an average elapsed time of 20 seconds.
原SQL:
update WWH
set WWH.STATUS = （case
when （select count（1）
from WWD
where.........................and exists （select 1
from WWD
where 1 = 1
and WWH.WH = WWD.WH
and WWH.WAVE = WWD.WAVE
and WWD.SO in （"2349212263452" ）
and rownum = 1）
set line 1000
set pagesize 1000
set timing on
set autotrace traceonly
执行SQL.........查看执行计划如下：Execution Plan
----------------------------------------------------------
Plan hash value: 3044987130--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
--------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 45 | 191K （1）| 00:38:18 |
| 1 | UPDATE | WWH | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | WWH | 98070 | 4309K| 449 （1）| 00:00:06 |
|* 4 | COUNT STOPKEY | | | | | |
|* 5 | INDEX UNIQUE SCAN | UNQ_WWD | 1 | 40 | 2 （0）| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 24 | | |
|* 7 | COUNT STOPKEY | | | | | |
|* 8 | FILTER | | | | | |
|* 9 | INDEX RANGE SCAN | UNQ_WWD| 1 | 24 | 3 （0）| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | 27 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID| WWD | 1 | 27 | 4 （0）| 00:00:01 |
|* 12 | INDEX RANGE SCAN | UNQ_WWD| 1 | | 3 （0）| 00:00:01 |
--------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
---------------------------------------------------2 - filter（ EXISTS （SELECT 0 FROM "WWD" WHERE ROWNUM=1 AND
"WWD"."WH"=:B1 AND "WWD"."SO"="2349212263452" AND "WWD"."WAVE"=:B2））
-.........................................................................................Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
263080 consistent gets
0 physical reads
72 redo size
829 bytes sent via SQL*Net to client
3583 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts （memory）
0 sorts （disk）
0 rows processed
上面显示表没走索引，逻辑读为263080，查看表的字段（WAVE,WH）存在索引，我们再建一个选择性更好的索引：
增加索引：
create index IND_MODIFY_TIME_IX on WWH（Modify_Time,WAVE,WH） tablespace wsx;再查看执行计划依然没有走索引，逻辑读同样是263080大。

强制加入索引后，虽然执行计划中有显示走了全索引扫描，且驱动表由WWH表变成了WWD表，但逻辑读变得更大为299361，走索引前后性能都差的最主要原因是执行计划中看到有
"filter（ EXISTS..........."这行，表示子查询没有展开。
SQL> update /*+index（WWH IND_MODIFY_TIME_IX） */ WWH ................Elapsed: 00:00:01.11Execution Plan
----------------------------------------------------------
Plan hash value: 389457693----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
----------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 45 | 196K （1）| 00:39:16 |
| 1 | UPDATE | WWH | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | WWD | 4903 | 215K| 5302 （1）| 00:01:04 |
|* 3 | INDEX FULL SCAN | IND_MODIFY_TIME_IX | 4996 | | 602 （1）| 00:00:08 |
|* 4 | COUNT STOPKEY | | | | | |
|* 5 | INDEX UNIQUE SCAN | UNQ_WWD | 1 | 40 | 2 （0）| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 24 | | |
|* 7 | COUNT STOPKEY | | | | | |
|* 8 | FILTER | | | | | |
|* 9 | INDEX RANGE SCAN | UNQ_WWD | 1 | 24 | 3 （0）| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | 27 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID| WWD | 1 | 27 | 4 （0）| 00:00:01 |
|* 12 | INDEX RANGE SCAN | UNQ_WWD | 1 | | 3 （0）| 00:00:01 |
----------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
---------------------------------------------------2 - filter（ EXISTS （SELECT 0 FROM "WWD" WHERE ROWNUM=1 AND
"WWD"."WH"=:B1 AND "WWD"."SO"="2349212263452" AND "WWD"."WAVE"=:B2））
-.........................................................................................
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
299361 consistent gets
2 physical reads
0 redo size
840 bytes sent via SQL*Net to client
3619 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts （memory）
0 sorts （disk）
0 rows processed手工加入/*+unnest*/让子查询展开，但从执行计划查看，依然没有展开。exists无法展开子查询，我们将SQL等价改成in子查询，如下：SQL略 Execution Plan
----------------------------------------------------------
Plan hash value: 1922347980--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
--------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 81 | 616 （1）| 00:00:08 |
| 1 | UPDATE | WWH | | | | |
| 2 | NESTED LOOPS | | 1 | 81 | 607 （1）| 00:00:08 |
| 3 | NESTED LOOPS | | 1 | 81 | 607 （1）| 00:00:08 |
| 4 | VIEW | VW_NSO_1 | 1 | 36 | 4 （0）| 00:00:01 |
| 5 | SORT UNIQUE | | 1 | 40 | | |
|* 6 | COUNT STOPKEY | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| WWD | 1 | 40 | 4 （0）| 00:00:01 |
|* 8 | INDEX RANGE SCAN | UNQ_WWD | 1 | | 3 （0）| 00:00:01 |
|* 9 | INDEX FULL SCAN | IND_MODIFY_TIME_IX | 1 | | 601 （1）| 00:00:08 |
|* 10 | TABLE ACCESS BY INDEX ROWID | WWH | 1 | 45 | 602 （1）| 00:00:08 |
| 11 | SORT AGGREGATE | | 1 | 24 | | |
|* 12 | COUNT STOPKEY | | | | | |
|* 13 | FILTER | | | | | |
|* 14 | INDEX RANGE SCAN | UNQ_WWD | 1 | 24 | 3 （0）| 00:00:01 |
| 15 | SORT AGGREGATE | | 1 | 27 | | |
|* 16 | TABLE ACCESS BY INDEX ROWID | WWD | 1 | 27 | 4 （0）| 00:00:01 |
|* 17 | INDEX RANGE SCAN | UNQ_WWD | 1 | | 3 （0）| 00:00:01 |
--------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
---------------------------------------------------此部分看不到exists和filter信息了，说明有展开
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
2 physical reads
0 redo size
842 bytes sent via SQL*Net to client
3546 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts （memory）
0 sorts （disk）
0 rows processed
发现上面已走索引了，且逻辑读降为3。以上是在测试环境中模拟测试，因数据与正式环境有差异，我们再看下正式环境中查询操作性能比较：

select count（*） from WWH
where exists （select 1
from WWD
where 1 = 1
and WWH.WH= WWD.WH
and WWH.WAVE= WWD.WAVE
and WWD.SO in （"SO201612345" ）
and rownum = 1
）
and WWH.STATUS <> "11"
and WWH.STATUS <> "22"

---逻辑读625043，执行时间为1秒改成in子查询后：

SELECT COUNT （ * ）
FROM WWH
WHERE （wwh.WH, wwh.WAVE） IN
（SELECT WWD.WH, WWD.WAVE
FROM WWD
WHERE 1 = 1
AND WWD.SO_NO IN （"SO201612345"）
AND ROWNUM = 1
）
AND WWH.STATUS <> "11"and WWH.STATUS <> "22"

---逻辑读变为8，执行时间58豪秒。也可直接去掉rownum=1。
总结：当exists子查询中出现有rownum时，子查询无法展开，可以改成in子查询或表联接，或根据实际逻辑需求，去掉rownum。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址