在执行如下的查询时，oracle往往会选择进行查询重写：
sys@TEST> select
  2           outer.*
  3   from emp outer
  4   where outer.sal >
  5           (select
  6                   avg(inner.sal)
  7           from    emp inner
  8           where   inner.dept_no = outer.dept_no
  9   );

9998 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=101 Card=1000 Bytes=
          98000)

   1    0   HASH JOIN (Cost=101 Card=1000 Bytes=98000)
   2    1     VIEW OF 'VW_SQ_1' (Cost=65 Card=6 Bytes=156)
   3    2       SORT (GROUP BY) (Cost=65 Card=6 Bytes=48)
   4    3         TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=20000 Byt
          es=160000)

   5    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=20000 Bytes=1
          440000)

实际上，优化器对该sql进行了重写，相当于采用了一个视图，用该视图按照dept_no来统计出各部门的avg(sal),然后将此视图的执行结果与emp表进行hash join，这样就避免了filter操作，减少了过多不必要的扫描，可以手动来创建一个视图来演示优化器的查询重写
 
 
sys@TEST>create view v_emp as select dept_no,avg(sal) avg_sal from emp group by dept_no;

View created.

sys@TEST> select outer.* from emp outer,v_emp where outer.dept_no=v_emp.dept_no and outer.sal>v_emp.avg_sal;

9998 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=101 Card=1000 Bytes=
          98000)

   1    0   HASH JOIN (Cost=101 Card=1000 Bytes=98000)
   2    1     VIEW OF 'V_EMP' (Cost=65 Card=6 Bytes=156)
   3    2       SORT (GROUP BY) (Cost=65 Card=6 Bytes=48)
   4    3         TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=20000 Byt
          es=160000)

   5    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=20000 Bytes=1
          440000)

如果强制优化器不进行查询转换，则执行效率就降低很多：

sys@TEST>select
  2          outer.*
  3  from emp outer
  4  where outer.sal >
  5          (select /*+ no_unnest */ avg(inner.sal)
  6          from emp inner
  7          where inner.dept_no = outer.dept_no
  8  );

9998 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=35035 Card=1000 Byte
          s=72000)

   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=1000 Bytes=72
          000)

   3    1     SORT (AGGREGATE)
   4    3       TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=3333 Bytes=
          26664)