ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于rownum的心得

关于rownum的心得

原创 Linux操作系统 作者:harrison_han 时间:2010-08-19 04:59:51 0 删除 编辑

rownum的执行原理如下:

先查询出结果,然后按照给定的rownum的条件,对结果进行rownum的编号,永远从1开始,第一条符合条件的记录rownum是1,然后是2,就像子弹一样,只能按照顺序一颗颗的射出,不能跳跃。

举例1:rownum<=3, 则rownum=1,2,3,取出前三个结果。


举例2:rownum<3,则rownum=1,2,取出两个结果。


举例3:rownum=3,不符合条件,将查不到任何值,因为rownum永远从1开始,第一 个符合条件的记录rownum=1,然后才会有rownum=2,依次累加,因此若不存在rownum=1,则rownum=其他,都不存在

举例4:rownum>3,不符合条件,将查不到任何值


举例5:rownum=1 or rownum=3,只能查到第一条记录,不存在rownun=2,则不会存在rownum=3.


举例6:rownum=1 or rownum=2 or rownum=3 or rownum=5, 只能查询到3条记录,即rownum=1,2,3

 

以下内容为转载,还是可以很好的加深对rownum的理解
正确使用ROWNUM
Oracle中rownum是一个pseudocolumn,它是用来标识查询返回结果集每一行号顺序.
SQL>select rownum,object_id,object_name from test where rownum<5;
  ROWNUM  OBJECT_ID OBJECT_NAME
---------- ---------- --------------------
         1         22    USER$
         2         49     I_CON2
         3         30     I_COBJ#
         4         18    OBJ$
创建一个带有primary key的heap-organization表
SQL> create table test_heap(object_id number,object_name varchar2(32),primary key(object_id));
Table created.
插入上面的数据:
SQL> insert into test_heap select object_id,object_name from test where rownum<5;
4 rows created.
SQL> select object_id,object_name from test_heap;
 OBJECT_ID OBJECT_NAME
---------- --------------------
        22 USER$
        49 I_CON2
        30 I_COBJ#
        18 OBJ$
我们发现即使heap表有primary key它的存储也是以插入数据的先后顺序来物理存储的,这也是heap-organization表结构决定的.我们可以继续验证是否如此.我们先后插入一个object_id分别300,200,如果是primary key可以决定表的存储位置,就应该在选择的时候,200排在300前面?
SQL> insert into test_heap values(300,'test300');
1 row created.
SQL> insert into test_heap values(200,'test200');
1 row created.
SQL> select object_id,object_name from test_heap;
 OBJECT_ID OBJECT_NAME
---------- --------------------
        22   USER$
        49   I_CON2
        30   I_COBJ#
        18   OBJ$
       300  test300
       200  test200
到这里我们可以说primary key在heap-oranization table中不是用来确定物理存储的.它仅仅是一个约束保证数据的完整性和一致性.
有点跑题了,我们继续rownum的测试。
分别执行下面的的三个查询:我们看看rownum怎么工作:
SQL> select rownum,object_id,object_name from test_heap;
    ROWNUM  OBJECT_ID OBJECT_NAME
---------- ---------- --------------------------------
         1         22 USER$
         2         49 I_CON2
         3         30 I_COBJ#
         4         18 OBJ$
         5        300 test300
         6        200 test200
SQL> select rownum,object_id,object_name from test_heap order by object_id;
    ROWNUM  OBJECT_ID OBJECT_NAME
---------- ---------- --------------------------------
         1         18 OBJ$
         2         22 USER$
         3         30 I_COBJ#
         4         49 I_CON2
         5        200 test200
         6        300 test300
去掉object_id上索引,执行上面同样的语句,你猜测结果会跟上面有什么不同吗?
SQL> drop index pk_test_heap;
Index dropped.
SQL> select rownum,object_id,object_name from test_heap order by object_id;
    ROWNUM  OBJECT_ID OBJECT_NAME
---------- ---------- --------------------------------
         4         18 OBJ$
         1         22 USER$
         3         30 I_COBJ#
         2         49 I_CON2
         6        200 test200
         5        300 test300
其实从上面的语句我们发现,最后rownum结果不一样,是跟有没有索引有关系,如果有索引,oracle先执行索引来返回符合order by要求的结果集,然后在对结果结果集进行rownum标号.没有索引,它是对全表扫描的结果标号,然后按照order by要求来取出符合要求的结果.这对我们进行Top-N查询的最后结果是否是我们想要的至关重要.所以防止上面的语句发生我们不期望的结果,一般可以用下面的方式来替代.
SQL> select object_id,object_name from (select object_id,object_name from test_heap order by object_id) where  rownum<3
 OBJECT_ID OBJECT_NAME
---------- --------------------------------
        18 OBJ$
        22 USER$
或者
SQL>select object_id,object_name from (select row_number() over (order by object_id) rn, object_id,object_name from test_heap) where rn<3
OBJECT_ID OBJECT_NAME
---------- --------------------------------
        18 OBJ$
        22 USER$
如果用下面的语句看看是否能得到你希望的结果否?
SQL> select object_id,object_name from (select rownum rn,object_id,object_name from test_heap order by object_id) where rn<3;
 OBJECT_ID OBJECT_NAME
---------- --------------------------------
        22 USER$
        49 I_CON2
显然不是我们期望的结果,由于我们所在在object_id没有索引,如果有索引,将会得到你期望的结果.
SQL> create index pk_test_heap on test_heap(object_id);
Index created.
再来执行上面的同样的语句
SQL>select object_id,object_name from (select rownum rn,object_id,object_name from test_heap order by object_id) where rn<3;
 OBJECT_ID OBJECT_NAME
---------- --------------------------------
        18 OBJ$
        22 USER$
我们执行下面语句看会得到什么结果?
SQL> select rownum,object_id,object_name from test_heap where rownum>1;
no rows selected
rownum>N这种方式肯定是得不到任何结果得,这是很容易理解得,因为oracle先全表扫描得到结果,然后一次取出一行,每次rownum总是等于1,不能满足>N得条件..下面我们再执行几条语句,就会理解.
SQL> select object_id,object_name from test_heap where rownum=1;
 OBJECT_ID OBJECT_NAME
---------- --------------------------------
        22 USER$
SQL> select object_id,object_name from test_heap where rownum=2;
no rows selected
SQL> select object_id,object_name from test_heap where rownum>=1;
 OBJECT_ID OBJECT_NAME
---------- --------------------------------
        22 USER$
        49 I_CON2
        30 I_COBJ#
        18 OBJ$
       300 test300
       200 test200
同样道理将rownum用于delete or update.
SQL> update test_heap set object_id=rownum;
6 rows updated.
SQL> select object_id,object_name from test_heap;
 OBJECT_ID OBJECT_NAME
---------- --------------------------------
         1 USER$
         2 I_CON2
         3 I_COBJ#
         4 OBJ$
         5 test300
         6 test200
SQL> delete from test_heap where rownum<3;
2 rows deleted.
SQL> select object_id,object_name from test_heap;
 OBJECT_ID OBJECT_NAME
---------- --------------------------------
         3 I_COBJ#
         4 OBJ$
         5 test300
         6 test200

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

上一篇: 关于Role password
请登录后发表评论 登录
全部评论

注册时间:2010-01-09

  • 博文量
    10
  • 访问量
    24642