Oracle中ROWNUM的使用技巧

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2009-02-14 14:54:36

分享到 

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)    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           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 processed   SQL> /     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           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 processed   10G以后，这个问题就被修正了：   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 processed   SQL> select * from t_test1   2  where rownum <= 1;   <