在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.函数索引创建时要和使用时保持一致
2.不建议使用自定义函数
3.创建函数索引的函数必须是确定性的。即,对于指定的输入,总是会有确定的结果。
4.创建索引的函数不能使用SUM、COUNT等聚合函数。
5.不能在LOB类型的列、NESTED TABLE列上创建函数索引。
6.不能使用SYSDATE、USER等非确定性函数。
7.对于任何用户自定义函数必须显式声明DETERMINISTIC关键字,否则会导致ORA-30553:the funciton is not deterministic错误。