oracle:rownum 的使用技巧

fengda

　　声明：
　　本文转载出自：http://www.blogjava.net/conans/articles/219693.html
　　ROWNUM是一种伪列，它会根据返回记录生成一个序列化的数字。利用ROWNUM，我们可以生产一些原先难以实现的结果输出，但因为它是伪列的这个特殊性，我们在使用时也需要注意一些事项，不要掉入“陷阱”。下面就介绍一下它的使用技巧及注意事项。
1         特殊结果输出
　　利用ROWNUM，我们可以做到一些特殊方式的输出。
1.1     Top N结果输出
　　我们如果希望取输出结果的前面几条数据，通过ROWNUM可以轻松实现：
sql> select * from t_test4  2  where rownum  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  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_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_test1  2  where rownum =、=操作
　　不能对ROWNUM使用>（大于1的数值）、>=（大于或等于1的数值）、=（大于或等于1的数值），否则无结果
sql> select count(*) from css_bl_view a where rownum>0;  COUNT(*)----------361928sql> select count(*) from css_bl_view a  2  where rownum > 1;  COUNT(*)----------         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;  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  select object_id,object_name from t_test1  2  where rownum  select object_id,object_name from t_test1  2  where rownum  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  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 = 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 from  2  (select a.*, rownum as rn from  3  (select owner, object_name from t_test1 order by owner) a  4  where 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 from  2  (select a.*, rownum as rn from  3  (select owner, object_name from t_test1 order by owner) a  4  where 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 rownum  select owner, object_name from  2  (select a.*, rownum as rn from  3  (select owner, object_name, rowid from t_test1 order by owner) a)  4  where 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 from  2  (select a.*, rownum as rn from  3  (select owner, object_name, rowid from t_test1 order by owner) a)  4  where 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 from  2  (select a.*, rownum as rn from  3  (select owner, object_name, rowid from t_test1 order by owner) a)  4  where 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 from  2  (select a.*, rownum as rn from  3  (select owner, object_name, rowid from t_test1 order by owner, object_id) a  4  where 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 from  2  (select a.*, rownum as rn from  3  (select owner, object_name, rowid from t_test1 order by owner, object_id) a  4  where 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 from  2  (select a.*, rownum as rn from  3  (select owner, object_name, rowid from t_test1 order by owner, object_id) a  4  where 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 from  2  (select a.*, rownum as rn from  3  (select /*+index(t T_TEST1_IDX1)*/owner, object_name from t_test1 t order by owner) a  4  where rownum = 1  6  ;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 from  2  (select a.*, rownum as rn from  3  (select /*+index(t T_TEST1_IDX1)*/owner, object_name from t_test1 t order by owner) a  4  where 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 from  2  (select a.*, rownum as rn from  3  (select /*+index(t T_TEST1_IDX1)*/owner, object_name from t_test1 t order by owner) a  4  where 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遇到相关问题能迅速定位。