函数索引与虚拟列的比较

在数据库开发过程中，经常遇到一个问题是时间字段精度问题。有时候在保存的时候要求时间精确到秒，但是在使用过程中可能只要精确到天。

例如：。如果要求找到2012年七月25号创建的用户。

1.错误sql：select * from t_cust_custbasedata t where t.fcreatetime = to_date('2012-07-25','yyyy-mm-dd') 数据库里面存储的时间有精度。

2.正确做法：但是明显是全表扫描，如果数据量大那么会非常影响性能。

3.错误优化方式：在创建时间列上添加索引create index IDX_DATABASE_FCREATETIME on dpcrm.t_cust_custbasedata(fcreatetime);。由于在fcreatetime使用了函数，所以还是不会使用普通索引。

4.添加函数索引：create index IDX_TRUNC_FCREATETIME on dpcrm.t_cust_custbasedata(TRUNC(fcreatetime));

结果很明显。

那么来看看索引的大小，还有原来表的大小

5.添加虚拟列

添加：

alter table dpcrm.t_cust_custbasedata
add (truncdate TIMESTAMP(6) generated always as (trunc(fcreatetime)) virtual)

收集统计信息：

begin
  dbms_stats.gather_table_stats(OWNNAME => 'DPCRM',
                                TABNAME => 'T_CUST_CUSTBASEDATA',
                                cascade => true);
end;

这样就增加了一列名字为truncdate 到表后面，接下来的查询可以直接使用该列，避免了函数转换导致不走索引，但是很明显需要在该列上增加索引。最终的效果和函数索引一样。说明：虚拟列相当一个函数，在使用该列的时候实时返回值，所以不占用任何存储空间。

综上，在使用虚拟列实现函数索引功能意义并不是很大，oracle官方引入虚拟列主要是优化分区，详见

Using Virtual Column-Based Partitioning

https://docs.oracle.com/database/121/VLDBG/to_vldbg1109_d272.htm#VLDBG1109