你是否仍迷信rowid分页？

Kevin__Zhang

哈哈，精了。 谢谢斑竹，最近正好肾虚，混个精补补

dingjun123

肾虚？？你一夜八次郎啊

gyhgood

真不错～我一直怀疑rowid分页效率高不太靠谱，又不知道怎么去证明

newkid

很有意思的发现！如果这页的ROWID分散在很多块中，又会回到10G的那种结果？
用ROWID的方法，建议最后加ORDER BY对输出排序。

jboracle1981

good

sj198421

学习下，^_^，实践出真理

Kevin__Zhang

newkid 发表于 2012-4-19 22:09
很有意思的发现！如果这页的ROWID分散在很多块中，又会回到10G的那种结果？
用ROWID的方法，建议最后加ORD ...

是，当11g的表的数据是彻底无序，也就是说集群因子最大化（和表的行数一样多），
这种情况下，11g的rowid分页性能是和10g的rowid分页性能一样的。也是每个rowid都需要产生一个块读。

atgc

consistent gets值大不, 并不等于就一定慢，如果排序分页需要提取所有字段，显然是ROWID分页快

SQL> select count(*) from t100;
  COUNT(*)
----------
    150000

set autot trace
set timi on

SQL> select /*+ all_rows */ *
     from   (select rownum rn,t.*
             from   (select  * from t100 order by id) t
             where  rownum<1000)
     where  rn >900;  
99 rows selected.
Elapsed: 00:00:00.39

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4555  consistent gets
          0  physical reads
          0  redo size
      12772  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         99  rows processed

SQL> select /*+ ordered use_nl(p s) */ *
     from   (select rownum rn,rd
             from   (select rowid rd from t100 order by id) t
             where  rownum<1000) p,
            t100 s
     where rn>900 and p.rd=s.rowid;
99 rows selected.
Elapsed: 00:00:00.10

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4654  consistent gets
          0  physical reads
          0  redo size
      14303  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         99  rows processed
         

Kevin__Zhang

atgc 发表于 2012-4-20 09:17
consistent gets值大不, 并不等于就一定慢，如果排序分页需要提取所有字段，显然是ROWID分页快

SQL> sel ...

此帖主要目的是揭露一个一直被忽略的11g新特性。11g得益于这个新特性，rowid分页基本普遍优于rownum分页。

而在10g中由于没有这个特性，那么要考虑的就很多了。
页后翻的页数，以及页的大小，以及行的长度，以及集群因子，都可能决定了rownum和rowid谁的性能更好。

所以也可以通过调整：页数，页大小，行长度，集群因子 这些参数，来构造不同的实验得到不同结果。

譬如：
select /*+ ordered use_nl(p s) */ *
      from (
            select rownum rn,rd
            from (select  rowid rd from test order by created)
            t where rownum<8000) p,
            test s
      where rn>7000 and p.rd=s.rowid; swp1 >swp1 >  2    3    4    5    6    7
999 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 827357089
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |  7999 |   437K|  8321   (1)| 00:01:57 |
|   1 |  NESTED LOOPS               |           |  7999 |   437K|  8321   (1)| 00:01:57 |
|*  2 |   VIEW                      |           |  7999 |   195K|   316   (1)| 00:00:05 |
|*  3 |    COUNT STOPKEY            |           |       |       |            |          |
|   4 |     VIEW                    |           |   182K|  2138K|   316   (1)| 00:00:05 |
|   5 |      INDEX FULL SCAN        | TEST_IND1 |   182K|  3385K|   316   (1)| 00:00:05 |
|   6 |   TABLE ACCESS BY USER ROWID| TEST      |     1 |    31 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("RN">7000)
   3 - filter(ROWNUM<8000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1081  consistent gets
          0  physical reads
          0  redo size
      49093  bytes sent via SQL*Net to client
       1218  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed



swp1 >select *
       from (
             select rownum rn,t.*
             from
             (select  id,status,type,created from test2 order by created) t
             where rownum<8000)
       where rn >7000;    2    3    4    5    6    7
999 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1795700156
---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |  7999 |   398K|    30   (0)| 00:00:01 |
|*  1 |  VIEW                          |            |  7999 |   398K|    30   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                |            |       |       |            |          |
|   3 |    VIEW                        |            |  7999 |   296K|    30   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| TEST2      |   182K|  4455K|    30   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | TEST2_IND1 |  7999 |       |    12   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">7000)
   2 - filter(ROWNUM<8000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        163  consistent gets
          0  physical reads
          0  redo size
      31838  bytes sent via SQL*Net to client
       1218  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed

可以看到我构造的这个结果：
         rowid     rownum
时间：  00.03    00.01   
块读：   1081    163  



此外，更重要的一点就是，关于你的看执行时间不看 块读 的做法我不认同。
一是因为在我们的实验中全部都是逻辑读。
而要知道，在真正的环境中，这种全表的块读是很容易产生物理读的，而常常多一个物理读的开销就能抵得上千百个逻辑读。
二是因为大部分的系统瓶颈都在IO

atgc

Kevin__Zhang 发表于 2012-4-20 09:50
此帖主要目的是揭露一个一直被忽略的11g新特性。11g得益于这个新特性，rowid分页基本普遍优于rownum分页。 ...

集群因子，不可能每个字段的集群因子都很小。如果一个字段的集群因子小了，别的字段的集群因子就会变大

说明一下，我这里是全表扫描，真实业务中，未必能做到都利用索引
即使物理读也是ROWID分页快，执行速度是客户体验，也是我们需要的效果

SQL> set autot trace
SQL> alter system  flush buffer_cache;

System altered.

SQL> select /*+ all_rows */ *
  2       from   (select rownum rn,t.*
  3               from   (select  * from t100 order by id) t
  4               where  rownum<1000)
  5       where  rn >900;

99 rows selected.

Elapsed: 00:00:00.59

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       4733  consistent gets
       4548  physical reads          0  redo size
      12772  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         99  rows processed

SQL> alter system  flush buffer_cache;

System altered.

SQL> select /*+ ordered use_nl(p s) */ *
          from (
                select rownum rn,rd
                from (select  rowid rd from t100 order by id)
                t where rownum<1000) p,
                t100 s
          where rn>900 and p.rd=s.rowid;

99 rows selected.

Elapsed: 00:00:00.21

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4654  consistent gets
       4548  physical reads          0  redo size
      14303  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         99  rows processed