分页语句SQL优化思路总结-2(分页语句中存在谓词条件)

继《分页语句SQL优化思路总结-1(分页语句中无谓词条件)》中我们实验了目标分页SQL存在排序的优化,该分页SQL是不存在谓词过滤条件的“select owner,object_id from t order by object_id”,但是我们99%的情况遇到的应用SQL都是存在谓词过滤条件,例如”select owner,object_id from t where owner=’TRAVELSKYDBA’ order by object_id;”的情况。我们上文提到,可以利用索引有序特性将object_id(排序列)创建索引,但是如果遇到等值查询,我们是否仍需要将排序列创建索引呢?我们继续试验论证,我们来看一下测试数据表T的OWNER列的数据分布:

20191117-分页(4)

我们执行如下目标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 WHERE OWNER='TRAVELSKYDBA'
    ORDER BY object_id) r
  )
   WHERE rownum <= 10)
WHERE rn >= 1;

20191117-分页(5)

通过之前此表的数据分布来看,在等值过滤时,谓词条件为’TRAVELSKYDBA’,过滤条件选择性非常好,但是我需求的10条数据需要过滤数据表中的绝大部分数据,所以执行INDEX FULL SCAN时,只处理需求数据的10行是无法过滤出谓词条件的,要扫描大量数据才可以。我们还看到,因未创建组合索引造成的典型的回表再过滤,索引扫描601K,回表最终过滤10行数据,执行计划是非常亏的。

我们将谓词改变为SYS: 20191117-分页(6)

我们从真实的执行计划中看到,SQL索引全扫描只扫描处理了10行数据,是我们理想中的执行计划,因为SYS这个谓词条件不需要扫描过多的数据就可以匹配到。那么问题来了,谓词选择性好变成了一种负面问题,我们上文提到索引扫描601K,回表过滤10行数据,执行计划是非常亏的,我们应该如何优化?

那么我们创建组合索引避免回表:

create index t_idx02 on t(object_id,owner);

20191117-分页(7)   我们看到执行计划中,走了索引跳跃扫描(INDEX SKIP SCAN)扫描了10行,1807个逻辑读,1804个物理读,这是最优的执行计划吗?

create index t_idx03 on t(owner,object_id);

20191117-分页(8) 我们看到,最优的执行计划出现了,逻辑读仅为4,在等值过滤的分页语句中,如果存在排序列且结果集非常大时(避免大量数据排序),可以将谓词条件与排序列创建组合索引,并且组合索引中的列顺序要遵循如下原则:

  create index  index_name on(等值过滤,排序列);

我们来测试一下非等值过滤的情况:

创建索引:create index t_idx04 on t(owner,object_type,object_id);

20191117-分页(9)

执行计划中出现SORT ORDER BY,索引创建顺序不能避免排序,执行计划并非最优。

创建索引:create index t_idx05 on t(owner,object_id,object_type); 

20191117-分页(10)

我们看到,按照等值过滤,排序列,非等值过滤顺序创建的组合索引仅仅需要4个buffer get,并且我们避免了排序的工作,是最优的执行计划。当创建索引且存在非等值过滤时,需要将非等值过滤列放在排序列的后面。

create index  index_name on(等值过滤,排序列,非等值过滤);