【丁原】分页sql中普通写法和rowid写法的性能比较
上一篇 /
下一篇 2008-04-17 12:56:47
我们都知道，不管你翻多少页，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--
导入论坛
引用链接
收藏
分享给好友
推荐到圈子
管理
举报
TAG:
