Oracle中ROWNUM的提示

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但是，如果你希望對一個排序結果取TopN資料的話，使用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=2183Bytes=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=10Bytes=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=2183Bytes=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=2183Bytes=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=10Bytes=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=2183Bytes=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> SELECTDECODE(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_QUEUE29 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=89Bytes=7654)
3 2 INDEX (RANGE SCAN) OF 'T_TEST1_PK' (UNIQUE) (Cost=2Card=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 processedSQL> 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=89Bytes=7654)
3 2 INDEX (RANGE SCAN) OF 'T_TEST1_PK' (UNIQUE) (Cost=2Card=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 processedSQL> /
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=89Bytes=7654)
3 2 INDEX (RANGE SCAN) OF 'T_TEST1_PK' (UNIQUE) (Cost=2Card=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 processed10G以後，這個問題就被修正了：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 processedSQL> 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