Oracle之函数索引

0    451    1

Tags:

👉 本文共约13284个字,系统预计阅读时间或需50分钟。

在Oracle中,有一类特殊的索引,称为函数索引(Function-Based Indexes,FBI),它基于对表中列进行计算后的结果创建索引。函数索引在不修改应用程序的逻辑基础上提高了查询性能。如果没有函数索引,那么任何在列上执行了函数的查询都不能使用这个列的索引。当在查询中包含该函数时,数据库才会使用该函数索引。函数索引可以是一个B-Tree索引或位图索引。

用于生成索引的函数可以是算术表达式,也可以是一个包含SQL函数、用户定义PL/SQL函数、包函数,或C调用的表达式。当数据库处理INSERT和UPDATE语句时,它仍然必须计算函数才能完成对语句的处理。

对于函数索引的索引列的函数查询可以通过视图DBA_IND_EXPRESSIONS来实现,通过如下的SQL语句可以查询所有的函数索引:

SELECT * FROM DBA_INDEXES D WHERE D.INDEX_TYPE LIKE 'FUNCTION-BASED%';

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

① 必须使用基于成本的优化器,而且创建后必须对索引进行分析。

② 如果被函数索引所引用的用户自定义PL/SQL函数失效了或该函数索引的属主没有了在函数索引里面使用的函数的执行权限,那么对这张表上的执行的所有的操作(例如SELECT查询、DML等)也将失败(会报错:ORA-06575: Package or function F_R1_LHR is in an invalid state或ORA-00904: : invalid identifier)。这时,可以重新修改自定义函数并在编译无报错通过后,该表上所有的DML和查询操作将恢复正常。

③ 创建函数索引的函数必须是确定性的。即,对于指定的输入,总是会返回确定的结果。

④ 在创建索引的函数里面不能使用SUM、COUNT等聚合函数。

⑤ 不能在LOB类型的列、NESTED TABLE列上创建函数索引。

⑥ 不能使用SYSDATE、USER等非确定性函数。

⑦ 对于任何用户自定义函数必须显式的声明DETERMINISTIC关键字,否则会报错:“ora-30553: the function is not deterministic”。

需要注意的是,使用函数索引有几个先决条件:

(1)必须拥有CREATE INDEX和QUERY REWRITE(本模式下)或CREATE ANY INDEX和GLOBAL QUERY REWRITE(其它模式下)权限。其赋权语句分别为“GRANT QUERY REWRITE TO LHR;”和“GRANT GLOBAL QUERY REWRITE TO LHR;”。

(2)必须使用基于成本的优化器,基于规则的优化器将被忽略。

(3)参数QUERY_REWRITE_INTEGRITY和QUERY_REWRITE_ENABLED可以保持默认值。

QUERY_REWRITE_INTEGRITY = ENFORCED

QUERY_REWRITE_ENABLED = TRUE(从Oracle 10g开始默认为TRUE)

这里举一个基于函数的索引的例子。

首先为函数索引的建立及数据做准备:

SYS@lhrdb> CREATE TABLE TESTFINDEX_LHR(ID NUMBER,SCHR VARCHAR2(10));

Table created.

SYS@lhrdb> CREATE INDEX IND_FUN ON TESTFINDEX_LHR(UPPER(SCHR));

Index created.

SYS@lhrdb> INSERT INTO TESTFINDEX_LHR VALUES(1,'a');

1 row created.

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
Oracle之函数索引后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章
验证码:
请关注本站微信公众号,回复“小麦苗博客”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部