排序的迷惑

排序的迷惑，请教！

create table cust
( cust_id number(10),
cust_name varchar2(50)
)
;
insert into cust values(1 ,'aaa');
insert into cust values(2 ,'bbb');
insert into cust values(3 ,'ccc');
insert into cust values(4 ,'ddd');
insert into cust values(5 ,'eee');

====
SQL> select * from (
2 SELECT cust_name,MAX(cust_id) OVER ( order by cust_id desc ) cc
3 FROM cust) where rownum=1
4 /

CUST_NAME CC
------------------ ----------
eee 5

SQL> select cust_name from (
2 SELECT cust_name,MAX(cust_id) OVER ( order by cust_id desc ) cc
3 FROM cust) where rownum=1
4 /

CUST_NAME
------------------
aaa

迷惑，为什么结果不一样？

[@more@]

SQL> set autotrace on
SQL> select * from (
2 SELECT cust_name,MAX(cust_id) OVER ( order by cust_id desc ) cc
3 FROM cust)
4 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 WINDOW (SORT)
3 2 TABLE ACCESS (FULL) OF 'CUST'

SQL> select cust_name from (
2 SELECT cust_name,MAX(cust_id) OVER ( order by cust_id desc ) cc
3 FROM cust)
4 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 TABLE ACCESS (FULL) OF 'CUST'

原因自然是由于执行计划的不同，但是为什么会导致执行计划的不同，我只能认为，当你没有访问那个要排序的列的时候，oracle很聪明的识别出来了，然后内部做处理了，导致数据没有按照你想象的去排序。

其实用：

SQL> select cust_name from (
2 SELECT cust_name,cust_id
3 FROM cust order by cust_id desc)
4 /

CUST_NAME
--------------------------------------------------
eee
ddd
ccc
bbb
aaa

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 SORT (ORDER BY)
3 2 TABLE ACCESS (FULL) OF 'CUST'

就可以了，为啥要那么复杂呢。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/133835/viewspace-929885/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/133835/viewspace-929885/