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

AiDBA宝典

发布于 2019-09-29 15:27:10

1.5K0

发布于 2019-09-29 15:27:10

文章被收录于专栏：小麦苗的DB宝专栏

♣

题目部分

在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来查询。