10.2.0.5 on windows 2008
以下sql,
select
count(av_sal)
from (
select
outer.dept_no,
outer.sal,
outer.emp_no,
outer.padding,
(
selectavg(inner.sal)
fromempinner
whereinner.dept_no = outer.dept_no
) av_sal
fromempouter
)
where
sal > av_sal
;
执行计划如附件，我的理解：
ID 5->ID 4，对于ID4返回的结果集里的每一行执行一次ID 1（这里由于distinct value，所以ID 1只执行了3次）
av_sal这个值由执行了3次ID 1得到的，那ID4先执行了（且要根据av_sal做一个过滤动作），ID 4 为何只执行了一次呢？
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buf
fers |
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.65 |
4641 |
| 1 | SORT AGGREGATE | | 6 | 1 | 6 |00:00:00.27 |
3978 |
|* 2 | TABLE ACCESS FULL | EMP | 6 | 3333 | 20000 |00:00:00.18 |
3978 |
| 3 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.65 |
4641 |
|* 4 | VIEW | | 1 | 20000 | 9998 |00:00:00.58 |
4641 |
| 5 | TABLE ACCESS FULL| EMP | 1 | 20000 | 20000 |00:00:00.12 |
663 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------
---------------------------------------------------
2 - filter("INNER"."DEPT_NO"=:B1)
4 - filter("SAL">"AV_SAL")