专栏首页小麦苗的DB宝专栏【DB笔试面试550】在Oracle中,函数索引是什么?

【DB笔试面试550】在Oracle中,函数索引是什么?

题目部分

在Oracle中,函数索引是什么?

答案部分

在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.
SYS@lhrdb> COMMIT;
Commit complete.

--因为强制使用基于规则的优化器,所以,不会使用函数索引:
SYS@lhrdb> SELECT /*+ RULE*/ * FROM TESTFINDEX_LHR WHERE UPPER(SCHR)='A';
        ID SCHR
---------- ----------
         1 a
Execution Plan
----------------------------------------------------------
Plan hash value: 940247041
--------------------------------------------
| Id  | Operation         | Name           |
--------------------------------------------
|   0 | SELECT STATEMENT  |                |
|*  1 |  TABLE ACCESS FULL| TESTFINDEX_LHR |
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("SCHR")='A')
Note
-----
   - rule based optimizer used (consider using cbo)


--这里优化器选择了全表扫描,若在不使用基于规则的优化器的情况下,则该查询会选择函数索引IND_FUN:
SYS@lhrdb> SELECT  * FROM TESTFINDEX_LHR WHERE UPPER(SCHR)='A';
        ID SCHR
---------- ----------
         1 a
Execution Plan
----------------------------------------------------------
Plan hash value: 967513602
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    27 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTFINDEX_LHR |     1 |    27 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_FUN        |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(UPPER("SCHR")='A')
Note
-----
   - dynamic sampling used for this statement (level=2)
SYS@lhrdb> SELECT D.TABLE_NAME,D.COLUMN_EXPRESSION FROM DBA_IND_EXPRESSIONS D WHERE D.INDEX_NAME='IND_FUN';

TABLE_NAME                     COLUMN_EXPRESSION
------------------------------ ----------------------
TESTFINDEX_LHR                 UPPER("SCHR")

可见,例子中使用了IND_FUN函数索引,且函数可以通过视图DBA_IND_EXPRESSIONS来查询。

本文选自《Oracle程序员面试笔试宝典》,作者:李华荣。

本文分享自微信公众号 - DB宝(xiaomaimiaolhr)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-04-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【DB笔试面试352】什么是不可用索引(Unusable Indexes),哪些操作会导致索引变为不可用即失效状态?

    在Oracle数据库中,什么是不可用索引(Unusable Indexes),哪些操作会导致索引变为不可用(unusable)即失效状态?

    小麦苗DBA宝典
  • 【DB笔试面试549】在Oracle中,单列索引和复合索引分别是什么?

    按照索引列的个数,索引可以分为单列索引和复合索引。单列索引是基于单个列所建立的索引。复合索引(Composite Indexes),也称为连接索引、组合索引或多...

    小麦苗DBA宝典
  • 【DB笔试面试558】在Oracle中,反向键索引(Reverse Key Indexes)是什么?

    反向键索引也称为反转索引,是一种B-Tree索引,它在物理上反转每个索引键的字节,但保持列顺序不变。例如,如果索引键是20,并且在一个标准的B-Tree索引中此...

    小麦苗DBA宝典
  • 一个执行计划异常变更的案例 - 外传之聚簇因子(Clustering Factor)

    之前的几篇文章: 《一个执行计划异常变更的案例 - 前传》 《一个执行计划异常变更的案例 - 外传之绑定变量窥探》 《一个执行计划异常变更的案例 -...

    bisal
  • Java面试中常问的数据库方面问题

    B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接,是有序的

    Spark学习技巧
  • MySQL DBA基本知识点梳理和查询优化

    本文主要是总结了工作中一些常用的操作,以及不合理的操作,在对慢查询进行优化时收集的一些有用的资料和信息,本文适合有MySQL基础的开发人员。

    数据和云
  • 面试中有哪些经典的数据库问题?

    1、如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯...

    lyb-geek
  • MySQL数据库建表、优化、算法、分区分库分表总结

    1、因为任何有业务含义的列都有改变的可能性,主键一旦带上了业务含义,那么主键就有可能发生变更。主键一旦发生变更,该数据在磁盘上的存储位置就会发生变更,有可能会引...

    chenchenchen
  • 24 个MySQL面试题,Java 程序员又知道多少呢?

    1、如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引。

    lyb-geek
  • 面试中有哪些经典的数据库问题?

    1、如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯...

    Java后端技术

扫码关注云+社区

领取腾讯云代金券