80-分页查询,不止写法

据孔老先生说,茴香豆的茴字有四种写法,那oracle的分页查询又有多少种写法呢?

分页查询,其实本质上就是topN查询的变种, 如果把topN的一部分结果集去掉,就变成了分页.

topN的基本写法,两层select,第一层先order by,第二层再用rownum:

select owner,object_name,object_id,rownum as rn from(select * from t1 where owner='SYS' order by object_id desc) where rownum<=20;

有些初级开发人员有时会写成:

select owner,object_name,object_id,rownum as rn from t1 where owner='SYS' and rownum<=20 order by object_id desc;

这种写法的逻辑可能存在问题,因为sql解析时会先执行rownum,随机先选出20条记录,再执行排序. 而不是常见业务需要的先排序,再取前20. 如果满足条件的全部结果集<=20, 那么逻辑也是没问题的.

在上面topn的基础上, 再套一层select, 就变成了最常见的标准的三层select的分页查询写法(第一层排序,第二层给rownum取别名,得到topn,第三层去掉topn的前面部分):

select owner,object_name,object_id,rn from

(select a.*,rownum as rn from

(select * from t1 where owner='SYS' order by object_id desc) a where rownum<=20

) where rn>10;

执行计划中看到COUNT STOPKEY 为最佳(没有sort字样).

除了上面比较常见的写法, 还有其他几个不常见的写法:

1层select(12c+才支持的offset 写法,有时可能需要使用hint来纠正优化器执行计划):

select owner,object_name,object_id,rownum as rn

from t1

where owner='SYS'

order by object_id desc

offset 10 rows fetch next 10 rows only;

执行计划中看到WINDOW NOSORT STOPKEY为最佳

2层select,用到了row_number分析函数(可能需要使用hint来纠正优化器执行计划):

SELECT * FROM

(SELECT owner,object_name,object_id,

row_number() over (order by object_id desc) as rn

FROM t1

where owner='SYS'

) WHERE RN<= 20 and RN > 10;

执行计划中看到WINDOW NOSORT STOPKEY为最佳

4层select,对于页数比较大的分页查询,某些情况下可以使用:

with tmp as

(SELECT * FROM

( SELECT rid, ROWNUM as RN

FROM

(SELECT rowid as rid

FROM t1

where owner='SYS'

order by object_id desc

) WHERE ROWNUM <= 500

) WHERE RN > 490

) select /*+ use_nl(a) leading(b) */ owner,object_name,object_id,rn

from t1 a,tmp b

where a.rowid=b.rid;

下面的3层写法,是比较常见的低效分页写法,在分页前结果集大的情况,性能会比较差, 需要避免使用:

select * from

(

select a.*,rownum as rn

from

(select owner,object_name,object_id

from t1

where owner= 'SYS'

order by object_id desc

)a

) where rn>10 and rn<=20;

执行计划一般包含 SORT ORDER BY 的步骤.

掌握了分页写法,只是优化的第一步,下面我们看一个生产案例,SQL代码如下:

这是一个取topn的SQL,先取topn(分页前结果集20万左右),再left join,写法完全没问题,但是执行时间还是比较长,需要24秒:

用hint调下执行计划,执行时间变成1秒:

hint: /*+ monitor leading(p o) push_pred(co@sel$2) */

如果再创建一个core_userprofile表上orgid+UpdateDate+id 3字段联合索引, 那么这个SQL的执行时间估计也就是10毫秒以下了. (从24秒到10毫秒,这种性能的提高,靠硬件是无法实现的,现实中确实有很多类似的SQL,可惜的是,我们很多的决策人员, 只相信高级硬件才能解决性能问题,不知道有这些高级优化技巧)

总结:

分页查询,写法只是第一步,写法正确的基础上,如果执行计划不佳,我们可以通过oracle优化器提供的hint来调整执行计划(不需要改sql代码); 但是如果sql写法不佳,也是没有办法通过调整索引和执行计划进行优化.

写法和索引,是SQL优化的核心,在此基础上通过hint调整执行计划, 是更高级的技术, 需要更进一步的了解优化器特性,以人脑优化器代替电脑优化器.