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                                     AQ$_KUPC$DATAPUMP_QUETAB_E
APARKMAN                                AQ$_JMS_TEXT_E
STRMADMIN                               AQ$_STREAMS_QUEUE_TABLE_E
SYS                                     AQ$_SCHEDULER$_EVENT_QTAB_E

 

如果我们希望结果按照OWNER进行分组后,再对每组中成员进行编号,结果类似如下:

 

OWNER                                   NO NAME
------------------------------------------------------
APARKMAN                                1 JOB_QUEUE
                                        2 AQ$_JMS_TEXT_E
STRMADMIN                               1 STREAMS_QUEUE
                                        2 AQ$_STREAMS_QUEUE_TABLE_E
SYS                                     1 AQ$_AQ_SRVNTFN_TABLE_E
                                        2 AQ$_KUPC$DATAPUMP_QUETAB_E
                                        3 AQ$_SCHEDULER$_EVENT_QTAB_E

 

在没有ROWNUM时要实现这样的功能会很复杂,但通过ROWNUM我们可以轻松实现:

 

SQL> SELECT DECODE(ROWNUM-min_sno,0,a.owner,NULL) owner,DECODE(ROWNUM-min_sno,0,1,rownum+1-min_sno) sno, a.name
  2  FROM (SELECT *
  3        FROM t_test8
  4        ORDER BY owner, name ) a,
  5       (SELECT owner, MIN(rownum) min_sno
  6        FROM( SELECT *
  7              FROM t_test8
  8              ORDER BY owner, name)
  9        GROUP BY owner) b
 10  WHERE a.owner=b.owner;
 
OWNER                                 SNO NAME
------------------------------ ---------- ------------------------------
APARKMAN                                1 JOB_QUEUE
                                        2 AQ$_JMS_TEXT_E
STRMADMIN                               1 STREAMS_QUEUE
                                        2 AQ$_STREAMS_QUEUE_TABLE_E
SYS                                     1 AQ$_AQ_SRVNTFN_TABLE_E
                                        2 AQ$_KUPC$DATAPUMP_QUETAB_E
                                        3 AQ$_SCHEDULER$_EVENT_QTAB_E
                                        4 AQ$_SCHEDULER$_JOBQTAB_E
                                        5 AQ$_STREAMS_QUEUE_TABLE_E
                                        6 AQ$_SYS$SERVICE_METRICS_TAB_E
                                        7 AQ$_AQ_EVENT_TABLE_E
                                        8 AQ$_AQ$_MEM_MC_E
                                        9 AQ$_ALERT_QT_E
                                       10 ALERT_QUE
                                       11 AQ_EVENT_TABLE_Q
                                       12 SYS$SERVICE_METRICS
                                       13 STREAMS_QUEUE
                                       14 SRVQUEUE
                                       15 SCHEDULER$_JOBQ
                                       16 SCHEDULER$_EVENT_QUEUE
                                       17 AQ_SRVNTFN_TABLE_Q
SYSMAN                                  1 AQ$_MGMT_NOTIFY_QTABLE_E
                                        2 MGMT_NOTIFY_Q
SYSTEM                                  1 DEF$_AQERROR
                                        2 DEF$_AQCALL
                                        3 AQ$_DEF$_AQERROR_E
                                        4 AQ$_DEF$_AQCALL_E
WMSYS                                   1 AQ$_WM$EVENT_QUEUE_TABLE_E
                                        2 WM$EVENT_QUEUE
 
29 rows selected.

2         性能

我们很多程序员在确认某个表中是否有相应数据时,喜欢加上ROWNUM=1,其思路就是只要存在一条数据就说明有相应数据,查询就可以直接返回了,这样就能提高性能了。但是在10G之前,使用ROWNUM=1是不能达到预期的性能效果的,而是需要通过<2<=1作为过滤条件才能达到预期效果,看以下查询计划:

 

SQL> select * from t_test1
  2  where object_id <100
  3  and rownum = 1;
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=86)
   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_TEST1' (Cost=37 Card=89 Bytes=7654)
   3    2       INDEX (RANGE SCAN) OF 'T_TEST1_PK' (UNIQUE) (Cost=2 Card=89)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         62  consistent gets
          0  physical reads
          0  redo size
        654  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)
          1  rows processed
 
SQL> select * from t_test1
  2  where object_id <100
  3  and rownum <= 1;
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=86)
   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_TEST1' (Cost=37 Card=89 Bytes=7654)
   3    2       INDEX (RANGE SCAN) OF 'T_TEST1_PK' (UNIQUE) (Cost=2 Card=89)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        654  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)
          1  rows processed
 
SQL> /
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=86)
   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_TEST1' (Cost=37 Card=89 Bytes=7654)
   3    2       INDEX (RANGE SCAN) OF 'T_TEST1_PK' (UNIQUE) (Cost=2 Card=89)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        654  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)
          1  rows processed

 

10G以后,这个问题就被修正了:

 

SQL> select * from t_test1
  2  where rownum = 1;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 536364188
 
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    86 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T_TEST1 |     1 |    86 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          1  physical reads
          0  redo size
       1201  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL> select * from t_test1
  2  where rownum <= 1;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 536364188
 
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    86 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T_TEST1 |     1 |    86 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=1)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1201  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

3         ROWNUM的使用“陷阱”

由于ROWNUM是一个伪列,只有有结果记录时,ROWNUM才有相应数据,因此对它的使用不能向普通列那样使用,否则就会陷入一些“陷阱”当中。

3.1     ROWNUM进行>>==操作

不能对ROWNUM使用>(大于1的数值)、>=(大于或等于1的数值)、=(大于或等于1的数值),否则无结果

 

SQL> select count(*) from css_bl_view a where rownum>0;
 
  COUNT(*)
----------
361928
 
 
SQL> select count(*) from css_bl_view a
  2  where rownum > 1;
 
  COUNT(*)
----------
         0

 

这是因为:

1ROWNUM是伪列,必须要要有返回结果后,每条返回记录就会对应产生一个ROWNUM数值;

2、返回结果记录的ROWNUM是从1开始排序的,因此第一条始终是1;

 

这样,当查询到第一条记录时,该记录的ROWNUM1,但条件要求ROWNUM>1,因此不符合,继续查询下一条;因为前面没有符合要求的记录,因此下一条记录过来后,其ROWNUM还是为1,如此循环,就不会产生结果。上述查询可以通过子查询来替代:

 

SQL> select count(*)
  2  from
  3  (select BL_REF_CDE, rownum rn from css_bl_view)
  4  where rn > 1;
 
  COUNT(*)
----------
    361927

 

我们可以通过以下方式来实现对ROWNUM>=的查询:

查询ROWNUM=5的数据:

 

SQL> select object_id,object_name
  2  from (select object_id,object_name, rownum as rn from t_test1)
  3  where rn = 5;
 
 OBJECT_ID OBJECT_NAME
---------- ------------------------------
        29 C_COBJ#

 

查询ROWNUM > 25的数据:

 

SQL> select * from t_test4
  2  minus
  3  select * from t_test4
  4  where rownum <= 25;
 
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
DIP                                    19 21-NOV-05
OUTLN                                  11 21-NOV-05
PUBLIC                              99999 18-JUL-07
SYS                                     0 21-NOV-05
SYSMAN                                 32 21-NOV-05
SYSTEM                                  5 21-NOV-05
 
6 rows selected.

3.2     ROWNUMOrder BY

要注意的是:在使用ROWNUM时,只有当Order By的字段是主键时,查询结果才会先排序再计算ROWNUM,下面OBJECT_ID是表T_TEST1的主键字段:

 

SQL> select object_id,object_name from t_test1
  2  where rownum <= 5
  3  order by object_id;
 
 OBJECT_ID OBJECT_NAME
---------- ------------------------------
         2 C_OBJ#
         3 I_OBJ#
         4 TAB$
         5 CLU$
         6 C_TS#

 

但是,对非主键字段OBJECT_NAME进行排序时,结果就混乱了:

 

SQL> select object_id,object_name from t_test1
  2  where rownum <= 5
  3  order by object_name;
 
 OBJECT_ID OBJECT_NAME
---------- ------------------------------
        28 CON$
        29 C_COBJ#
        20 ICOL$
        44 I_USER1
        15 UNDO$
 
SQL> select count(*) from t_test1
  2  where object_name < 'CON$';
 
  COUNT(*)
----------
     21645

 

出现这种混乱的原因是:Oracle先按物理存储位置(rowid)顺序取出满足rownum条件的记录,即物理位置上的前5条数据,然后在对这些数据按照Order By的字段进行排序,而不是我们所期望的先排序、再取特定记录数。

 

如果需要对非主键字段排序再去前n条数据,我们可以以以下方式实现:

 

SQL> select object_id,object_name
  2  from (select object_id,object_name from t_test1
  3        order by object_name)
  4  where rownum <= 5;
 
 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     35489 /1000e8d1_LinkedHashMapValueIt
     35490 /1000e8d1_LinkedHashMapValueIt
     21801 /1005bd30_LnkdConstant
     21802 /1005bd30_LnkdConstant
     17205 /10076b23_OraCustomDatumClosur

 

3.3     排序分页

当对存在重复值的字段排序后再分页输出,我们很容易会陷入到另外一个“陷阱”。

 

请看以下例子,我们希望对T_TEST1OWNER字段排序后,以每页输出10个结果的方式分页输出:

 

SQL> select owner, object_name from
  2  (select a.*, rownum as rn from
  3  (select owner, object_name from t_test1 order by owner) a
  4  where rownum <= 10)
  5  where rn >= 1;
 
OWNER                          OBJECT_NAME
------------------------------ ------------------------------
AFWOWNER                       AFWADAPTER
AFWOWNER                       AFWADAPTERCONFIGURATION
AFWOWNER                       AFWADAPTERCONFIGURATION_IDX1
AFWOWNER                       AFWADAPTERFQN_PK
AFWOWNER                       AFWADAPTERCONFIGURATION_PK
AFWOWNER                       AFWADAPTERCONFIGURATION_IDX2
AFWOWNER                       AFWSERVERCODE_PK
AFWOWNER                       AFWSERVER
AFWOWNER                       AFWADAPTERLOOKUP_IDX1
AFWOWNER                       AFWADAPTERLOOKUP
 
10 rows selected.
 
SQL> select owner, object_name from
  2  (select a.*, rownum as rn from
  3  (select owner, object_name from t_test1 order by owner) a
  4  where rownum <= 20)
  5  where rn >= 11;
 
OWNER                          OBJECT_NAME
------------------------------ ------------------------------
AFWOWNER                       AFWTOKENSTATUSCODE_PK
AFWOWNER                       AFWTOKENSTATUS
AFWOWNER                       AFWTOKENADMIN_IDX1
AFWOWNER                       AFWTOKENADMINCODE_PK
AFWOWNER                       AFWTOKENADMIN
AFWOWNER                       AFWTOKEN
AFWOWNER                       AFWSERVERCONFIGURATION_PK
AFWOWNER                       AFWSERVERCONFIGURATION
AFWOWNER                       AFWSERVER
AFWOWNER                       AFWADAPTERLOOKUP
 
10 rows selected.

 

仔细比较结果,你会发现“AFWSERVER”、“AFWADAPTERLOOKUP”在两次分页结果中都出现了。但是OBJECT_NAME在每个OWNER中的值是唯一的,说明这个输出结果是错误的,我们又陷入了一个“陷阱”。这是怎么回事呢,请先看下上述语句的查询计划:

 

SQL> select owner, object_name from
  2  (select a.*, rownum as rn from
  3  (select owner, object_name from t_test1 order by owner) a
  4  where rownum <= 20)
  5  where rn >= 11;
 
10 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=205 Card=20 Bytes=94
          0)