Home > fundamental > what is rownum?

what is rownum?

long long long ago写过的一篇文章,分享给开发同学的,现在看看解释的还没有到点上,读起来会有点呼之欲出的感觉,吊起了欲可惜却没有出=,=!,需要的人将就着看看先。

rownum在我们平常的查询中很常见,比如分页,限定最大返回记录数等,但是rownum是什么呢?

先看SQL Reference的解释:

ROWNUM Pseudocolumn

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

从上面可以看出两点:首先,rownum是个伪列。其次,rownum不是我们一般意义上的column,它不会在segment中真实存储,只有当cursor获取记录的时候才会生成。注意,是cursor获取一条才生成一条rownum,所以,它不是我们预先在表中存储的值,也不是我们预先定义好的固定值。这个特性让rownum难以捉摸。

其实说rownum难以捉摸也不对,rownum本质上也是遵循了一些固定原则的,简单来讲比如:

1.获取数据时,rownum是按照cursor获取的顺序生成的。也就是获取到符合条件的一条,才生成一个rownum,不会提前生成,这跟表中得column预先就存储在DB里不一样。所以,不同的执行计划会导致不同的rownum生成。

2.rownum在sort之前。也就是说,rownum是oracle获取记录时生成的,跟你排不排序无关,因为排序之前我已经生成好了。这一点的话其实也可以归结为第一点的内容。

归根到底,可以归结为一句话:

ROWNUM是CURSOR获取数据时动态生成的伪列。

举个例子阐述一下,有这样一张表n,10W+1条记录:

SQL> create table n(id number,deptno number,name varchar2(4000));

Table created.

Elapsed: 00:00:00.01
SQL> insert into n values (10001,2,rpad(‘OP’,4000));

1 row created.

Elapsed: 00:00:00.00
SQL> insert into n select level,mod(level,10000),rpad(‘OP’,4000) from dual connect by level<=100000;

100000 rows created.

Elapsed: 00:00:06.06

第一点:rownum是在sort之前生成的。

SQL> select rownum,id from n where id<=10 and rownum<=5 order by id desc;

ROWNUM         ID
———- ———-
5          5
4          4
3          3
2          2
1          1

Elapsed: 00:00:00.01

所以如果想按order by id desc 后获取前5条应该加层嵌套:

SQL> select rownum,id from (select rownum cur,id from n where id<=10 order by id desc) where cur<=5;

ROWNUM         ID
———- ———-
1          5
2          4
3          3
4          2
5          1

Elapsed: 00:00:00.47

第二点:不同的执行计划会导致不同的rownum生成方式。

针对第一点的第一个SQL做个对比:

SQL> create index n_id_ind on n(id);

Index created.

Elapsed: 00:00:00.43
SQL> set autot on exp
SQL> select rownum,id from n where id<=10 and rownum<=5 order by id desc;

ROWNUM         ID
———- ———-
1         10
2          9
3          8
4          7
5          6

Elapsed: 00:00:00.00

Execution Plan
———————————————————-
Plan hash value: 1849867597

—————————————————————————————–
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————–
|   0 | SELECT STATEMENT             |          |     4 |    52 |     1   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |          |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING| N_ID_IND |     4 |    52 |     2   (0)| 00:00:01 |
—————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(ROWNUM<=5)
2 – access(“ID”<=10)

这次走的是INDEX DESC,所以order by id desc会使INDEX从id=10的末尾开始向前扫,省去了ID sort的开销。

所以标准的分页应该包括三层:

select * from (

select a.*,rownum cur_row from (

select * from t where id<:val order by name2 desc) a

where rownum<40)

where cur_row>20;

第三点:子查询带来的效率问题。

考虑这样一个SQL:

select * from (select * from n) where rownum<10;

这样的SQL是不是意味着先全表扫出所有记录作为结果集,再取前10条呢?

其实CBO已经为我们做了一次query transformation: view merging,看执行计划就清楚了:

SQL> select * from (select * from n) where rownum<10;

9 rows selected.

Elapsed: 00:00:00.01

Execution Plan
———————————————————-
Plan hash value: 277183755

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |     9 | 18252 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| N    |     9 | 18252 |     2   (0)| 00:00:01 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – filter(ROWNUM<10)

Statistics
———————————————————-
0  recursive calls
0  db block gets
12  consistent gets
0  physical reads
0  redo size
4792  bytes sent via SQL*Net to client
492  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
9  rows processed

 

可以看到只有12个一致读。如果不能做view merging呢?那就等着收拾杯具吧:

SQL> select * from (select * from n order by deptno desc) where rownum<10;

9 rows selected.

Elapsed: 00:00:00.43

Execution Plan
———————————————————-
Plan hash value: 449158247

——————————————————————————–
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————–
|   0 | SELECT STATEMENT        |      |     9 | 18252 |     3  (34)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |      |       |       |            |          |
|   2 |   VIEW                  |      |    82 |   162K|     3  (34)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY|      |    82 |   162K|     3  (34)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | N    |    82 |   162K|     2   (0)| 00:00:01 |
——————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(ROWNUM<10)
3 – filter(ROWNUM<10)

Statistics
———————————————————-
1  recursive calls
0  db block gets
100021  consistent gets
0  physical reads
0  redo size
4767  bytes sent via SQL*Net to client
492  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
9  rows processed
一致读彪到100021,这样的SQL肯定是不能上线的。针对这种需求,其实加上order列的索引,使它满足view merging的条件就行了。

Categories: fundamental Tags:
  1. No comments yet.
  1. No trackbacks yet.