合 Oracle之函数索引

2022年11月8日

0 588 1

👉 本文共约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.

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

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

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

原文地址：说说函数索引作者：realkid4

我们进行数据库检索优化的方法，通常是对特定条件列加索引，减少由于全表扫描带来的逻辑物理IO消耗。索引的种类很多，我们经常使用的B树索引，由于结构简单、适应性强，可以应对大多数数据访问优化需求。除B树索引外，其他一些索引类型，也在一些场合中扮演着独特的地位。本篇来介绍其中的函数索引。

1**、从B*树索引的失效谈起**

和通常一样，我们准备实验环境。

SQL> select * from v$version where rownum<2;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL> create table t as select * from dba_objects;

Table created

//构建两个索引用作实验对象

SQL> create index idx_t_owner on t(owner);

Index created

SQL> create index idx_t_ddlt on t(last_ddl_time);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

环境中，我们在数据表T上建立了一般意义的索引。当我们进行检索的时候，CBO会适时选择合适的索引执行计划。

SQL> explain plan for select * from t where owner='SCOTT';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1516787156

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2419 | 229K| 71 (0)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 2419 | 229K| 71 (0)|

|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 2419 | | 6 (0)|

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OWNER"='SCOTT')

14 rows selected

但是，很多时候，我们可能会遇到在where条件里对索引列进行函数处理的情况。比如，选择owner列取值第二个字母是“c”的数据列，或者选取在特定天进行ddl操作的对象信息。这样的情况下，直接的想法就是在where条件列中加入列函数处理，但是这样做，会带来B*树索引的失效问题。

SQL> explain plan for select * from t where substr(owner,2,1)='C';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 726 | 70422 | 283 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T | 726 | 70422 | 283 (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(SUBSTR("OWNER",2,1)='C')

13 rows selected

在对条件列owner进行substr操作之后，生成的执行计划就不会带有索引路径，出现全表扫描。如果列上的B*树普通索引就是为该查询对应的用例服务的，那么这个索引的存在就失去了意义。

那么，这种时候应该如何处理呢？答案是：在SQL语句本身不存在重构优化的空间时（此种情况通常出现在系统的运维阶段），可以考虑使用函数索引来解决问题。

2**、函数索引**

函数索引与通常B*树索引的结构，存在很大相似性。区别就在于形成树结构的叶子节点上，保存的不是索引列的取值，而是经过特定的函数处理过的索引列值。

这样的结构，进行搜索的时候，就可以直接使用到函数索引的叶子节点，获取到对应的rowid集合。要求是出现于构建函数索引完全相同的函数条件。

首先，我们来构建函数索引。

SQL> create index idx_t_ownerf on t(substr(owner,2,1));

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

构建函数索引的语法和一般索引的语法没有过多的区别，最大的差异就是在声明索引列的位置上，写清楚应用的函数语句。此时，数据字典视图系列中，已经反映出函数索引的不同。

SQL> select index_type from dba_indexes where index_name='IDX_T_OWNERF';

INDEX_TYPE

---------------------------

FUNCTION-BASED NORMAL

此时，我们再进行查询，执行计划会发生变化。

SQL> explain plan for select * from t where substr(owner,2,1)='C';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2485331276

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4839 | 467K| 135 (0)

| 1 | TABLE ACCESS BY INDEX ROWID| T | 4839 | 467K| 135 (0)

|* 2 | INDEX RANGE SCAN | IDX_T_OWNERF | 4839 | | 9 (0)

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access(SUBSTR("OWNER",2,1)='C')

14 rows selected

加入函数索引之后，我们可以发现同样的SQL语句，执行计划发生变化。函数索引开始起效。

那么，函数索引的本质是什么呢？我们检查数据字典视图，就可以发现函数索引的本质。

SQL> col table_name for a20;

SQL> col table_owner for a20;

SQL> col column_name for a30;

SQL> select table_owner, table_name, column_name from dba_ind_columns where index_name='IDX_T_OWNERF';

TABLE_OWNER TABLE_NAME COLUMN_NAME

SYS T SYS_NC00016$

SQL> select column_expression from dba_ind_expressions where index_name = 'IDX_T_OWNERF';

COLUMN_EXPRESSION

-------------------------------------

SUBSTR("OWNER",2,1)

SQL> select column_name,data_type,data_default from dba_tab_cols where wner='SYS' and table_name='T' and column_name='SYS_NC00016$';

COLUMN_NAME DATA_TYPE DATA_DEFAULT

SYS_NC00016$ VARCHAR2 SUBSTR("OWNER",2,1)

检查了三个视图的情况，我们可以清楚的看出Oracle函数索引的本质。Oracle建立函数索引之后，就会先建立出一个不可见的内部列（SYS_NC00016$）。之后，对这个列建立普通的B*树索引。为了保证该列在不受影响的情况下进行数据生成，使用默认值技术，在数据插入或者变化的时候，进行同步。

3**、函数索引使用**

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(1)

标签： Oracle

小麦苗

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

发表回复取消回复

要发表评论，您必须先登录。

合 Oracle之函数索引

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 11 月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

合 Oracle之函数索引

相关文章

您可能还喜欢...

【MOS】诊断 ’library cache: mutex X’ 等待 (Doc ID 2331144.1) Troubleshooting 'library cache: mutex X' Waits. (Doc ID 1357946.1)

Oracle中的SQL_TRACE是什么？诊断事件event是什么？常用的events有哪些？常用的10046及10053诊断事件的区别是什么？

Oracle 物理读、逻辑读、一致性读、当前模式读

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复