Oracle性能优化-利用索引特性进行max/min优化(IS NOT NULL的优化)

 

1. 问题提出

客户系统有这样一条SQL,脱敏后如下:

SELECT NVL(MAX(T1.CREATED),SYSDATE) FROM DUAL LEFT JOIN TEST11 T1
ON T1.OWNER=’OUTLN’ AND OBJECT_TYPE IS NOT NULL;

SQL是TEST11表和DUAL表相关联,WHERE条件中OWNER字段有索引,SQL走了该字段索引范围扫描的执行计划,单次执行逻辑读2117。SQL执行频率非常高,一分钟数万次。执行计划如下:
图片

2. 初步优化

WHERE条件有两个【OWNER=’OUTLN’】和【OBJECT_TYPE IS NOT NULL】,查询取出来的字段是CREATED,考虑创建OWNER+OBJECT_TYPE+CREATED三列联合索引,可以消除回表的成本,创建索引后逻辑读由2117降为82。执行计划如下:
图片

3. 简化版取min/max索引优化

为了更好理解,我们把问题简化成取表里CREATED最小值(或者最大值)。

需要取得TEST11表CREATED的最大/最小值:

SELECT MAX(CREATED) FROM TEST11;

假设存在CREATED字段的索引,那么完全可以只取叶子块的最靠边的一个块,就能得到所需要的的值。

下面做一个测试,创建一个测试表:

create table test11 tablespace DATA_TS as select * from dba_objects where rownum <1000;

begin 
for i in 0..10 loop
insert /*+append */into test11 select * from test11;
commit;
end loop;
end;
/

创建一个CREATED的索引,然后运行之前简化的SQL。根据索引原理可以判断应该需要3-4个逻辑读:分别是Root节点开始–>找最右边的Branch(可能是0-2个,根据索引的层级)–>再找到最右边的Leaf Block。

执行如下,结果和我们之前设想的一样,执行计划走的是INDEX FULL SCAN(MIN/MAX)。
图片

设想稍微复杂一点场景:假设需要得到的是符合指定的条件的最大CREATED值呢?

如果我们需要取的是符合OWNER = 'OUTLN’的最大CREATED值。SQL如下:

SELECT MAX(CREATED) FROM TEST11 WHERE OWNER = 'OUTLN';

如果存在(OWNER,CREATED)组合索引,数据库就可以利用类似的方法只取其中一个叶子节点。执行计划走的是INDEX RANGE SCAN(MIN/MAX),逻辑读是3:
图片

那么假如是SELECT MAX(CREATED) FROM TEST11 WHERE OWNER= ‘OUTLN’ AND OBJECT_TYPE =‘TABLE’ ,就需要新的索引(OWNER,OBJECT_TYPE,CREATED)来完成同样的动作。执行计划走的是INDEX RANGE SCAN(MIN/MAX),需要3个逻辑读:
图片

也就是说,必须条件中的列和索引前导列完全匹配,然后取的索引里最后一列的MAX/MIN值,它就可以走最优的INDEX RANGE SCAN (MIN/MAX)索引。

4. 转化IS NOT NULL条件到索引中

回到原始的SQL:

SELECT NVL(MAX(T1.CREATED),SYSDATE) FROM DUAL LEFT JOIN TEST11 T1
ON T1.OWNER=’OUTLN’AND OBJECT_TYPE IS NOT NULL;

原始SQL中除了OWNER=’OUTLN’,还有一个OBJECT_TYPE IS NOT NULL,直接创建 (OWNER,OBJECT_TYPE,CREATED)联合索引消耗逻辑读82,并不能达到最优的效果,因为OBJECT_TYPE IS NOT NULL不能对应到索引的一个特定值,执行计划如下:
图片

我们需要把OBJECT_TYPE IS NOT NULL的条件固化成一个特定的值,这样就可以匹配索引中的特定值了。SQL需要改写一下:

1) 去掉DUAL, 并不会影响结果集:

SELECT NVL(MAX(T1.CREATED),SYSDATE) FROM TEST11 T1
WHERE T1.OWNER=’OUTLN’AND OBJECT_TYPE IS NOT NULL;

2) 把OBJECT_TYPE IS NOT NULL变成一个特定值,这里用了CASE WHEN,SQL变成:

SELECT MAX(CREATED) FROM TEST11
WHERE OWNER ='OUTLN'
AND CASE WHEN OBJECT_TYPE IS NOT NULL THEN 1 END = 1;

3) 创建一个函数索引。也就是说OBJECT_TYPE IS NOT NULL的记录在索引中存储为1。如下:

CREATE INDEX IDX_TEST11_MAX ON TEST11(OWNER,CASE WHEN OBJECT_TYPE IS NOT NULL THEN 1 END,CREATED) PARALLEL 4 NOLOGGING;
ALTER INDEX IDX_TEST11_MAX NOPARALLEL;

此时执行计划如下,逻辑读降为3:
图片

6. 优化结果

为生产SQL创建CASE WHEN索引并改写SQL后逻辑读降为75,并没有走上最优的INDEX RANGE SCAN (MIN/MAX)执行计划,这是因为这种写法MAX(T1.CREATED)被放在最外层,也就是说对MAX的取值是基于关联后的结果集而不是基于TEST11表。
图片

SQL是TEST11和DUAL做关联,取NVL (MAX (T1.CREATED), SYSDATE) 的值,即使没有数据也会返回SYSDATE的值,去掉DUAL表不影响,去掉DUAL表后SQL逻辑读降为3:
图片

最终完成了一个高频SQL从2117到3的极致优化,提升达数百倍。

 

  • 0
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

码农2007

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值