27 分页语句优化--优化主题系列

最新推荐文章于 2024-04-19 12:24:55 发布

leo0805leo0805

最新推荐文章于 2024-04-19 12:24:55 发布

阅读量336

点赞数

本文链接：https://blog.csdn.net/leo0805/article/details/79005625

版权

SQL Tuning 专栏收录该内容

29 篇文章 8 订阅

订阅专栏

分页语句一般都有orderby column desc/asc

或者说分页语句里直接就是where rownum < xxx and rn > xx

一般只返回20行或者50行一般不会一页显示超过100行

分页语句的优化技巧：

1.分页SQL要想快最好走索引根据orderby asc/desc 用hintindex_asc/index_desc

强制它走索引INDEXFULL SCAN DESCENDING/INDEX RANGE SCANDESCENDING

2.不要让Oracle扫描整个index确保只扫描一部分索引块然后回表就取得数据然后就countstopkey

3.一定要确保索引回表的时候不要在表上再去过滤也就是说TABLEACCESS BY INDEX ROWID

上面没* 不然就可能扫描整个索引块根据where条件可以创建组合索引(分区表要创建globalindex)

4.看执行计划的时候不要被执行计划的基数给骗了----它可能显示是有误差的

分页方法：

1.rownum

2.row_numberover(order by column) as 行号

3.ROWID分页

12C用limit了

做个试验：

createtable page as select * from dba_objects;

createindex idx_page on page(object_id);

createindex idx_page_1 on page(owner,object_id);

createindex idx_page_2 on page(owner);

createindex idx_page_3 on page(object_id,owner);

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',

tabname => 'PAGE',

estimate_percent => 100,

method_opt => 'for all columns size skewonly',

no_invalidate => FALSE,

degree => 4,

cascade => TRUE);

END;

altersession set optimizer_features_enable='10.2.0.1'; --优化器参数设置为10g

altersession set statistics_level=all;

select* from (select * from( select a.*,rownum rn from page a where object_id>1000and owner='SYS' order by object_id desc) where rownum<=20)where rn>=0;

select* from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

因为OWNER='SYS'以及OBJECT_ID>1000 选择性都很低

执行计划走的是全表扫描分页语句绝对不能让它走全表扫描现在加个HINT

select* from (select * from(select /*+index(a) */ a.*,rownum rn from page a whereobject_id>1000 and owner='SYS' order by object_id desc) whererownum<=20)where rn>=0;

select* from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

你们看到分页语句里面有SORTORDER BY关键字就是错误的现在加上INDEX_DESCHINT

select* from (select * from( select /*+index_desc(a idx_page_3) */a.*,rownum rn frompage a where object_id>1000 and owner='SYS' order by object_id desc) whererownum<=20)where rn>=0;

select* from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

A-ROWS列发现前两个是要扫描所有数据最后才停止第三个则只扫描20行就停止了请注意Buffers列变化

索引本身是不是已经排序过的？？索引结构对吧

分页语句里面如果有orderby某个列是不是要把这个orderby的列放入到索引里面去？？

select* from (select * from( select a.*,rownum rn from page a where object_id>1000and owner='SYS' order by object_id desc) where rownum<=20)where rn>=0;

有两个过滤条件一个是owner一个是object_id

创建索引一定要包含where过滤条件并且包含orderby的列即建立组合索引包含这两个列IDX_PAGE_3

orderby 是不是desc？？那么扫描索引的时候是不是要从后往前扫描倒着扫

是不是只扫描20行数据停止就返回？？扫描索引高度加一个块有可能就扫描三个块

假如表为1亿行数据最优的是只取20行就停止如果不优化是不是要扫描1亿行？？全扫1亿行即使走索引也很坑

分页第一页扫描20个块那么第二页扫描40个块越往后越慢

可在程序里判断如扫描最后一页那就反着来只扫描20个块但中间部分是最慢的

sortorder by stopkey 这个关键字要消除如果分页语句中有这个性能一定很差说明根本没走索引

orderby a.xx,b.xx这种只能创建MV 把他们当成一个表然后再在MV上创建索引select MV order by xx

select* from a,b where a.id=b.id order by a.owner;

a有1亿行数据b有1kw行数据按一页20行如何优化？？

此处a表驱动b在a.owner上和b.id列建索引 owner选择性低但为了分页性能高必须这么搞a.id列不建索引create index idx_1 on a(owner);

createindex idx_2 on b(id);

select* from a,b where b.id=a.id(+) order by a.owner;

不能优化驱动表是Border by A 此时优化只能建MV 但建议跟业务商议改为orderby B表列

如果where过滤条件很多那要把所有列全部放到组合索引中如：

select* from test where id>1000 and owner='SYS' order by xxx;

如果建组合索引只包含owner,xxx是不是需要回表再去检查id>1000

第一页要最优时只扫描索引块中20行就停止因要根据id列过滤索引中没包含id列是不是要将索引块全部扫描了

分页语句建索引一定要包含所有where列+ order by列

如果orderby a.id,a.xx同一个表的orderby是不是要按照先后顺序创建索引

id=5处rownum<=20id=6就返回20行数据

索引有很多叶子块假设一个块有100条数据分页只取20行类似谓词推入只返回20行

select* from (select * from(select /*+index_desc(a idx_page_3) */a.*,rownum rn frompage a where object_id>1000 and owner='SYS' order by object_id desc) whererownum<=20)where rn>=0;

案例<分页语句>优化下面分页语句：