在索引列上使用函数使得索引失效的是常见的索引失效原因之一,因此尽可能的避免在索引列上使用函数。尽管可以使用基于函数的索引来
解决索引失效的问题,但如此一来带来的比如磁盘空间的占用以及列上过多的索引导致DML性能的下降。本文描述的是一个索引列上使用函数使
其失效的案例。
一、数据版本与原始语句及相关信息
1.版本信息
2.原始语句与其执行计划
从执行计划可以看出,SQL语句使用了全表扫描,而where 子句中只有唯一的一列business_date
3.表上的索引信息
从索引的情况上来看有一个基于主键的索引包含了BUSINESS_DATE列,而查询语句并没有走索引而是选择的全表扫描,而且预估所返回
的行Rows与bytes也是大的惊人,cost的值96399,接近10W。
二、分析与改造SQL语句
1.原始的SQL语句分析
SQL语句中where子句的business_date列实现对记录过滤
business_date <= '20110728'条件不会限制索引的使用
SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6)使用了SUBSTR函数,限制了优化器选择索引
基于business_date列来建立索引函数,从已存在的索引来看,必要性不大
2.改造SQL语句
SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6)的实质是等于当月,即限制返回的行为从2011.7.1日至2011.7.28
因此其返回的记录大于等于2011.7.1,且小于2011.7.28
做如下改造
business_date >=to_char(last_day(add_months(to_date('20110728','yyyymmdd'),-1)) + 1,'yyyymmdd')
3.改造后的SQL语句
4.改造后的执行计划
改造后可以看到SQL语句的执行计划已经由原来的全表扫描改为执行INDEX SKIP SCAN,但其cost也并没有降低多少
三、进一步分析
1.表的相关信息
2.索引的相关信息
3.尝试在BUSINESS_DATE列上创建索引
建立索引后聚簇因子较小,差不多接近表上块的数量
4.使用新创建索引后的执行计划
从上面的执行计划看出,SQL语句已经选择了新建的索引
尽管返回的rows,bytes没有明显的变化,但cost已经少了近7倍。