根据标题为“一道基础题，考考大家”进行SQL语句的执行计划的具体分析！

jboracle1981

学习

bell6248

原帖由 dingjun123 于 2010-10-20 12:02 发表
tom说，要想必须必须显示order by,相信排序只是order by显示指定，newkid说优化器很聪敏，如果重复排序了，优化器会只排序一次
--测试，就一个分析函数，里面已经排序了，后面显示order by ename因为和分析函数的排序一致，oracle忽略，但是要排序，还是建议写一下
--不用order by enmae,执行计划和前面的还是一致，这就是优化器的魅力
--PS:相信老TOM,相信newkid,自己测试，没错的
DINGJUN123>select emp.*,row_number() over(order by ename) rn
  2  from scott.emp
  3  order by ename;

已选择12行。


执行计划
----------------------------------------------------------
Plan hash value: 3145491563

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    12 |   444 |     3   (0)| 00:00:01 |
|   1 |  WINDOW SORT       |      |    12 |   444 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    12 |   444 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

关于“如果重复排序了，优化器会只排序一次”， 你说的没有错！ 我估计之所以为这样， 我认为对于一条SQL语句， 会对该语句的不同的排序子句进行缓存，
因此即使在一个语句中重复了写了多个相同的“order by子句”, 只会把它们看出一次排序， 这个不管是在语句后进行排序， 还是对于分析函数中的排序， 都是一样的！
就你上面的语句的执行计划中的那个“WINDOW SORT”， 实际上就是指分析函数中的排序“order by ename”， 由于该语句最后还有一个“order by子句”，
但根据语句的执行顺序规则来看， 分析函数先执行， 接着才是SQL语句的order by子句。 因此分析函数中的排序先被缓存在一个特殊的缓存区中， 接着对于SQL语句的order by子句只要发现在缓存区中已经存在并且能确认是完全一样的， 这时， 该order by子句就不会在执行了， 否则， 如上的语句就有2次排序了， 一次是”window sort“, 另一次是“sort order by”了， 如果是这样的话， 那oracle的优化器就超级SB了！

dingjun123

恩，其实这个排序很重要，很多人对这问题还没有理解，所以列出来
如果是order by empno那么window sort完了后海有个sort order by的，优化器的确很聪明

mr_wngk

dingjun123 发表于 2010-10-19 19:30
好帖子，要保留
人肉常规规则，非人肉规则见bell,ORACLE SQL重要操作，所有OPERATION在同一层（非优化器查 ...

精辟

NewDamon

select deptno,
       job,
       count(sum(sal)) over()
  from emp
where sal > 2000
start with mgr is null
connect by prior empno = mgr
group by deptno, job
having sum(sal) > 100
order by deptno
order by 是group by的子集执行计划是sort group by

select deptno,
       job,
       sum(sal),
       count(*) over(order by sum(sal)) cnt
  from emp
where sal > 1500
group by deptno, job
having sum(sal) > 2
order by deptno
这个order by 也是group by 的子集执行计划是 hash group by
是不是说明order by 是group by子集并不是决定是否执行sort group by 的因素

ggfbunengfangqi

有必要看一下

ghsau

顶楼主，学习了，惭愧，我以前还不知道start with...connect by...语句

zwqq01

学习了！

suisui0926

真是博大精深啊、学习中

lhnjchina

好好学习