注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

帅小伙的博客

希望能在这里交到更多的朋友

 
 
 

日志

 
 
 
 

分页查询的优化  

2008-02-25 17:04:00|  分类: oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

今天总结一下分页查询,下面几个链接是yangtingkun blog上的测试,写的很详细!

Oracle分页查询语句(1)

http://yangtingkun.itpub.net/post/468/100278

Oracle分页查询语句(2)

http://yangtingkun.itpub.net/post/468/101703

Oracle分页查询语句(3)

http://yangtingkun.itpub.net/post/468/104595

Oracle分页查询语句(4)

http://yangtingkun.itpub.net/post/468/104867

 

下面是对优化的进一步总结和补充:

首先,创建测试表

create table t2 as select * from dba_objects;

create idx idx-t2 on t2(object_id);

最早使用分页方法:

SQL> set autotrace on
SQL> /

        RN  OBJECT_ID OBJECT_NAME
---------- ---------- ----------------------------------------------------------
        96         97 ACCESS$
        97         98 AUD$
        98         99 LINK$
        99        100 TRUSTED_LIST$
       100        101 PROPS$


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   VIEW
   2    1     COUNT
   3    2       VIEW
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'T2'
   5    4           INDEX (RANGE SCAN) OF 'IDX_T2' (NON-UNIQUE)

 


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        346  consistent gets
          0  physical reads
          0  redo size
        604  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

 

我们发现,逻辑读为346 ,做第一次改进,将rn<=100移到第二层循环:

SQL> select rn,object_id,object_name from
  2  (
  3  select rownum rn,a.* from
  4   (
  5     select * from t2   where object_id<500 order by object_id
  6   ) a where rownum<100
  7   )
  8   where rn>95;

        RN  OBJECT_ID OBJECT_NAME
---------- ---------- ----------------------------------------------------------
        96         97 ACCESS$
        97         98 AUD$
        98         99 LINK$
        99        100 TRUSTED_LIST$


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   VIEW
   2    1     COUNT (STOPKEY)
   3    2       VIEW
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'T2'
   5    4           INDEX (RANGE SCAN) OF 'IDX_T2' (NON-UNIQUE)

 


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         63  consistent gets
          0  physical reads
          0  redo size
        585  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

我们发现,逻辑读降到了63,执行计划中用到了stopkey,也就是排序的时候只排到前100条就停止。

上面的改进中我们发现,排序虽然只排到了前100个,但是仍然有95条记录是做的无用功(因为只输出5条记录),这样,翻的页数越大,做的无用功越多,效率自然也越低,这时,我们可以做进一步的改进:

SQL> select rn,t2.object_id,object_name from t2,
  2  (
  3  select * from
  4  (
  5  select rownum rn,a.* from
  6  (
  7   select rowid rd,object_id from t2  where object_id<500 order by object_id
  8   ) a where rownum<100
  9   ) where rn>95
 10   )c
 11   where t2.rowid=c.rd;

        RN  OBJECT_ID OBJECT_NAME
---------- ---------- ----------------------------------------------------------
        96         97 ACCESS$
        97         98 AUD$
        98         99 LINK$
        99        100 TRUSTED_LIST$


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     VIEW
   3    2       COUNT (STOPKEY)
   4    3         VIEW
   5    4           INDEX (RANGE SCAN) OF 'IDX_T2' (NON-UNIQUE)
   6    1     TABLE ACCESS (BY USER ROWID) OF 'T2'

 


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        585  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

 

此时,我们直接对索引进行排序后的提取,然后用rowid和原表进行表关联,因为索引是拍好序的,因此对索引的rowid的提取非常快,提取出的rowid和原表做nested loop也非常快(rowid查询是最快的方法),因此,我们发现,逻辑读居然降到了只有7,大大提高了分页的查询速度。

 

 

 

 

  评论这张
 
阅读(185)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018