小型 网站 源码,东莞网站建设价位,wordpress模板 汉化,php网站开发小程序据孔老先生说,茴香豆的茴字有四种写法,那oracle的分页查询又有多少种写法呢?分页查询,其实本质上就是topN查询的变种, 如果把topN的一部分结果集去掉,就变成了分页.topN的基本写法,两层select,第一层先order by,第二层再用rownum:select owner,object_name,object_id,rownum a… 据孔老先生说,茴香豆的茴字有四种写法,那oracle的分页查询又有多少种写法呢?分页查询,其实本质上就是topN查询的变种, 如果把topN的一部分结果集去掉,就变成了分页.topN的基本写法,两层select,第一层先order by,第二层再用rownum:select owner,object_name,object_id,rownum as rn from(select * from t1 where ownerSYS order by object_id desc) where rownum20;有些初级开发人员有时会写成:select owner,object_name,object_id,rownum as rn from t1 where ownerSYS and rownum20 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 ownerSYS order by object_id desc) a where rownum20 ) where rn10;执行计划中看到COUNT STOPKEY 为最佳(没有sort字样).除了上面比较常见的写法, 还有其他几个不常见的写法:1层select(12c才支持的offset 写法,有时可能需要使用hint来纠正优化器执行计划):select owner,object_name,object_id,rownum as rnfrom t1where ownerSYSorder by object_id descoffset 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 ownerSYS) 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 ownerSYS order by object_id desc ) WHERE ROWNUM 500 ) WHERE RN 490) select /* use_nl(a) leading(b) */ owner,object_name,object_id,rnfrom t1 a,tmp bwhere a.rowidb.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 rn10 and rn20;执行计划一般包含 SORT ORDER BY 的步骤.掌握了分页写法,只是优化的第一步,下面我们看一个生产案例,SQL代码如下:这是一个取topn的SQL,先取topn(分页前结果集20万左右),再left join,写法完全没问题,但是执行时间还是比较长,需要24秒:用hint调下执行计划,执行时间变成1秒:hint: /* monitor leading(p o) push_pred(cosel$2) */如果再创建一个core_userprofile表上orgidUpdateDateid 3字段联合索引, 那么这个SQL的执行时间估计也就是10毫秒以下了. (从24秒到10毫秒,这种性能的提高,靠硬件是无法实现的,现实中确实有很多类似的SQL,可惜的是,我们很多的决策人员, 只相信高级硬件才能解决性能问题,不知道有这些高级优化技巧)总结: 分页查询,写法只是第一步,写法正确的基础上,如果执行计划不佳,我们可以通过oracle优化器提供的hint来调整执行计划(不需要改sql代码); 但是如果sql写法不佳,也是没有办法通过调整索引和执行计划进行优化. 写法和索引,是SQL优化的核心,在此基础上通过hint调整执行计划, 是更高级的技术, 需要更进一步的了解优化器特性,以人脑优化器代替电脑优化器. 想提高SQL优化技能,看完我的线上培训课程(索引专题,SQL写法与改写专题)会大有帮助.
