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
迷惑,为什么结果不一样?
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/