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。