你是否仍迷信rowid分页？

Kevin__Zhang

一直以来，看到的说法大都是rowid要强于rownum分页：
在索引设计的没问题的前提下，两种方式在前几页性能上接近，越向后翻rowid的效果越明显。

注意红字部分，红字部分一定成立吗？实验说明一切。

我们先来看看11g的情况，建立实验环境：
11gR2 >create table test(id number,status VARCHAR2(7),type VARCHAR2(19),created date);
Table created.
11gR2 >insert into test select OBJECT_ID,STATUS,OBJECT_TYPE,CREATED from dba_objects;
12926 rows created.
11gR2 >alter table test modify created not null;
Table altered.
11gR2 >create index test_ind1 on test(CREATED);
Index created.
11gR2 >ANALYZE TABLE TEST compute statistics;
Table analyzed.

测试11g的rownum分页：
11gR2 >select *
       from (
             select rownum rn,t.*
             from
             (select  id,status,type,created from test order by created) t
             where rownum<1000)
       where rn >900;  
99 rows selected.
--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |   999 | 50949 |     9   (0)| 00:00:01 |
|*  1 |  VIEW                          |           |   999 | 50949 |     9   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                |           |       |       |            |          |
|   3 |    VIEW                        |           |   999 | 37962 |     9   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| TEST      | 12926 |   277K|     9   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | TEST_IND1 |   999 |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
       3703  bytes sent via SQL*Net to client
        590  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed

下面测试rowid分页
11gR2 >select /*+ ordered use_nl(p s) */ *
      from (
            select rownum rn,rd
            from (select  rowid rd from test order by created)
            t where rownum<1000) p,
            test s
      where rn>900 and p.rd=s.rowid;  2    3    4    5    6    7
99 rows selected.
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   999 | 52947 |  1003   (0)| 00:00:13 |
|   1 |  NESTED LOOPS               |           |   999 | 52947 |  1003   (0)| 00:00:13 |
|*  2 |   VIEW                      |           |   999 | 24975 |     4   (0)| 00:00:01 |
|*  3 |    COUNT STOPKEY            |           |       |       |            |          |
|   4 |     VIEW                    |           |   999 | 11988 |     4   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN        | TEST_IND1 | 12926 |   239K|     4   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY USER ROWID| TEST      |     1 |    28 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
       5450  bytes sent via SQL*Net to client
        590  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed

我们看可以看到，当读900-1000行时，rowid分页（19逻辑读）确实是优于rownum分页（22逻辑读）。因为rowid方式减少了前900行的回表。

我们来看看10g的情况。
在10g建立同样的TEST表和索引，然后：
测试10g的rownum分页：
10gR2 >select *
       from (
             select rownum rn,t.*
             from
             (select  id,status,type,created from test order by created) t
             where rownum<1000)
       where rn >900;  
99 rows selected.
--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |   999 | 50949 |    17   (0)| 00:00:01 |
|*  1 |  VIEW                          |           |   999 | 50949 |    17   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                |           |       |       |            |          |
|   3 |    VIEW                        |           |   999 | 37962 |    17   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| TEST      | 45620 |  1113K|    17   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | TEST_IND1 |   999 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
       3842  bytes sent via SQL*Net to client
        558  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed

19个逻辑读，和11g是很接近的，是个正常值，也符合我们的预期。

继续测试10g的rowid分页：
swp1 >select /*+ ordered use_nl(p s) */ *
      from (
            select rownum rn,rd
            from (select  rowid rd from test order by created)
            t where rownum<1000) p,
            test s
      where rn>900 and p.rd=s.rowid;
  2    3    4    5    6    7
99 rows selected.
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   999 | 55944 |  1061   (1)| 00:00:15 |
|   1 |  NESTED LOOPS               |           |   999 | 55944 |  1061   (1)| 00:00:15 |
|*  2 |   VIEW                      |           |   999 | 24975 |    62   (2)| 00:00:01 |
|*  3 |    COUNT STOPKEY            |           |       |       |            |          |
|   4 |     VIEW                    |           | 45620 |   534K|    62   (2)| 00:00:01 |
|   5 |      INDEX FULL SCAN        | TEST_IND1 | 45620 |   846K|    62   (2)| 00:00:01 |
|   6 |   TABLE ACCESS BY USER ROWID| TEST      |     1 |    31 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        109  consistent gets
          1  physical reads
          0  redo size
       5585  bytes sent via SQL*Net to client
        558  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed

怎么逻辑读变成了109？ 执行计划是和11g一样的。为什么会有这么大的差别？
rowid分页的cost一下变成了rownum方式的好几倍。

为什么？

下面的实验揭露了原因，也为我们揭露了11g一个不为人知的新特性：
11gR2 >select rowid,id from test where rowid in ('AAADSHAABAAAH3hAAA','AAADSHAABAAAH3hAAB','AAADSHAABAAAH3hAAC','AAADSHAABAAAH3hAAD','AAADSHAABAAAH3hAAE','AAADSHAABAAAH3hAAF','AAADSHAABAAAH3hAAG','AAADSHAABAAAH3hAAH','AAADSHAABAAAH3hAAI','AAADSHAABAAAH3hAAJ');
ROWID                      ID
------------------ ----------
AAADSHAABAAAH3hAAA         20
AAADSHAABAAAH3hAAB         46
AAADSHAABAAAH3hAAC         28
AAADSHAABAAAH3hAAD         15
AAADSHAABAAAH3hAAE         29
AAADSHAABAAAH3hAAF          3
AAADSHAABAAAH3hAAG         25
AAADSHAABAAAH3hAAH         41
AAADSHAABAAAH3hAAI         54
AAADSHAABAAAH3hAAJ         40
10 rows selected.
------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    16 |     1   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR            |      |       |       |            |          |
|   2 |   TABLE ACCESS BY USER ROWID| TEST |     1 |    16 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        875  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

10gR2 >select rowid,id from test where rowid in ('AAAzo5AABAAAKnWAAA','AAAzo5AABAAAKnWAAB','AAAzo5AABAAAKnWAAC','AAAzo5AABAAAKnWAAD','AAAzo5AABAAAKnWAAE','AAAzo5AABAAAKnWAAF','AAAzo5AABAAAKnWAAG','AAAzo5AABAAAKnWAAH','AAAzo5AABAAAKnWAAI','AAAzo5AABAAAKnWAAJ');
ROWID                      ID
------------------ ----------
AAAzo5AABAAAKnWAAA         30
AAAzo5AABAAAKnWAAB          8
AAAzo5AABAAAKnWAAC         14
AAAzo5AABAAAKnWAAD         34
AAAzo5AABAAAKnWAAE         45
AAAzo5AABAAAKnWAAF         39
AAAzo5AABAAAKnWAAG         47
AAAzo5AABAAAKnWAAH         51
AAAzo5AABAAAKnWAAI         11
AAAzo5AABAAAKnWAAJ         48
10 rows selected.
------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    16 |     1   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR            |      |       |       |            |          |
|   2 |   TABLE ACCESS BY USER ROWID| TEST |     1 |    16 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        861  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)
         10  rows processed

我们看到，同样用rowid读取同一个block的10行，在11g中仅仅耗费了2个读，而在10g中耗费10个读。
更进一步的测试也确认了10g中，每一个rowid都会产生一个逻辑读，即使这些rowid来自一个block，也不能重用。
而11g引入了对rowid读的新特性， 那就是，如果rowid是来自一个block，那么是可以重用的。

总结：
在11g中，得益于这个11g不为人知的新特性，总体来说，rowid分页要优于rownum。
可是在10g中，相当一部分情况下rowid方式性能是远远不如rownum方式的。只有在后翻的页数非常大，并且页的大小很小，譬如10000-10010的情况下，rowid的分页才会优于rownum方式。


（PS：之前发了疑问在管理版，貌似不是很多人关注，重新整理了实验发在开发版，求讨论和斧正。）

Kevin__Zhang

测试环境分别为：
11gR2 >select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production


10gR2 >select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

dingjun123

perfect job

Kevin__Zhang

thx DJ

〇〇

怎么知道是一个块的rowid?

Kevin__Zhang

〇〇 发表于 2012-4-19 14:00
怎么知道是一个块的rowid?

ROWID                      ID
------------------ ----------
AAAzo5AABAAAKnWAAA         30
AAAzo5AABAAAKnWAAB          8
AAAzo5AABAAAKnWAAC         14
AAAzo5AABAAAKnWAAD         34
AAAzo5AABAAAKnWAAE         45
AAAzo5AABAAAKnWAAF         39
AAAzo5AABAAAKnWAAG         47
AAAzo5AABAAAKnWAAH         51
AAAzo5AABAAAKnWAAI         11
AAAzo5AABAAAKnWAAJ         48

从rowid的名字就可以看出出来，一个rowid是这样组成的：
OOOOOOFFFBBBBBBRRR，占用10个字节（32bit+10bit rfile#+22bit+16bit）。其中，O是对象ID，F是文件ID，B是块ID，R是行ID

所以我们可以看到以上的那些rowid，前面的对象号，文件号，块号都是一样的。只有行号有区别。
所以说是一个block的。

〇〇

Kevin__Zhang 发表于 2012-4-19 14:03
ROWID                      ID
------------------ ----------
AAAzo5AABAAAKnWAAA         30

我的测试怎么还是逻辑读偏多
SQL> create table test as select level l from dual connect by level<=100;

表已创建。
  1* select rowid,l from RK.test where rownum<=15
SQL> /

ROWID                       L
------------------ ----------
AAAfbIAAMAAN/17AAA          1
AAAfbIAAMAAN/17AAB          2
AAAfbIAAMAAN/17AAC          3
AAAfbIAAMAAN/17AAD          4
AAAfbIAAMAAN/17AAE          5
AAAfbIAAMAAN/17AAF          6
AAAfbIAAMAAN/17AAG          7
AAAfbIAAMAAN/17AAH          8
AAAfbIAAMAAN/17AAI          9
AAAfbIAAMAAN/17AAJ         10
AAAfbIAAMAAN/17AAK         11
AAAfbIAAMAAN/17AAL         12
AAAfbIAAMAAN/17AAM         13
AAAfbIAAMAAN/17AAN         14
AAAfbIAAMAAN/17AAO         15

已选择15行。


执行计划
----------------------------------------------------------
Plan hash value: 3931117773

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    15 |   375 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST |   100 |  2500 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=15)

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       1000  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed

SQL> show release
release 1102000200
SQL> c/15/20
  1* select rowid,l from RK.test where rownum<=20
SQL> /

ROWID                       L
------------------ ----------
AAAfbIAAMAAN/17AAA          1
AAAfbIAAMAAN/17AAB          2
AAAfbIAAMAAN/17AAC          3
AAAfbIAAMAAN/17AAD          4
AAAfbIAAMAAN/17AAE          5
AAAfbIAAMAAN/17AAF          6
AAAfbIAAMAAN/17AAG          7
AAAfbIAAMAAN/17AAH          8
AAAfbIAAMAAN/17AAI          9
AAAfbIAAMAAN/17AAJ         10
AAAfbIAAMAAN/17AAK         11
AAAfbIAAMAAN/17AAL         12
AAAfbIAAMAAN/17AAM         13
AAAfbIAAMAAN/17AAN         14
AAAfbIAAMAAN/17AAO         15
AAAfbIAAMAAN/17AAP         16
AAAfbIAAMAAN/17AAQ         17
AAAfbIAAMAAN/17AAR         18
AAAfbIAAMAAN/17AAS         19
AAAfbIAAMAAN/17AAT         20

已选择20行。


执行计划
----------------------------------------------------------
Plan hash value: 3931117773

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    20 |   500 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST |   100 |  2500 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=20)

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
       1295  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL> c/20/2
  1* select rowid,l from RK.test where rownum<=2
SQL> /

ROWID                       L
------------------ ----------
AAAfbIAAMAAN/17AAA          1
AAAfbIAAMAAN/17AAB          2


执行计划
----------------------------------------------------------
Plan hash value: 3931117773

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    50 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST |   100 |  2500 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=2)

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        675  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>

Kevin__Zhang

两点：
1.select rowid,l from RK.test where rownum<=15；
这种查询并不是文中所说的通过rowid去访问表，这只是访问表取出了rowid。

2.逻辑读大是由于
  7  recursive calls

重复执行下消除：
  7  recursive calls

Kevin__Zhang

要对比的话可以这样，在10g中：

swp1 >create table test as select level l from dual connect by level<=100;
Table created.

swp1 >select rowid,l  from test where rownum<=10;    ----- 这正是正常的全表扫描和通过索引访问block的方式。  通过读block获得了rowid
ROWID                       L
------------------ ----------
AAAzqpAABAAAKnWAAA          1
AAAzqpAABAAAKnWAAB          2
AAAzqpAABAAAKnWAAC          3
AAAzqpAABAAAKnWAAD          4
AAAzqpAABAAAKnWAAE          5
AAAzqpAABAAAKnWAAF          6
AAAzqpAABAAAKnWAAG          7
AAAzqpAABAAAKnWAAH          8
AAAzqpAABAAAKnWAAI          9
AAAzqpAABAAAKnWAAJ         10
10 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          1  physical reads
          0  redo size
        860  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)
         10  rows processed


以上面显示的rowid进行以下查询：
swp1 >select rowid,l  from test where  rowid in ('AAAzqpAABAAAKnWAAA','AAAzqpAABAAAKnWAAB','AAAzqpAABAAAKnWAAC','AAAzqpAABAAAKnWAAD','AAAzqpAABAAAKnWAAE','AAAzqpAABAAAKnWAAF','AAAzqpAABAAAKnWAAG','AAAzqpAABAAAKnWAAH','AAAzqpAABAAAKnWAAI','AAAzqpAABAAAKnWAAJ');  ----- 这是通过rowid去读block，也是rowid分页调优的方式

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        860  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)

Kevin__Zhang

开始只是4 VS 10，不明显，如果是用:
swp1 >select rowid,l  from test where rownum<=100;
对比：
swp1 >select rowid,l  from test where rowid in （。。。）
更明显，可能是10 VS 100的差距。