oracle:rownum 的使用技巧
声明：本文转载出自：http://www.blogjava.net/conans/articles/219693.html
ROWNUM是一种伪列，它会根据返回记录生成一个序列化的数字。利用ROWNUM，我们可以生产一些原先难以实现的结果输出，但因为它是伪列的这个特殊性，我们在使用时也需要注意一些事项，不要掉入“陷阱”。下面就介绍一下它的使用技巧及注意事项。
1 特殊结果输出
利用ROWNUM，我们可以做到一些特殊方式的输出。
1.1 Top N结果输出
我们如果希望取输出结果的前面几条数据，通过ROWNUM可以轻松实现：
sql> select * from t_test42where rownumselect * from2(3select a.*, rownum as rn from css_bl_view a4where capture_phone_num = '(1) 925-4604800'5) b6where 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---------------------------------------------------------- 0recursive calls 0db block gets 29346consistent gets 29190physical reads 0redo size 7328bytes sent via sql*Net to client 234bytes received via sql*Net from client 4sql*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 5rows processed 另外一种实现方式：
sql> select * from css_bl_view a2where capture_phone_num = '(1) 925-4604800'3and rownumSELECT DECODE(ROWNUM-min_sno,0,a.owner,NULL) owner,DECODE(ROWNUM-min_sno,0,1,rownum+1-min_sno) sno, a.name2FROM (SELECT *3 FROM t_test84 ORDER BY owner, name ) a,5 (SELECT owner, MIN(rownum) min_sno6 FROM( SELECT *7 FROM t_test88 ORDER BY owner, name)9 GROUP BY owner) b 10WHERE a.owner=b.owner;OWNER SNO NAME------------------------------ ---------- ------------------------------APARKMAN 1 JOB_QUEUE 2 AQ$_JMS_TEXT_ESTRMADMIN 1 STREAMS_QUEUE 2 AQ$_STREAMS_QUEUE_TABLE_ESYS 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_QSYSMAN 1 AQ$_MGMT_NOTIFY_QTABLE_E 2 MGMT_NOTIFY_Qsystem 1 DEF$_AQERROR 2 DEF$_AQCALL 3 AQ$_DEF$_AQERROR_E 4 AQ$_DEF$_AQCALL_EWMSYS 1 AQ$_WM$EVENT_QUEUE_TABLE_E 2 WM$EVENT_QUEUE29 rows selected.2 性能
我们很多程序员在确认某个表中是否有相应数据时，喜欢加上ROWNUM=1，其思路就是只要存在一条数据就说明有相应数据，查询就可以直接返回了，这样就能提高性能了。但是在10G之前，使用ROWNUM=1是不能达到预期的性能效果的，而是需要通过 select * from t_test12where rownum =、=操作
不能对ROWNUM使用>（大于1的数值）、>=（大于或等于1的数值）、=（大于或等于1的数值），否则无结果
sql> select count(*) from css_bl_view a where rownum>0;COUNT(*)----------361928sql> select count(*) from css_bl_view a2where rownum > 1;COUNT(*)---------- 0 这是因为：
1、ROWNUM是伪列，必须要要有返回结果后，每条返回记录就会对应产生一个ROWNUM数值；
2、返回结果记录的ROWNUM是从1开始排序的，因此第一条始终是1;
这样，当查询到第一条记录时，该记录的ROWNUM为1，但条件要求ROWNUM>1，因此不符合，继续查询下一条；因为前面没有符合要求的记录，因此下一条记录过来后，其ROWNUM还是为1，如此循环，就不会产生结果。上述查询可以通过子查询来替代：
sql> select count(*)2from3(select BL_REF_CDE, rownum rn from css_bl_view)4where rn > 1;COUNT(*)---------- 361927 我们可以通过以下方式来实现对ROWNUM的>、=的查询：
查询ROWNUM=5的数据：
sql> select object_id,object_name2from (select object_id,object_name, rownum as rn from t_test1)3where rn = 5; OBJECT_ID OBJECT_NAME---------- ------------------------------ 29 C_COBJ# 查询ROWNUM > 25的数据：
sql> select * from t_test42minus3select * from t_test44where rownumselect object_id,object_name from t_test12where rownumselect object_id,object_name from t_test12where rownumselect count(*) from t_test12where object_name < 'CON$';COUNT(*)---------- 21645 出现这种混乱的原因是：oracle先按物理存储位置（rowid）顺序取出满足rownum条件的记录，即物理位置上的前5条数据，然后在对这些数据按照Order By的字段进行排序，而不是我们所期望的先排序、再取特定记录数。
如果需要对非主键字段排序再去前n条数据，我们可以以以下方式实现：
sql> select object_id,object_name2from (select object_id,object_name from t_test13 order by object_name)4where rownumselect owner, object_name from2(select a.*, rownum as rn from3(select owner, object_name from t_test1 order by owner) a4where rownum = 1;OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWADAPTERAFWOWNER AFWADAPTERCONFIGURATIONAFWOWNER AFWADAPTERCONFIGURATION_IDX1AFWOWNER AFWADAPTERFQN_PKAFWOWNER AFWADAPTERCONFIGURATION_PKAFWOWNER AFWADAPTERCONFIGURATION_IDX2AFWOWNER AFWSERVERCODE_PKAFWOWNER AFWSERVERAFWOWNER AFWADAPTERLOOKUP_IDX1AFWOWNER AFWADAPTERLOOKUP10 rows selected.sql> select owner, object_name from2(select a.*, rownum as rn from3(select owner, object_name from t_test1 order by owner) a4where rownum = 11;OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWTOKENSTATUSCODE_PKAFWOWNER AFWTOKENSTATUSAFWOWNER AFWTOKENADMIN_IDX1AFWOWNER AFWTOKENADMINCODE_PKAFWOWNER AFWTOKENADMINAFWOWNER AFWTOKENAFWOWNER AFWSERVERCONFIGURATION_PKAFWOWNER AFWSERVERCONFIGURATIONAFWOWNER AFWSERVERAFWOWNER AFWADAPTERLOOKUP10 rows selected. 仔细比较结果，你会发现“AFWSERVER”、“AFWADAPTERLOOKUP”在两次分页结果中都出现了。但是OBJECT_NAME在每个OWNER中的值是唯一的，说明这个输出结果是错误的，我们又陷入了一个“陷阱”。这是怎么回事呢，请先看下上述语句的查询计划：
sql> select owner, object_name from2(select a.*, rownum as rn from3(select owner, object_name from t_test1 order by owner) a4where rownum = 11;10 rows selected.Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=205 Card=20 Bytes=94 0) 1 0 VIEW (Cost=205 Card=20 Bytes=940) 2 1 COUNT (STOPKEY) 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.*, rownum as rn from (select owner, object_name from t_test1 order by owner) awhere rownumselect owner, object_name from2(select a.*, rownum as rn from3(select owner, object_name, rowid from t_test1 order by owner) a)4where rn = 1;OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWADAPTERAFWOWNER AFWADAPTERCONFIGURATIONAFWOWNER AFWADAPTERCONFIGURATION_IDX2AFWOWNER AFWADAPTERCONFIGURATION_PKAFWOWNER AFWADAPTERCONFIGURATION_IDX1AFWOWNER AFWADAPTERFQN_PKAFWOWNER AFWADAPTERLOOKUP_IDX1AFWOWNER AFWSERVERCODE_PKAFWOWNER AFWSERVERCONFIGURATION_IDX1AFWOWNER AFWTOKENTYPECODE_PK10 rows selected.sql> select owner, object_name from2(select a.*, rownum as rn from3(select owner, object_name, rowid from t_test1 order by owner) a)4where rn = 11;OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWTOKENTYPEAFWOWNER AFWTOKENSTATUSCODE_PKAFWOWNER AFWTOKENSTATUSAFWOWNER AFWTOKENADMIN_IDX1AFWOWNER AFWTOKENADMINCODE_PKAFWOWNER AFWTOKENADMINAFWOWNER AFWTOKENAFWOWNER AFWSERVERCONFIGURATION_PKAFWOWNER AFWTOKEN_PKAFWOWNER AFWTOKEN_IDX610 rows selected.sql> set autot tracesql> select owner, object_name from2(select a.*, rownum as rn from3(select owner, object_name, rowid from t_test1 order by owner) a)4where rn = 11;10 rows selected.Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=237 Card=30670 Bytes=1441490) 1 0 VIEW (Cost=237 Card=30670 Bytes=1441490) 2 1 COUNT 3 2 VIEW (Cost=237 Card=30670 Bytes=1042780) 4 3 SORT (ORDER BY) (Cost=237 Card=30670 Bytes=1073450) 5 4 table ACCESS (FULL) OF 'T_TEST1' (Cost=42 Card=30670 Bytes=1073450) 2、在排序时，加上一个或多个字段（如主键字段、ROWID），使排序结果具有唯一性：
sql> select owner, object_name from2(select a.*, rownum as rn from3(select owner, object_name, rowid from t_test1 order by owner, object_id) a4where rownum = 1;OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWADAPTERAFWOWNER AFWADAPTERFQN_PKAFWOWNER AFWADAPTERCONFIGURATIONAFWOWNER AFWADAPTERCONFIGURATION_PKAFWOWNER AFWADAPTERCONFIGURATION_IDX1AFWOWNER AFWADAPTERCONFIGURATION_IDX2AFWOWNER AFWADAPTERLOOKUPAFWOWNER AFWADAPTERLOOKUP_IDX1AFWOWNER AFWSERVERAFWOWNER AFWSERVERCODE_PK10 rows selected.sql> select owner, object_name from2(select a.*, rownum as rn from3(select owner, object_name, rowid from t_test1 order by owner, object_id) a4where rownum = 11;OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWSERVERCONFIGURATIONAFWOWNER AFWSERVERCONFIGURATION_PKAFWOWNER AFWSERVERCONFIGURATION_IDX1AFWOWNER AFWTOKENAFWOWNER AFWTOKEN_PKAFWOWNER AFWTOKEN_IDX1AFWOWNER AFWTOKEN_IDX2AFWOWNER AFWTOKEN_IDX3AFWOWNER AFWTOKEN_IDX4AFWOWNER AFWTOKEN_IDX510 rows selected.sql> set autot tracesql> select owner, object_name from2(select a.*, rownum as rn from3(select owner, object_name, rowid from t_test1 order by owner, object_id) a4where rownum = 11;10 rows selected.Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=253 Card=20 Bytes=940) 1 0 VIEW (Cost=253 Card=20 Bytes=940) 2 1 COUNT (STOPKEY) 3 2 VIEW (Cost=253 Card=30670 Bytes=1042780) 4 3 SORT (ORDER BY STOPKEY) (Cost=253 Card=30670 Bytes=1196130) 5 4 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.sql> select owner, object_name from2(select a.*, rownum as rn from3(select /*+index(t T_TEST1_IDX1)*/owner, object_name from t_test1 t order by owner) a4where rownum = 16;OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWADAPTERAFWOWNER AFWADAPTERCONFIGURATIONAFWOWNER AFWADAPTERCONFIGURATION_IDX1AFWOWNER AFWADAPTERCONFIGURATION_IDX2AFWOWNER AFWADAPTERCONFIGURATION_PKAFWOWNER AFWADAPTERFQN_PKAFWOWNER AFWADAPTERLOOKUPAFWOWNER AFWADAPTERLOOKUP_IDX1AFWOWNER AFWSERVERAFWOWNER AFWSERVERCODE_PK10 rows selected.sql> select owner, object_name from2(select a.*, rownum as rn from3(select /*+index(t T_TEST1_IDX1)*/owner, object_name from t_test1 t order by owner) a4where rownum = 11;OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWSERVERCONFIGURATIONAFWOWNER AFWSERVERCONFIGURATION_IDX1AFWOWNER AFWSERVERCONFIGURATION_PKAFWOWNER AFWTOKENAFWOWNER AFWTOKENADMINAFWOWNER AFWTOKENADMINCODE_PKAFWOWNER AFWTOKENADMIN_IDX1AFWOWNER AFWTOKENSTATUSAFWOWNER AFWTOKENSTATUSCODE_PKAFWOWNER AFWTOKENTYPE10 rows selected.sql> set autot tracesql> select owner, object_name from2(select a.*, rownum as rn from3(select /*+index(t T_TEST1_IDX1)*/owner, object_name from t_test1 t order by owner) a4where rownum = 11;10 rows selected.Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=414 Card=20 Bytes=940) 1 0 VIEW (Cost=414 Card=20 Bytes=940) 2 1 COUNT (STOPKEY) 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遇到相关问题能迅速定位。
页:
[1]