函数索引

在Oracle中，有一类特殊的索引，称为函数索引（Function-Based Indexes，FBI），它基于对表中列进行计算后的结果创建索引。函数索引在不修改应用程序的逻辑基础上提高了查询性能。如果没有函数索引，那么任何在列上执行了函数的查询都不能使用这个列的索引。当在查询中包含该函数时，数据库才会使用该函数索引。函数索引可以是一个B-Tree索引或位图索引。

踩坑背景

1.生产环境某交易表数据量大约在20w左右，在根据具体条件查询时，发现查询特别慢。

（测试环境数据）

2.查看表结构发现日期字段没有加索引

3.本想着直接对日期加上索引，应该就可以了。

create index IX_MESSAGE_MA_CREAT_TIME on MESSAGE (MA_CREAT_TIME)
  tablespace UTMSINDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 9M
    next 1M
    minextents 1
    maxextents unlimited
  );

4.但是再次查询时发现，查询依旧很慢，通过查看Oracle解释计划，发现日期索引没有使用。

5.创建函数索引

create index IX_MESSAGE_MA_CREAT_TIME on MESSAGE (to_char(MA_CREAT_TIME, 'yyyy-mm-dd'))
  tablespace UTMSINDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 9M
    next 1M
    minextents 1
    maxextents unlimited
  );

6.使用Oracle解释计划再次查询，日期字段正常使用函数索引，查询效率提高很多。

注意事项

1.函数索引创建时要和使用时保持一致

• 创建时为：to_char(MA_CREAT_TIME, 'yyyy-mm-dd')

• 查询时也需使用：to_char(MA_CREAT_TIME, 'yyyy-mm-dd')

• to_char(MA_CREAT_TIME, 'YYYY-MM-DD') 这样就会失效

• 任何不一致都会导致索引失效，其他函数同样。

2.不建议使用自定义函数

• 如果被函数索引所用的自定义函数失效或该函数索引的属主没有了在函数索引里面使用的函数的执行权限，则会导致ORA-06575错误

• 重新修改自定义函数并在编译无报错通过后，方可正常使用。

3.创建函数索引的函数必须是确定性的。即，对于指定的输入，总是会有确定的结果。

4.创建索引的函数不能使用SUM、COUNT等聚合函数。

5.不能在LOB类型的列、NESTED TABLE列上创建函数索引。

6.不能使用SYSDATE、USER等非确定性函数。

7.对于任何用户自定义函数必须显式声明DETERMINISTIC关键字，否则会导致ORA-30553：the funciton is not deterministic错误。