分页sql中普通写法和rowid写法的性能比较

我们都知道，不管你翻多少页，rowid写法消耗的性能基本是恒定的，逻辑读不会有很大的增加，普通写法随着你翻页的页数增加，逻辑读成指数倍的增大。一般来说，大部分人关注只是的前面几页，甚至只是第一页的查询结果。那么，对于只关注第一页的应用来说，rowid写法除了要回表之外，比普通写法多做了一次关联查询，性能上肯定要差一点的，那么，到底差多少呢？
1.普通写法

SQL> set autot traceonly
SQL>select *
    from (select row_.*, rownum rownum_
            from (select *
                    from order_detail
                   where item_id = 182945
                     and deleted = 0
                   order by gmt_create desc) row_
           where rownum <= 50)
   where rownum_ >= 1;
----------------------------------------------------------------------------Operation                      |Name         |Rows|Bytes |Cost(%CPU)| Time|   
----------------------------------------------------------------------------
SELECTSTATEMENT                |                 |12|42216 |6(0)|00:00:01 |  VIEW                           |                 |12|42216|6(0)|00:00:01 | 
  COUNT STOPKEY                 |                 |  |      |    |         |
   VIEW                         |                 |12|42060 |6(0)|00:00:01 |
    TABLE ACCESS BY INDEX ROWID |ORDER_DETAIL     |12| 5724 |6(0)|00:00:01 |
     INDEX RANGE SCAN DESCENDING|IDX_ORDER_DET_ITE|12|      |1(0)|00:00:01 |
----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         51  consistent gets
          0  physical reads
          0  redo size
       5412  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

结果：普通写法第一页消耗了51个逻辑读。
2.rowid写法

SQL> select *
  2     from (select rid
  3             from (select r.rid, rownum linenum
  4                     from (select rowid rid
  5                             from order_detail
  6                            where item_id = 182945
  7                              and deleted = 0
  8                            order by gmt_create desc) r
  9                    WHERE rownum <= 50)
 10            WHERE linenum >= 1) t1,
 11          order_detail t2
 12    where t1.rid = t2.rowid;
------------------------------------------------------------------------------
| Operation                     | Name          |Rows|Bytes|Cost(%CPU)|Time  |
------------------------------------------------------------------------------
SELECT STATEMENT                |                 |12|6168 |13  (0)|00:00:01 |
 NESTED LOOPS                   |                 |12|6168 |13  (0)|00:00:01 |
  VIEW                          |                 |12| 300 | 1  (0)|00:00:01 |
   COUNT STOPKEY                |                 |  |     |       |         |
    VIEW                        |                 |12| 144 | 1  (0)|00:00:01 |
     INDEX RANGE SCAN DESCENDING| IDX_ORDER_DET_IT|12| 288 | 1  (0)|00:00:01 |
  TABLE ACCESS BY USER ROWID    | ORDER_DETAIL    | 1| 489 | 1  (0)|00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         57  consistent gets
          0  physical reads
          0  redo size
       6108  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

结果：rowid写法第一页消耗了57个逻辑读。
从上面的实验结果来看，rowid要比普通写法多消耗6个逻辑读，影响还是挺大的。对于有些sql语句，动不动每小时执行300万次之上，使用rowid写法就是多消耗1800万以上的逻辑读，这个还是值得我们去衡量的。

注：
1.这个实验仅仅是分页中第一页的性能比较，随着分页的增加，rowid的优势会逐渐体现出来
2.6个逻辑读仅仅是这个sql的差距，不同的sql可能是不一样的
3.rowid写法的逻辑读随着分页次数增加也会逐渐增大，因为扫描索引是省不了的，不过增加的逻辑读块很少

--EOF--