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

1特殊结果输出

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

1.1Top 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 ;

 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  62 consistent gets

 50 physical reads

 7232 bytes sent via  2 sorts (memory)

 5 rows processed

 5 and rownum <= 10

 6 ) b

 7 where b.rn > 5;

 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2770 Card=10 Bytes=32830)

 1 0 VIEW (Cost=2770 Card=10 Bytes=32830)

 2 1 COUNT (STOPKEY)

 35 consistent gets

 30 physical reads

 7271 bytes sent via  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,a.owner,NULL) owner,DECODE(ROWNUM-min_sno,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

------------------------------ ---------- ------------------------------

 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;

 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)

 0 physical reads

 654 bytes sent via  1 rows processed

 3 and rownum <= 1;

 3 consistent gets

sql> /

 1 rows processed

10G以后，这个问题就被修正了：

 2 where rownum = 1;

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)

 1 recursive calls

 4 consistent gets

 1 physical reads

 1201 bytes sent via  385 bytes received via  2  2 where rownum <= 1;

 1 - filter(ROWNUM<=1)

 1 rows processed

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;

 0

这是因为：

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

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

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

sql> select count(*)

 2 from

 3 (select BL_REF_CDE,rownum rn from css_bl_view)

 4 where rn > 1;

 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的数据：

 2 minus

 3 select * from t_test4

 4 where rownum <= 25;

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.2ROWNUM和Order BY

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

 2 where rownum <= 5

 3 order by object_id;

 2 C_OBJ#

 3 I_OBJ#

 4 TAB$

 5 CLU$

 6 C_TS#

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

 3 order by 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$';

 21645

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

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

 3 order by object_name)

 4 where rownum <= 5;

 35489 /1000e8d1_LinkedHashMapValueIt

 35490 /1000e8d1_LinkedHashMapValueIt

 21801 /1005bd30_LnkdConstant

 21802 /1005bd30_LnkdConstant

 17205 /10076b23_OraCustomDatumClosur

3.3排序分页

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

请看以下例子，我们希望对T_TEST1的OWNER字段排序后，以每页输出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.

 4 where rownum <= 20)

 5 where rn >= 11;

AFWOWNER AFWTOKENSTATUSCODE_PK

AFWOWNER AFWTOKENSTATUS

AFWOWNER AFWTOKENADMIN_IDX1

AFWOWNER AFWTOKENADMINCODE_PK

AFWOWNER AFWTOKENADMIN

AFWOWNER AFWTOKEN

AFWOWNER AFWSERVERCONFIGURATION_PK

AFWOWNER AFWSERVERCONFIGURATION

10 rows selected.

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

 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=205 Card=20 Bytes=94

 0)

 1 0 VIEW (Cost=205 Card=20 Bytes=940)

 3 2 VIEW (Cost=205 Card=30670 Bytes=1042780)

 4 3 SORT (ORDER BY STOPKEY) (Cost=205 Card=30670 Bytes=858760)

 5 4 table ACCESS (FULL) OF 'T_TEST1' (Cost=42 Card=30670 Bytes=858760)

看下这个“陷阱”是怎么形成的。从查询计划上，我们可以注意到，对于子查询

select a.*,153)"> (select owner,153)">where rownum <= 20

优化器采用了“SORT (ORDER BY STOPKEY)”。

“SORT (ORDER BY STOPKEY)”不需要对所有数据进行排序，而是只要找出结果集中的按特定顺序的最前N条记录，一旦找出了这N条记录，就无需再对剩下的数据进行排序，而直接返回结果。这种算法我们可以视为是“快速排序”算法的变种。快速排序算法的基本思想是：先将数据分2组集合，保证第一集合中的每个数据都大于第二个集合中每个数据，然后再按这个原则对每个集合进行递归分组，直到集合的单位最小。在进行“SORT (ORDER BY STOPKEY)”时，首先找出N条数据（这些数据并没有做排序）放在第一组，保证第一组的数据都大于第二组的数据，然后只对第一组数据进行递归。

可以看到，基于这样的算法基础上，如果N的数值不同，数据的分组也不同（如N=20时，第一次分组比例为12:8，然后继续递归；当N=10时，第一次分组比例为3:7 … …），这样，在数据的排序字段值都相等时，输出结果的顺序就会因为N值不同而不同。

知道原因后，我们可以通过以下几种方法来避免这个“陷阱”。

1、让查询计划避免“SORT (ORDER BY STOPKEY)”，采用“SORT (ORDER BY)”，使数据排序不受ROWNUM的影响。但这样会使所有数据都做排序：

 4 where rn <= 10

 5 and rn >= 1;

AFWOWNER AFWSERVERCONFIGURATION_IDX1

AFWOWNER AFWTOKENTYPECODE_PK

 4 where rn <= 20

 5 and rn >= 11;

AFWOWNER AFWTOKENTYPE

AFWOWNER AFWTOKEN_PK

AFWOWNER AFWTOKEN_IDX6

sql> set autot trace

 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=237 Card=30670 Bytes=1441490)

 1 0 VIEW (Cost=237 Card=30670 Bytes=1441490)

 3 2 VIEW (Cost=237 Card=30670 Bytes=1042780)

 4 3 SORT (ORDER BY) (Cost=237 Card=30670 Bytes=1073450)

table ACCESS (FULL) OF 'T_TEST1' (Cost=42 Card=30670 Bytes=1073450)

2、在排序时，加上一个或多个字段（如主键字段、ROWID），使排序结果具有唯一性：

 5 where rn >= 1;

AFWOWNER AFWADAPTERLOOKUP

AFWOWNER AFWSERVER

AFWOWNER AFWTOKEN_IDX1

AFWOWNER AFWTOKEN_IDX2

AFWOWNER AFWTOKEN_IDX3

AFWOWNER AFWTOKEN_IDX4

AFWOWNER AFWTOKEN_IDX5

 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=253 Card=20 Bytes=940)

 1 0 VIEW (Cost=253 Card=20 Bytes=940)

 3 2 VIEW (Cost=253 Card=30670 Bytes=1042780)

 4 3 SORT (ORDER BY STOPKEY) (Cost=253 Card=30670 Bytes=1196130)

table ACCESS (FULL) OF 'T_TEST1' (Cost=42 Card=306

 70 Bytes=1196130)

3、对排序字段建立索引，并强制使用索引。这样就能利用索引已经建立好的排序结果：

sql> create index t_test1_idx1 on t_test1(owner);

Index created.

 3 (select /*+index(t T_TEST1_IDX1)*/owner,object_name from t_test1 t order by owner) a

 5 where rn >= 1

 6 ;

 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=414 Card=20 Bytes=940)

 1 0 VIEW (Cost=414 Card=20 Bytes=940)

 3 2 VIEW (Cost=414 Card=30670 Bytes=1042780)

 4 3 table ACCESS (BY INDEX ROWID) OF 'T_TEST1' (Cost=414Card=30670 Bytes=858760)

 5 4 INDEX (FULL SCAN) OF 'T_TEST1_IDX1' (NON-UNIQUE) (

 Cost=26 Card=30670)

以上就是ROWNUM的使用技巧及其注意事项，希望编程成员正确使用ROWNUM，也希望DBA遇到相关问题能迅速定位。