如何创建oracle函数索引

转载 2006年06月24日 16:28:00

 

    【IT168 技术文档】Oracle8i的很重要的一个新特性就是增加了function-based index这种索引类型(后面简称为FBI)。有了这个特性后,Oracle DBA就可以在索引中使用函数或者表达式了。这些函数可以使Oracle自己的函数,也可以使用户自己的PL/SQL函数等。

 

 

    Oracle8i的很重要的一个新特性就是增加了function-based index这种索引类型(后面简称为FBI)。有了这个特性后,Oracle DBA就可以在索引中使用函数或者表达式了。这些函数可以使Oracle自己的函数,也可以使用户自己的PL/SQL函数等。

 

 

    DBA在SQL语句调优的过程中遇到的一个很常见的问题就是,如何优化那些在WHERE子句中使用了函数的语句。因为在以前,在WHERE子句中使用函数会使在这个表上创建的索引没法利用,从而难以提高这个语句的性能。

 

 

    例子:
使用基于成本的优化器,索引为标准的B树索引,建立在SURNAME列上。

 

SQL>create index non_fbi on sale_contacts (surname); SQL>analyze index non_fbi compute statistics; SQL>:analyze table sale_contacts compute statistics; SQL>SELECT count(*) FROM sale_contacts WHERE UPPER(surname) = 'ELLISON'; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=17) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'SALES_CONTACTS' (Cost=3 Card=16 Bytes=272)

 

    从SQL*PLUS的autotrace产生的执行路径可以看到,虽然我们在WHERE子句中用到的SURNAME列上创建了索引,但是仍然执行的是全表扫描。如果这张表很大的话,这回消耗大量的时间。

 

 

    现在我们试着建立一个FBI索引:

 

SQL>create index fbi on sale_contacts (UPPER(surname)); SQL>analyze index fbi compute statistics; SQL>analyze table sale_contacts compute statistics; SQL>SELECT count(*) FROM sale_contacts WHERE UPPER(surname) = 'ELLISON'; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17) 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF 'FBI' (NON-UNIQUE) (Cost=2 Card=381 Bytes=6477)

 

    从SQL*Plus返回的执行计划我们可以看到,这次,Oracle对表不再全表扫描,而是先扫描索引,因为优化器可以知道FBI索引得存在。使用FBI索引所能够带来的性能提升取决于表的大小、表中重复记录的量、在WHERE子句中使用的列等因素。有一点需要清楚,FBI索引并不真正在索引里边存储了表达式的结果,而是使用了一个“表达树”(expression tree)。

 

 

    由优化器来对SQL语句中的表达式进行解析,并且和FBI索引上面的表达式进行对比。这里,SQL函数的大小写时敏感的。因此要求SQL语句中使用的函数和创建FBI索引得时候的那个SQL函数的大小写一致,否则无法利用这个FBI索引。因此,在编程的时候要有一个良好的编程风格。

 

 

    Init.ora里边需要修改的参数,下面这几个参数必须在init.ora里边指定:

 

QUERY_REWRITE_INTEGRITY = TRUSTED QUERY_REWRITE_ENABLED = TRUE COMPATIBLE = 8.1.0.0.0 (or higher)

 

    授权:

 

 

    要使一个用户能够创建FBI索引,他必须被授予以下权限:CREATE INDEX和QUERY REWRITE,或者CREATE ANY INDEX和GLOBAL QUERY REWRITE这两个权限。索引的使用者必须能够有那个FBI索引上使用的那个函数的执行权限。如果没有相应的权限,那么这个FBI索引得状态将变成DISABLED(DBA_INDEXES)。

 

 

    如果那个FBI索引得状态是DISABLED,那么DBA可以这样来处理:

 

 

    a:删除并重建
    B:ALTER INDEX index_name ENABLED。这个Enabled只能对FBI索引使用。
    C:ALTER INDEX UNUSABLE;

 

 

    注意:如果一个查询中使用到了这个索引,但是这个FBI索引的状态是DISABLED,但是优化器选择了使用这个索引,那么将会返回一个Oracle错误。

 

 

    例子:

 

 

    ORA error:
    ERROR at line 1: ORA-30554: function-based index MYUSER.FBI is disabled.

 

 

    而且,一旦这个FBI索引的状态是Disabled,那么这张表上所有涉及索引列的DML操作也将失败。除非这个索引得状态变成UNUSABLE,而且在初始化参数里边指定SKIP_UNUSABLE_INDEXES为TRUE。

 

 

    一些例子:

 

SQL>CREATE INDEX expression_ndx ON mytable ((mycola + mycolc) * mycolb); SQL>SELECT mycolc FROM mytable WHERE (mycola + mycolc) * mycolb <= 256;

 

    复合索引的例子:

 

SQL>CREATE INDEX example_ndx ON myexample (mycola, UPPER(mycolb), mycolc); SQL>SELECT mycolc FROM myexample WHERE mycola = 55 AND UPPER(mycolb) = 'JONES';

 

    限制和规则总结:

 

 

    对于下面这些限制,不能创建FBI索引:

 

 

    a) LOB 列
    b) REF
    c) Nested table 列
    d) 包含上面数据类型的对象

 

 

    FBI索引必须遵守下面的规则:

 

 

    a) 必须使用基于成本的优化器,而且创建后必须对索引进行分析
    b) 不能存储NULL值。因为任何函数在任何情况下都不能返回NULL值。
    c)如果一个用户定义的PL/SQL例程失效了,而且这个例程被FBI索引用到了,那么相应的这个FBI索引会变成DISABLED
    d)创建FBI索引得函数必须是确定性的。即,对于指定的输入,总是会返回确定的结果。
    e) 索引的属主如果没有了在FBI索引里面使用的函数的执行权限,那么这个FBI索引会变成DISABLED.
    f) 在创建索引得函数里面不能使用SUM等总计函数。
    g)要把一个DISABLED了的索引重新变成ENABLED,这个函数必须首先是ENABLED的才可以。

基于函数的索引+创建基于函数的索引

基于函数的索引 1基于函数的索引是常规的B树索引。 2该索引存放的数据是由表中的数据应用函数后得到的,而不是直接存放表中的数据本身。 3如果查询条件包含与索引相同的函数,就可以使用基于函数的索引...
  • zhou920786312
  • zhou920786312
  • 2017年05月28日 15:57
  • 1503

使用Oracle函数索引简单介绍(一般)

使用Oracle函数索引 提高查询效率 2010-10-25 10:55 佚名 互联网 我要评论(0) 字号:T | T 想要提高Oracle数据库的查询检索的效率,使用函...
  • haiross
  • haiross
  • 2013年11月01日 15:46
  • 2348

基于函数的索引+创建基于函数的索引

基于函数的索引 1基于函数的索引是常规的B树索引。 2该索引存放的数据是由表中的数据应用函数后得到的,而不是直接存放表中的数据本身。 3如果查询条件包含与索引相同的函数,就可以使用基于函数的索引...
  • zhou920786312
  • zhou920786312
  • 2017年05月28日 15:57
  • 1503

Oracle中索引的创建和使用

OLTP系统索引创建 创建索引的作用 1、通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 2、可以大大加快数据的检索速度,这也是创建索引的最主要的原因。 3、可以加速表和表之间的连接,...
  • u010383786
  • u010383786
  • 2016年09月07日 13:38
  • 2081

oracle数据库建立索引的原则

数据库建立索引的原则 1,确定针对该表的操作是大量的查询操作还是大量的增删改操作。 2,尝试建立索引来帮助特定的查询。检查自己的sql语句,为那些频繁在where子句中出现的字段建立索引。 3,尝...
  • zbyluxzing
  • zbyluxzing
  • 2016年08月08日 23:16
  • 2586

oracle表空间,角色,权限,表,索引,序列号,视图,同义词,约束条件,存储函数和过程,常用数据字典,基本数据字典信息,查看VGA信息,维护表空间,创建表空间等信息

查看当前用户的缺省表空间   SQL>select username,default_tablespace from user_users;   查看当前用户的角色   SQL>select *...
  • toto1297488504
  • toto1297488504
  • 2013年08月09日 09:11
  • 1093

oracle 视频教程 视图 存储过程 触发器 表索引 函数

  • 2009年08月29日 17:50
  • 224B
  • 下载

数据库 day60,61 Oracle入门,单行函数,多表查询,子查询,事物处理,约束,rownum分页,视图,序列,索引

数据库 day60,61 Oracle入门,单行函数,多表查询,子查询,事物处理,约束,rownum 分页,视图,序列,索引...
  • opopopwqwqwq
  • opopopwqwqwq
  • 2016年06月23日 02:51
  • 8280

使用Oracle函数索引简单介绍(一般)

使用Oracle函数索引 提高查询效率 2010-10-25 10:55 佚名 互联网 我要评论(0) 字号:T | T 想要提高Oracle数据库的查询检索的效率,使用函...
  • haiross
  • haiross
  • 2013年11月01日 15:46
  • 2348

深入探讨Oracle函数索引

Oracle还是比较常用的,Oracle函数索引在某些情况下也是非常有用的。 一.Oracle函数索引 1.Oracle函数索引的好处:Exp: function(column1) 如果表...
  • h254541060
  • h254541060
  • 2015年09月17日 21:53
  • 207
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:如何创建oracle函数索引
举报原因:
原因补充:

(最多只允许输入30个字)