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

但是，如果你希望对一个排序结果取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;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 536364188
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/jakeasd/p/5584576.html