前两篇文章中讲述总结了几种分页语句的优化方法后,碰巧生产系统中有如下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;
可以从执行计划中看到,T,T1表进行了MERGE JOIN CARTESIAN,逻辑读比较高,虽然此SQL结果集较小,返回速度很快,但是如果结果集很大情况下势必会造成严重性能违背,问题来了,是否可以将此SQL继续优化将逻辑读降下来,变得更快?
我们构造测试表,信息如下图所示:
我们先在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;
我们观察一下执行计划:
我们看到逻辑读变为各位数,我们指定了T1为 Nested Loop的驱动表,并且避免了目标SQL进行额外的排序工作,并且将处理行数降低,索引只扫描10行数据便停止扫描且满足需求。