ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle函数索引

Oracle函数索引

原创 Linux操作系统 作者:beyond_sea 时间:2009-04-19 11:53:12 0 删除 编辑

创建基于函数的索引为什么需要query write的权限


SQL> create table testaa (a1 varchar2(5));

表已创建。

SQL> create index ind_testaa on testaa(upper(a1));
create index ind_testaa on testaa(upper(a1))
                                        *
ERROR 位于第 1 行:
ORA-01031: 权限不足


SQL> conn /as sysdba
已连接。
SQL> grant query rewrite to scott;

授权成功。

SQL> conn scott/tiger
已连接。
SQL>  create index ind_testaa on testaa(upper(a1));

索引已创建。

SQL> show parameter query

NAME                                 TYPE
------------------------------------ -----------------
VALUE
------------------------------
query_rewrite_enabled                string
FALSE
query_rewrite_integrity              string
enforced
SQL>

 


9i以上函数索引不需要query rewrite权限,也不需要query_rewrite_enabled参数设置为TRUE

8i的版本需要这个权限。

 

 

其实结果无所谓,关键是明白其中的道理,我是这么猜测oracle在建函数索引需要query rewrite的原因(我测试过在9204以上的版本中不需要了)
因为函数索引的结构跟一般BTREE的结构没有区别,只是在KEY中存储的是函数而不是结果,所以每次用到索引时先必需运行函数返回结果,这里就存放类似查询重写把要查询的表结构映射到快照一样,在新的版本中过程也是一样,只是自动负于相应的权限,不需要用户指定了
这里还想请教:oracle内部是通过什么方式能够实现query rewrite??是否有相应的函数运算呢??

 

 

 

Oracle函数索引


Oracle8i新特性:FBI索引   

  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 ptimizer=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 ptimizer=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的才可以

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7532565/viewspace-590934/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-07-05

  • 博文量
    18
  • 访问量
    54078