Oracle 中
中中
中 ROWNUM 的使用技
的使用技的使用技
的使用技巧
巧巧
巧
作者
作者作者
作者：
：：
：fuyuncat
来源
来源来源
来源：
：：
：WWW.HelloDBA.COM
作者简介
作者简介作者简介
作者简介
黄玮，男，99 年开始从事 DBA 工作，有多年的水利、军工、电信及航
运行业大型数据库 Oracle 开发、设计和维护经验。
曾供职于南方某著名电信设备制造商——H 公司。期间，作为 DB 组
长，负责设计、开发和维护彩铃业务的数据库系统。目前，H 公司的彩铃系
统是世界上终端用户最多的彩铃系统。最终用户数过亿。
目前供职于某世界著名物流公司，负责公司的电子物流系统的数据库开
发、维护工作。
msn: fuyuncat@hotmail.com
Email：fuyuncat@gmail.com
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)
