日期做索引 oracle能否用日期做索引

转载

mob64ca140088a9 2024-03-25 20:22:05

文章标签 日期做索引数据库字段 sed 存储空间 文章分类 数据仓库大数据

3.函数索引

可以在表中创建基于函数的索引。如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。例如，下面的查询就不能使用DT_TIME列上的索引，除非它是基于函数的索引：

WHERE 1=1 AND (sysdate-to_date(DT_TIME,'yyyymmddhh24miss'))*24*60<10

在DT_TIME上面加上了索引，但是发现执行计划并没有按照索引来，这里其实是Oracle的一个缺陷了，

例如：DT_TIME-7<sysdate 与 DT_TIME<sysdate+7 在我们看来是一样的，但是在Oracle眼里却是不一样的，前面的表达式不会走索引，而后面的表达式就会走索引

下面的查询就不能使用JOB列上的索引，除非它是基于函数的索引：

select * from emp where UPPER(job) = 'MGR';
下面的查询使用JOB列上的索引，但是它将不会返回JOB列具有'Mgr'或'mgr'值的行：
select * from emp where job = 'MGR';

可以创建这样的索引，允许索引访问支持基于函数的列或数据。可以对列表达式UPPER(job)创建索引，而不是直接在JOB列上建立索引，如：
create index EMP$UPPER_JOB on emp(UPPER(job));

尽管基于函数的索引非常有用，但在建立它们之前必须先考虑下面一些问题：

1.能限制在这个列上使用的函数吗？如果能，能限制所有在这个列上执行的所有函数吗
2.是否有足够应付额外索引的存储空间？
3.在每列上增加的索引数量会对针对该表执行的DML语句的性能带来何种影响？

4.基于函数的索引非常有用，但在实现时必须小心。在表上创建的索引越多，INSERT、UPDATE和DELETE语句的执行就会花费越多的时间。
注意：对于优化器所使用的基于函数的索引来说，必须把初始参数QUERY _REWRITE _ ENABLED设定为TRUE。
示例：
创建索引前：select count(*) from sample where ratio(balance,limit) >.5;
Elapsed time: 20.1 minutes

创建索引： create index ratio_idx1 on sample (ratio(balance, limit));

创建索引后：select count(*) from sample where ratio(balance,limit) >.5;
Elapsed time: 7 seconds!!!

函数索引限制：

1、必须使用一个确定的函数定义基于该函数的索引，也就是说函数仅返回一个值

2、必须使用返回可重复值的函数来定义基于该函数的索引，如sysdate就不行

3、可以对基于函数的索引进行分区，但是对基于函数的全局分区索引来说，分区键不能是索引所基于的函数

4、函数必须使用圆括号来定义，即使没有参数

5、索引基于的函数不能包含聚合函数

使用建议：

1、不要轻易在字段前面加函数

2、尽量不要将字段嵌入表达式中

3、尽量减少使用函数索引，能不用就不用，因为函数索引的维护代价比普通索引高；函数索引计算值可能大于原字段值，将消耗更多的存储空间