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