Oracle 函数索引的建立及使用

近期在使用时间查询的时候，因为在日期字段上要使用函数转换，无法建立索引。
因此只好建立函数索引来完成。

环境如下：
select count(*) as TJ, FAULT_TYPE, decode(FAULT_STYLE, null, 0, fault_style) fault_style
  FROM RM_MALFUNCTION_FAULT
WHERE to_char(notice_date, 'yyyy-mm') = to_char(sysdate, 'yyyy-mm')
group by fault_type, fault_style

虽然在notice_date上建立了索引，但是因为使用函数转换，无法使用索引，还是全表扫描。

最后只好在此字段上建立函数索引：　to_char(notice_date,'yyyy-mm')

要使用函数索引，必须条件：

(1)
必须拥有QUERY REWRITE（本模式下）或GLOBAL QUERY REWRITE（其他模式下）权限。
(2)
必须使用基于成本的优化器，基于规则的优化器将被忽略。
(3)
必须设置以下两个系统参数：
1.QUERY_REWRITE_ENABLED=TRUE
2.QUERY_REWRITE_INTEGRITY=TRUSTED
可以通过alter system set,alter session set在系统级或线程级设置，也可以通过在init.ora添加实现。
基于函数的索引，类似于普通的索引，只是普通的索引是建立在列上，而它是建立在函数上。当然这回对插入数据有一定影响，因为需要通过函数计算一下，然后生成索引。但是插入数据一般都是少量插入，而查询数据一般数据量比较大。为了优化查询速度，稍微降低点插入速度是可以承担的。

建立函数索引后，上述SQL，执行起来效率非常不错。

函数索引还有一个功能，只对部分行建立索引。假设有一个很大的表，有一列叫做FLAG，只可能取Y和N。假设大部分数据是Y，小部分数据是N，我们需要将N修改成Y。如果建立一个普通索引，这个索引会非常大，而且将N修改成Y的时候，维护这个索引开销会很大。不过这个表听起来比较适合位图索引，但这是一个事物系统（OLTP），可能有很多人同时插入记录，或者进行修改。那么位图索引也不适合。所以，如果我们只是在值为N的行上建立索引，就比较好办了。

      只在值为N的行建立索引SQL：create index flag_index on big_table(case flag when 'N' then 'N' end);这样一个索引大小会大大降低，而且维护成本也会很低的。前提是我们只对值为N的行感兴趣。

评论

1 楼 jackra 2013-01-10  

从当年的第一天到第二年的第一天不就可以了噻？
为什么一定要函数匹配呢？