你好,游客 登录 注册 搜索
背景:
阅读新闻

《Oracle编程艺术》学习笔记

基于函数的索引

[日期:2012-01-11] 来源:Linux社区  作者:fw0124 [字体: ]
基于函数的索引(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函数。

[sql]
  1. tony@ORA11GR2> create table t (col1 varchar2(10), col2 varchar2(10));  
  2. Table created.  
  3.   
  4. tony@ORA11GR2> create or replace function my_func(p_str in varchar2) return varchar2  
  5.   2  deterministic  
  6.   3  as  
  7.   4      l_return varchar2(10);  
  8.   5  begin  
  9.   6      l_return := substr(p_str, 1, 10);  
  10.   7      return rpad(l_return, 10, '*');  
  11.   8  end;  
  12.   9  /  
  13. Function created.  
  14.   
  15. tony@ORA11GR2> create index t_idx on t(my_func(col1), my_func(col2));  
  16. create index t_idx on t(my_func(col1), my_func(col2))  
  17.                                                *  
  18. ERROR at line 1:  
  19. ORA-01450: maximum key length (6398) exceeded  

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

[sql]
  1. tony@ORA11GR2> create index t_idx on t(substr(my_func(col1), 1, 10), substr(my_func(col2), 1, 10));  
  2. 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

[sql]
  1. tony@ORA11GR2> create or replace view t_vw  
  2.   2  as  
  3.   3  select col1, substr(my_func(col1), 1, 10) col1_myfunc,  
  4.   4         col2, substr(my_func(col2), 1, 10) col2_myfunc  
  5.   5  from t;  
  6. View created.  
  7.   
  8. tony@ORA11GR2> select * from t_vw where col1_myfunc = my_func('abc');  

2)11g以后,可以使用虚拟列

先要把上面创建的基于函数的索引t_idx删除掉。然后添加虚拟列,在虚拟列上创建索引。

[sql]
  1. tony@ORA11GR2> alter table t add  
  2.   2  col1_myfunc as (substr(my_func(col1), 1 , 10));  
  3. Table altered.  
  4.   
  5. tony@ORA11GR2> alter table t add  
  6.   2  col2_myfunc as (substr(my_func(col2), 1 , 10));  
  7. Table altered.  
  8.   
  9. tony@ORA11GR2> create index t_idx on t(col1_myfunc, col2_myfunc);  
  10. Index created.  
  11.   
  12. tony@ORA11GR2> select * from t where col1_myfunc = my_func('abc');  
  13. no rows selected  

可以用基于函数的索引来有选择地只是对表中的某些行建立索引。这样索引更小,性能更好。
例如-> http://www.linuxidc.com/Linux/2012-01/51264p3.htm

还可以利用基于函数的索引来实现有选择的唯一性。
假设有一个存放项目信息的表。项目有两种状态:要么为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列将建立惟一的索引。如果试图创建同名的活动项目,就会被检测到。

linux
【内容导航】
第1页:配置环境 第2页:开发成功的Oracle应用
第3页:SELECT FOR UPDATE SKIP LOCKED 第4页:Oracle体系结构
第5页:Oracle中的文件 第6页:Oracle中的文件-参数文件
第7页:Oracle中的文件-Trace文件 第8页:Oracle中的文件-警告日志文件
第9页:Oracle中的文件-数据文件 第10页:SGA,PGA和UGA
第11页:Oracle进程 第12页:Oracle的锁
第13页:Oracle的并发与多版本控制 第14页:写一致性
第15页:事务原子性 第16页:事务持久性
第17页:DEFERRABLE约束 第18页:REDO和UNDO
第19页:数据库日志模式 第20页:块清除
第21页:ORA-01555:snapshot too old 错误 第22页:表类型
第23页: 第24页:堆组织表
第25页:索引组织表 第26页:索引聚簇表
第27页:散列聚簇表 第28页:有序散列聚簇表
第29页:临时表和CBO 第30页:B*树索引
第31页:什么情况下适合使用B*树索引 第32页:位图索引(bitmap index)
第33页:基于函数的索引 第34页:数据类型-字符串和二进制串
第35页:数据类型-数值 第36页:数据类型-时间日期
第37页:数据类型-LOB 第38页:表分区
第39页:索引分区 第40页:分区的优点
相关资讯       Oracle基础教程 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款