基于函数的索引

以下内容摘自《Oracle SQL 高级编程》 第12.4.2章节-基于函数的索引[其中代码部分被修改,原始请参考书籍]

如果一个谓语在索引列上应用了函数，则优化器不会选用该列上的索引。例如，对于谓语to_char(CYRQ, 'YYYY-MM-DD') = '2014-01-21',不会选用CYRQ列上的索引,因为在索引列上应用了to_char函数。这个限制可以通过表达式to_char(CYRQ)在创建基于函数的索引来克服。基于函数的索引预存函数的结果。谓语中所声明的表达式必须基于函数的索引所声明的表达式想匹配。

    基于函数的索引也可以建立在用户自定义函数上，但这个函数必须定义为确定性函数，也就是说对于这个函数的每一次执行必须返回一致的值。不遵守这一规则的用户自定义函数不能用来创建基于函数的索引。

    在代码清单12-14中，SELECT 语句使用to_char(CYRQ, 'YYYY-MM-DD') = '2014-01-21'子句来访问CK10_GHDJ表。如果没有基于函数的索引，优化器会选择全表扫描访问计划。通过表达式to_char(CYRQ, 'YYYY-MM-DD')增加了基于函数的索引INDEX_CK10_GHDJ_CYRQ2之后，优化器就为该SELECT语句选用了基于索引的访问路径。

CREATE INDEX INDEX_CK10_GHDJ_CYRQ2 ON CK10_GHDJ(TO_CHAR(CYRQ,'YYYY-MM-DD'));

SELECT COUNT(0)
  FROM CK10_GHDJ G
 WHERE TO_CHAR(G.CYRQ, 'YYYY-MM-DD') = '2014-01-21';

注意代码清单12-14中最后所打印出来的访问谓语“SYS_NC00009$”=’1000’。关于基于函数索引的一些实现上的细节列于代码清单12-15。基于函数的索引加入了一个虚拟列，所声明的表达式值作为默认值，然后在这个虚拟列上建立索引。这个虚拟列可从dba_tab_cols视图中可见，并且dba_tab_cols.data_default列显示了用来填充虚拟列的表达式。进一步的dba_ind_columns视图显示对虚拟列进行了索引。

SELECT DATA_DEFAULT, HIDDEN_COLUMN, VIRTUAL_COLUMN
  FROM DBA_TAB_COLS
 WHERE TABLE_NAME = 'CK10_GHDJ'
   AND VIRTUAL_COLUMN = 'YES';

    在增加了基于函数的索引后收集表的统计信息是很重要的。如果不收集，新的虚拟列就没有统计信息，这有可能会导致性能异常。脚本analyze_table_sfp.sql被用来收集表的统计信息并设置cascade=>true。代码12-16给出了analyze_talbe_sfp.sql脚本的内容。

代码清单12-16 Analyze_table_sfp.sql脚本

begin
  dbms_stats.gather_table_stats(ownname          => user,
                                tabname          => 'CK10_GHDJ',
                                estimate_percent => 30,
                                cascade          => true);
end;
/

         基于函数的索引也可以显示使用虚拟列来实现。在这个虚拟列上也可以增加索引。这种方法额外的好处就是你还可以使用虚拟列作为分区键来应用分区方案。在代码清单12-17中，使用virtual 关键字在表中加入了一个新的虚拟列cyrq_char。然后在cyrq_char列上建立了全局分区索引。SELECT语句的执行计划显示表使用新建的索引来访问，并且谓语to_char(CYRQ, 'YYYY-MM-DD') = '2014-01-21'被重写为谓语cyrq_char=’2014-01-21’以使用虚拟列。