rownum注意问题

      众所周知，rownum、rowid是oracle的一个伪列，但是rownum和rowid的不同是：rowid是唯一的标识了列的存储位置，rownum是按照用户的插入语句顺序，给一个序列号，rownum永远是从1开始，因此，绝对不能用“rownum等于”、“rownum大于”等的条件，否则返回无结果。（因为当前面条件不满足时，rownum总是从1开始。）

     下面总结下rownum和order by的关系，因为我一直会迷糊，以前还一度认为order by排序后，rownum就会按照我们需要的顺序保存，但是往往不是这样的。

     萌发这个想法也感谢以下这个帖子的五楼作者：http://www.itpub.net/thread-1352089-1-1.html

 

看看下面的测试：

 

create table test
(id number,
 name varchar2(128)
 );

 

--请注意以下的插入顺序
 insert into  test values(3,'dfhg');
 insert into  test values(1,'sag');
 insert into  test values(4,'ashtrhf');
 insert into  test values(2,'lllads');
 insert into  test values(5,'ahd');
 insert into  test values(6,'dfbhfsn');

 commit;

 

--查询结果

select id,name,rownum
 from test
 order by id;
 
 
        ID NAME                               ROWNUM
---------- ------------------------------ ----------
         1 sag                                     2
         2 lllads                                  4
         3 dfhg                                    1
         4 ashtrhf                                 3
         5 ahd                                     5
         6 dfbhfsn                                 6

 

总结：发现其实rownum并没有按照我们要的序列产生。

为什么呢？ 继续看下面的语句

 

select id,name,rownum
from (
 select id,name
 from test
 order by id
 );
 
         ID NAME                               ROWNUM
---------- ------------------------------ ----------
         1 sag                                     1
         2 lllads                                  2
         3 dfhg                                    3
         4 ashtrhf                                 4
         5 ahd                                     5
         6 dfbhfsn                                 6

 

总结：这时候按照我们要的顺序了，原因：rownum是作用在一个结果集上的。

（因此，想要取前几个序列号时，可以利用子查询，在外层限制rownum的取值。当然也可以直接使用row_number分析函数）

 

继续看下面的查询。

我想要得到按一定顺序排序的前三名。

 

SQL如下：

 select id,name from  test where rownum<4 order by id;      
 
         ID NAME
---------- ------------------------------
         1 sag
         3 dfhg
         4 ashtrhf

 

说明：此时oracle先选择，后进行了排序，（很明显，不是我们要的结果）

 

继续

 

select id,name
from (
 select id,name
 from test
 order by id
 )
 where rownum<4;

        ID NAME
---------- ------------------------------
         1 sag
         2 lllads
         3 dfhg

 

总结：利用子查询，在外层限制rownum的取值既可以实现需求。

说明：但是这种方法假设test表很大，会全表先扫描，排好序，再筛选，效率较低。在网上搜了下，但到有人这样说：

“只需在order by字段加上索引或者主键约束，即可让oracle先按照该字段排序，然后再rownum”，我试验了下，好像不行。

连接如下：http://www.360doc.com/content/09/0218/13/6785_2578842.shtml

 

试验过程如下：

1. 加入唯一索引

 create unique index ind_id on  test(id); 

 

2. SQL还是原来的SQL。（结果一样，并不是我们想要的，因此怀疑以上那句话的真实性？？？）

select id,name from  test where rownum<4 order by id;      
 
         ID NAME
---------- ------------------------------
         1 sag
         3 dfhg
         4 ashtrhf

 

 说明：标红的字体有待考证！！！