分页sql优化

摘要：记一次典型的分页sql优化过程

同事有如下分段落引用页sql执行耗时25妙，需要优化。

# 优化思路
拿到分页sql，首先我们先看是否符合分页语句编写规范，这里发现该sql完全符合。
查看执行计划

发现该分页SQL全部走的hash join outer,并没有利用索引已经排序特性、ROWNUM的COUNT STOPKEY 特性以及嵌套循环传值特性来优化。
通过查看sql中各表大小，发现最大的表AMOD_TARGET_ALARM才506,738数据

# 优化方案
通过在t表的ALARM_TIME建立倒序索引，如果ALARM_TIME可以为空，可以按如下方式创建

create index idx_xxx on AMOD_TARGET_ALARM(ALARM_TIME desc,0);

同时用如下hint

这里用no_merge的Hint是为了让内联视图不展开，作为一个整体接收NL的传值。不加这个Hint，有兴趣的读者可以自行测试执行计划怎么走的，后面会给出测试方法。

最终的执行计划如下（同事没有sqlplus，用PLSQL查看的执行计划）

以T表为NL的驱动表，扫描T的ALARM_TIME列的倒序索引20条记录就停止，同时内联视图也走的谓词推入，是我们想要的执行计划，最终该SQL妙杀，0.092秒出结果。

附录
测试方法（我只创建了3张表，掌握方法即可）

--创建表
create table AMOD_TARGET_ALARM as select * from dba_objects;
 
create table  amod_target_orginfo as select * from dba_objects;
 
create table  amod_alarm_smsreceiveinfo as select * from dba_objects;

--创建索引
create index idx_AMOD_TARGET_ALARM_created on AMOD_TARGET_ALARM(created desc,0);
create index amod_target_orginfo_id on amod_target_orginfo(object_id,0);
create index ind_smsreceiveinfo_id on amod_alarm_smsreceiveinfo(object_id,0);

--测试sql,hint按优化方案添加
alter session set statistics_level=all;
SELECT *
	FROM (SELECT temptable.*, ROWNUM ROW_NUM
					FROM (select t.object_id,
											 t.object_type
									from AMOD_TARGET_ALARM t
									left join amod_target_orginfo s
										on t.object_id = s.object_id
									left join (select sm.object_id,
																	 to_char(wmsys.wm_concat(sm.object_type)) people,
																	 to_char(wmsys.wm_concat(sm.object_type)) phone
															from amod_alarm_smsreceiveinfo sm
														 group by object_id) si
										on si.object_id = t.object_id
								 where 1 = 1
								 order by t.created desc) temptable
				 WHERE ROWNUM <= 20)
 WHERE ROW_NUM >= 1;
--可以是看具体的a-rows，用于测试
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

# 总结

以上是该sql优化的过程，有疑问可以留言交流，谢谢。