HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

Oracle中ROWNUM的使用技巧

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2009-02-14 14:54:36

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

ROWNUM是一种伪列,它会根据返回记录生成一个序列化的数字。利用ROWNUM,我们可以生产一些原先难以实现的结果输出,但因为它是伪列的这个特殊性,我们在使用时也需要注意一些事项,不要掉入“陷阱”。下面就介绍一下它的使用技巧及注意事项。

1         特殊结果输出

利用ROWNUM,我们可以做到一些特殊方式的输出。

1.1     Top N结果输出

我们如果希望取输出结果的前面几条数据,通过ROWNUM可以轻松实现:

 

SQL> select * from t_test4
  2  where rownum <= 5;
 
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
WOW                                    71 26-APR-07
CS2                                    70 15-JAN-07
3                                      69 01-NOV-06
DMP                                    68 12-OCT-06
PROFILER                               67 05-SEP-06

 

但是,如果你希望对一个排序结果取Top N数据的话,使用ROWNUM存在一些“陷阱”,我们后面部分会介绍这些“陷阱”并且说明如何避免。

1.2     分页查询

利用ROWNUM对结果进行分页,下面返回结果中的第6到第10条记录:

SQL> select * from
  2  (
  3  select a.*, rownum as rn from css_bl_view a
  4  where capture_phone_num = '(1) 925-4604800'
  5  ) b
  6  where b.rn between 6 and 10;
 
6 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2770 Card=2183 Bytes =7166789)
   1    0   VIEW (Cost=2770 Card=2183 Bytes=7166789)
   2    1     COUNT
   3    2       TABLE ACCESS (FULL) OF 'CSS_BL_VIEW' (Cost=2770 Card=2183 Bytes=1305434)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      29346  consistent gets
      29190  physical reads
          0  redo size
       7328  bytes sent via SQL*Net to client
        234  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

 

另外一种实现方式:

 

SQL> select * from css_bl_view a
  2  where capture_phone_num = '(1) 925-4604800'
  3  and rownum <= 10
  4  minus
  5  select * from css_bl_view a
  6  where capture_phone_num = '(1) 925-4604800'
  7  and rownum <= 5
  8  ;
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5920 Card=10 Bytes=8970)
   1    0   MINUS
   2    1     SORT (UNIQUE) (Cost=2960 Card=10 Bytes=5980)
   3    2       COUNT (STOPKEY)
   4    3         TABLE ACCESS (FULL) OF 'CSS_BL_VIEW' (Cost=2770 Card=2183 Bytes=1305434)
   5    1     SORT (UNIQUE) (Cost=2960 Card=5 Bytes=2990)
   6    5       COUNT (STOPKEY)
   7    6         TABLE ACCESS (FULL) OF 'CSS_BL_VIEW' (Cost=2770 Card=2183 Bytes=1305434)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         62  consistent gets
         50  physical reads
          0  redo size
       7232  bytes sent via SQL*Net to client
        234  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          5  rows processed

 

第三种实现方式:

 

SQL> select * from
  2  (
  3  select a.*, rownum as rn from css_bl_view a
  4  where capture_phone_num = '(1) 925-4604800'
  5  and rownum <= 10
  6  ) b
  7  where b.rn > 5;
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2770 Card=10 Bytes=32830)
   1    0   VIEW (Cost=2770 Card=10 Bytes=32830)
   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'CSS_BL_VIEW' (Cost=2770 Card=2183 Bytes=1305434)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         35  consistent gets
         30  physical reads
          0  redo size
       7271  bytes sent via SQL*Net to client
        234  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

 

这里特地将三种实现方式的查询计划及统计数据打印出来,大家可以比较一下3中方式的性能。

1.3     利用ROWNUM做分组子排序

对于以下表T_TEST4的内容:

 

OWNER                                   NAME
------------------------------------------------------
STRMADMIN                               STREAMS_QUEUE
APARKMAN                                JOB_QUEUE
SYS                                     AQ$_AQ_SRVNTFN_TABLE_E
SYS      &nbs