分页语句SQL优化思路总结-1(分页语句中无谓词条件)

今日,继续总结SQL优化相关原理总结-分页语句。现有如下SQL:

20191117-分页(1)

此SQL的目的是在T表中取出已排序后的10条记录,该SQL中走的是T表的全表扫描执行计划,并且进行排序后才取出10条记录,显然此执行计划并非是最优的,如果表的数据量很大,会造成严重的性能问题(全表扫描+排序),也就是说将表中数据全部扫描处理后再进行排序。但是,该SQL的目的是取出已排序好的10条记录,那么我们是否可以利用INDEX索引有序的特征,扫描到第10条后进行count stopkey停止扫描呢?

因为目标SQL是以OBJECT_ID列作为排序列,我们可以在此列上建立索引,根据索引原理,索引数据本身是有序的,我们可以利用这一特性减少目标SQL对表的访问block

我们将目标SQL先改写为如下形式:

create index t_idx01 on t(object_id,0)

select /*+ gather_plan_statistics */ * from (select /*+ index(t
t_idx01) */ owner,object_id from t order by object_id) where rownum <=10

我们来看一下执行计划:

20191117-分页(2)

我们用HINT强制SQL走了T_IDX01的索引的执行计划,并CBO选择了INDEX_FULL_SCAN索引全扫描,但是我们发现,A-ROWS只扫描了10行数据,处理的总行数等同于我们需要返回的行数,并且执行计划中我们没有看到SORT ORDER BY,在此案例中并没有进行排序,而是利用索引有序的特性,直接扫描INDEX返回即可。

我们将目标SQL改写成如下方式即可:

SELECT /*+ gather_plan_statistics */ *
  FROM (
   SELECT *
      FROM (
    SELECT rownum AS rn, r.owner, r.object_id
   FROM (
    SELECT /*+index(t t_idx01) --分页语句 */ owner, object_id
   FROM t
  ORDER BY object_id) r
 )
 WHERE rownum <= 10)
WHERE rn >= 1;

20191117-分页(3)

犹如预期所愿,目标SQL只扫描处理了10行便COUNT STOPKEY,这是理想的执行计划。

经过以上实验,我们可以得出几个个结论,

(1)分页语句中如果存在排序列(本例中为order by object_id),可以在此列上建立索引,可以避免排序SORT ORDER BY;

(2)例如本文开头的案例,扫描了所有的行数后最后却只要10行数据,那么在错误的分页框架中即使存在索引时,执行计划也会走INDEX FULL SCAN扫描所有的INDEX LEAF BLOCK,所以我们要利用ROWNUM来实现COUNT STOPKEY从而控制扫描的行数,避免扫描多余的行数,尽量只需要处理我们需要返回的行数即可。