分页语句SQL优化思路总结-3(多表JOIN)

前两篇文章中讲述总结了几种分页语句的优化方法后,碰巧生产系统中有如下SQL需要优化,我们看到这个SQL是多表JOIN的需求,也是我们在生产系统中常见的分页语句场景:

经过将分页框架等价改写正确后,生产系统SQL如下:

SELECT  * FROM ( SELECT * FROM (
 SELECT rownum AS rn, r.owner, r.object_id
 FROM ( SELECT t.owner, t.object_id, t1.account_status
 FROM t, t1 WHERE t.owner = t1.username AND t.owner = 'SYS'
 ORDER BY t.owner) r)
 WHERE rownum <= 10)
WHERE rn >= 1;

多表JOJN-1 20191117

  可以从执行计划中看到,T,T1表进行了MERGE JOIN CARTESIAN,逻辑读比较高,虽然此SQL结果集较小,返回速度很快,但是如果结果集很大情况下势必会造成严重性能违背,问题来了,是否可以将此SQL继续优化将逻辑读降下来,变得更快?

我们构造测试表,信息如下图所示:

20191117-分页(12)

我们先在T1表上创建一个索引:

SQL> create index t1_idx01 on t1(username);
 Index created.

将SQL添加如下HINT:

SELECT /*+ gather_plan_statistics */ *
 FROM ( SELECT * FROM (SELECT rownum AS rn, r.owner, r.object_id
 FROM (SELECT /*+ use_nl(t1,t) leading(t1) index(t1 t1_idx01) */ t.owner, t.object_id,t1.account_status
 FROM t,t1 WHERE t.owner=t1.username and t.owner='SYS'
 ORDER BY t.owner) r) WHERE rownum <= 10) WHERE rn >= 1;

我们观察一下执行计划:

多表JOJN-2 20191117

我们看到逻辑读变为各位数,我们指定了T1为 Nested Loop的驱动表,并且避免了目标SQL进行额外的排序工作,并且将处理行数降低,索引只扫描10行数据便停止扫描且满足需求。