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