《Oracle编程艺术》学习笔记(33)-基于函数的索引

基于函数的索引（function-based index）就是B*树索引或位图索引，但它能够对计算得出的列建立索引，并在查询中使用这些索引。可以把基于函数的索引看作一个虚拟列（或派生列）上的索引。
基于函数的索引可以用于加快形如SELECT * FROM T WHERE FUNCTION(DATABASE_COLUMN) = SAME_VALUE 这样的查询， 因为值FUNCTION(DATABASE_COLUMN)已经提前计算并存储在索引中。
基于函数的索引会影响插入和更新的性能，如果总是插入数据而不经常查询，则基于函数的索引并不适合。但一般都是插入一次，查询很多次，这样的话使用基于函数的索引，利大于弊。

例如，如果需要在EMP表格的ENAME列上执行大小写无关搜索，可以在ENAME上建立1个基于函数的索引，
create index emp_ename_upper_idx on emp(upper(ename));
这样执行如下的查询就可以利用此索引，
select * from emp where upper(ename) = 'KING';

也可以利用用户自己编写的函数来建立索引，不过需要使用DETERMINISTIC关键字。
这个关键字告诉Oracle这个函数是确定性的，在给定相同输入的情况下总会返回一致的结果。

如果对一个返回字符串的函数建立索引，需要注意有必要使用SUBSTR函数。

tony@ORA11GR2> create table t (col1 varchar2(10), col2 varchar2(10));
Table created.

tony@ORA11GR2> create or replace function my_func(p_str in varchar2) return varchar2
  2  deterministic
  3  as
  4      l_return varchar2(10);
  5  begin
  6      l_return := substr(p_str, 1, 10);
  7      return rpad(l_return, 10, '*');
  8  end;
  9  /
Function created.

tony@ORA11GR2> create index t_idx on t(my_func(col1), my_func(col2));
create index t_idx on t(my_func(col1), my_func(col2))
                                               *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

可以看到，创建索引的时候会报错。
这是因为我们的函数返回VARCHAR2(4000)，而索引条目必须能在块大小的大约3/4中放得下。
当前的数据库块大小为8K，最大的索引条目大约为8K的3/4，6398字节。
解决办法是使用SUBSTR函数，Oracle看到SUBSTR函数的输入参数1和10，知道最大返回值为10，就允许创建索引。

tony@ORA11GR2> create index t_idx on t(substr(my_func(col1), 1, 10), substr(my_func(col2), 1, 10));
Index created.

但是这样很容易出错，必须使用和创建索引一样的参数1和10来进行查询，例如
select * from t where substr(my_func(col1), 1, 10) = my_func('abc');
如果使用substr(my_func(col1), 1, 9)，查询计划就不会使用索引。
解决方法有2个：
1）建立VIEW

tony@ORA11GR2> create or replace view t_vw
  2  as
  3  select col1, substr(my_func(col1), 1, 10) col1_myfunc,
  4         col2, substr(my_func(col2), 1, 10) col2_myfunc
  5  from t;
View created.

tony@ORA11GR2> select * from t_vw where col1_myfunc = my_func('abc');

2）11g以后，可以使用虚拟列
虚拟列-〉http://blog.csdn.net/fw0124/article/details/6845365
先要把上面创建的基于函数的索引t_idx删除掉。然后添加虚拟列，在虚拟列上创建索引。

tony@ORA11GR2> alter table t add
  2  col1_myfunc as (substr(my_func(col1), 1 , 10));
Table altered.

tony@ORA11GR2> alter table t add
  2  col2_myfunc as (substr(my_func(col2), 1 , 10));
Table altered.

tony@ORA11GR2> create index t_idx on t(col1_myfunc, col2_myfunc);
Index created.

tony@ORA11GR2> select * from t where col1_myfunc = my_func('abc');
no rows selected

可以用基于函数的索引来有选择地只是对表中的某些行建立索引。这样索引更小，性能更好。
例如->http://blog.csdn.net/fw0124/article/details/6898673

还可以利用基于函数的索引来实现有选择的唯一性。
假设有一个存放项目信息的表。项目有两种状态：要么为ACTIVE，要么为INACTIVE。需要保证以下规则：“活动的项目必须有一个惟一名；而不活动的项目无此要求。”
也就是说，只有一个活动的“项目X”，但是如果你愿意，可以有多个名为X的不活动项目。
利用函数索引，并且利用B*树索引中对于完全为NULL的行没有相应的条目的特性，可以创建如下的索引：
create unique index active_projects_must_be_unique
on projects ( case when status = 'ACTIVE' then name end );
这样状态（status）列是ACTIVE 时，NAME列将建立惟一的索引。如果试图创建同名的活动项目，就会被检测到。