【丁原】分頁sql中普通寫法和rowid寫法的效能比較

idba發表於2008-04-23
SQL

我們都知道，不管你翻多少頁，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|   
----------------------------------------------------------------------------
SELECT STATEMENT                |                 |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--

作者：丁原 | 【轉載時請務必以超連結形式標明文章原始出處和作者資訊及本宣告】
地址：http://rdc.taobao.com/blog/dba/html/134_oracle_page_compare.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/1384/viewspace-247884/，如需轉載，請註明出處，否則將追究法律責任。

相關文章