Oracle分页语句优化思路


---分页语句优化思路
一、单表分页语句优化思路
1.分页语句框架是否正确
错误的框架
select *
from (select t.*,rownum rn from (需要分页的SQL) t)
where rn>=1
and rn<=10;

正确的分页框架
select *
from (select *
from (select a.*,rownum rn
from (需要分页的SQL) a)
where rownum<=10)
where rn >=1;

**************************************
如果SQL没有过滤条件,只有排序,我们可以利用已经排序的特性来优化分页语句的SQL;即消除执行计划中的 SORT ORDER BY
***如果拍序列允许为null,我们创建索引的时候,需要给这个索引中添加一个常量(可以是 0 1 2 3 或者 英文字母),索引不能存储空值!添加常量可以让索引存储空值;


create index idx_name on test (onject_name,0);

**************************************
错误的分页框架导致性能变差的原因:
错误的分页框架没有 COUNT STOPKEY(where rownum <=...)功能,COUNT STOPKEY 就是当扫描到指定行数的数据之后,SQL就停止运行了

*************************************
如果SQL有等值过滤也有order by ,这时 就需要将过滤列包含在索引中,创建组合索引;---等值列在前,过滤列在后,避免边扫描边过滤的情况
***如果过滤列能过滤掉大部分数据,不将拍序列包含在索引中也可以***
***在实际生产中,一般过滤列都是绑定变量,无法控制传入的参数究竟是那个值,不能确定返回数据的多少,建议最好将拍序列包含在索引中

************************************
如果拍序列有多个列,创建索引的时候,需要将所有的拍序列都包含在索引中!!!
注意:
拍序列先后顺序(跟SQL语句中顺序一致);
拍序列是升序还是降序;
如果分页语句中拍序列只有一个,且是降序显示,创建索引的时候就没必要降序了,可以使用HINT:index_desc 让索引降序扫描

***********************************
创建组合索引顺序
1>等值列在前,过滤列在后
2>过滤列在前,非等值列在后
即:等值列>拍序列>非等值列

***如果分页语句没有排序,可以直接利用 rownum的 COUNT STOPKEY 特性优化SQL

二、多表关联优化思路

1.索引已经排序的特性
2.ROWNUM 的 COUNT STOPKEY特性
3.嵌套循环传值特性(驱动表走的排序列的索引)/*+index (t2 idx_name) leading (t2) use_nl(t2,t1) */
***注意:如果走HASH,两表关联之后的结果,无法保证是有序的,需要关联后在排序(STOR ORDER BY ),也不能走排序合并连接
***嵌套循环是驱动表传值给被驱动表,如果驱动表返回的数据是有序的,关联之后的结果集也是有序的

**************************************
如果分页语句的拍序列来自多个表,这就需要等两表关联之后在进行排序,无法消除 STOR ORDER BY ,也就无法优化,只能走HASH 连接
想优化排序列来自多个表,需要开发和业务沟通,去掉一个排序列
**************************************
如果两表关联是外连接,当两表进行嵌套循环的时候,驱动表只能是主表;
如果嵌套循环的主表和排序列不是同一个表,则分页语句无法优化,只能走HASH;
想要优化,只能是让嵌套循环的驱动表的列作为排序列;

***************************************
分页语句中不能有 distinct , group by , max,avg,union,union all 等关键字;
如果有这些关键字,需要等表关联或者数据都跑完之后再来分页,但是性能很差;


****总结分页语句优化思路****
1.多表关联分页语句,如果有排序,只能对其中一个表进行排序
2.让排序列的表作为嵌套循环的驱动表
3.控制驱动表返回的数据顺序与排序的顺序一致
4.其余表的连接列要创建好索引
5.如果有外连接,只能选择主表的列作为排序列
6.语句中不能有 distinct , group by , max,avg,union,union all
7.执行计划中不能出现 SORT ORDER BY

 

posted @   钱若梨花落  阅读(747)  评论(0编辑  收藏  举报
(评论功能已被禁用)
编辑推荐:
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示