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

首先声明：本方法是受到dbsnake的指导，再次感谢指点。

通常来说，索引选取的数据列最好为分散度高、选择性好。从索引树结构的角度看，列值都是分布在叶节点位置。这样，通过树结构搜索得到的叶节点数量效率比较高。

实际中，我们常常遇到数据列值倾斜的情况。就是说，整个列数据取值有限。但是大部分数据值都集中在少数一两个取值里，其他取值比例极少。比如：一个数据列值有“N”、“B”、“M”、“P”、“Q”几个取值，其中55%数据行取值为“N”，40%数据行取值为“B”，剩下的取值分布在5%的数据行中。对于这种结构的数据列加索引，是存在一些问题的。

首先，默认数据库是会为所有的列值（非空）建立索引结构。也就意味着无论是高频度取值，还是低频度取值，都会在索引结构的叶节点上出现。当然，这样的大部分叶节点都是这些重复值。

其次，在CBO（基于成本优化器）的作用下，对高频度取值的搜索一般都不会选择索引作为搜索路径，因为进行全表扫描可能效率更高。我们为数据列建立了索引，但高频词的查询永远不会走到索引路径。

最后，建立的索引空间和时间消耗比较大。建立的索引涵盖所有取值，对海量数据表而言，占有的空间势必较大。同时，在进行小频度数据查询的时候，虽然会去走索引路径，但是引起的逻辑物理读也是有一些损耗。

引入一个解决方法，思路：既然高频度值在查询的时候不会走到索引路径，可以考虑将其剔出构建索引的过程，只为那些低频度数据值建立索引结构。这样，建立的索引树结构相对较小，而且索引查询的效率也能提升。

具体的方法是使用decode函数。decode(a,b,c,d,e…f)含义：如果a=b，则返回c,等于d,返回e,最后没有匹配的情况下，返回f。针对上面的例子，可以使用decode(列名，‘N’, null, ‘B’, null, 列名)，含义是，如果该列取值为N或者B，直接设置为null，否则才返回列值。并且以此建立函数索引。

这样做借助了Oracle两个功能：1、对null值不生成索引；2、函数索引；

下面的实验证明了该方法：

1、  构建数据环境

//数据准备

SQL> create table t as select * from dba_objects where 1=0;

Table created

//构造大数据环境，使用脚本

declare

  i number;

begin 

  for i in 1..40 loop    

     insert /*+ append */ into t

     select * from dba_objects;

     commit; 

  end loop;

end;

/

SQL> select count(*) from t;

  COUNT(*)

----------

   4759209

Executed in 15.522 seconds

整理后的数据环境如下：

//投入实验的数据状态

SQL> select secondary, count(*) from t group by secondary;

SECONDARY   COUNT(*)

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

W                273

Q                  9

D                273

T             421230

J            1866592

E                 99

S            2470733

7 rows selected

Executed in 18.002 seconds

可以看到，近五百万数据两种，绝大部分数据集中到了S、T、J上，其他数据取值频数较小。数据倾斜趋势明显。

2、  建索引

分别对secondary列建立常规、函数索引。

SQL>create index IND_SEC_NORMAL on t(secondary);

Index created

SQL> create index ind_t_fun on t(decode (secondary, 'S', null, 'J', null, 'T', null, secondary ));

Index created

Executed in 28.049 seconds

索引ind_t_fun将S、T、J值转化为null，剔出了建立索引的过程。从索引段信息看，两个索引所占的空间差异比较大，也证明了这点。

SQL> select * from dba_segments where segment_name='IND_SEC_NORMAL';

OWNER    SEGMENT_NAME  SEGMENT_TYPE             BYTES     BLOCKS    EXTENTS

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

SYS      IND_T_FUN     INDEX                 75497472       9216         80  

Executed in 0.733 seconds

SQL> select * from dba_segments where segment_name=upper('ind_t_fun');

OWNER  SEGMENT_NAME  SEGMENT_TYPE          BYTES     BLOCKS    EXTENTS

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

SYS    IND_T_FUN     INDEX                 65536          8          1  

Executed in 0.156 seconds

注：本结果经过额外处理，用于方便显示；

可以看出，同样是对一个数据列加索引。普通索引类型Ind_sec_normal占据80个区，9216个数据块，空间约占75.5M。而函数索引ind_t_fun的空间只用了初始分配的1个区，8个数据块，空间约占65K。由此，空间优势立现！

收集统计数据，由于是实验性质，而且数据量大，采用高采样率收集统计信息。

SQL> exec dbms_stats.gather_table_stats(user, 'T', cascade => true, estimate_percent => 100,method_opt => 'for all indexed columns');

PL/SQL procedure successfully completed

Executed in 60.403 seconds

3、  检索效率分析

针对数据量273的W取值进行分析。

直接索引搜索：

SQL> select * from t where secondary='W';

已选择273行。

已用时间:  00: 00: 00.37

执行计划

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

Plan hash value: 1573525374

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

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                |   273 | 25935 |    11   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T              |   273 | 25935 |    11   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_SEC_NORMAL |   273 |       |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("SECONDARY"='W')

统计信息

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

        775  recursive calls

          0  db block gets

        272  consistent gets

         21  physical reads

          0  redo size

      28339  bytes sent via SQL*Net to client

        583  bytes received via SQL*Net from client

         20  SQL*Net roundtrips to/from client

         16  sorts (memory)

          0  sorts (disk)

        273  rows processed

发现采用W作为搜索值时，是进行了索引搜索。下面是用函数索引搜索进行对比。

SQL> select * from t where decode(secondary,'S',null,'J',null,'T',null,secondary)='W';

已选择273行。

已用时间:  00: 00: 00.04

执行计划

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

Plan hash value: 3192598969

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

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |           |   273 | 25935 |   116   (0)| 00:00:02 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T         |   273 | 25935 |   116   (0)| 00:00:02 |

|*  2 |   INDEX RANGE SCAN          | IND_T_FUN |   273 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access(DECODE("SECONDARY",'S',NULL,'J',NULL,'T',NULL,"SECONDARY")='W')

统计信息

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

         45  recursive calls

          0  db block gets

        140  consistent gets

          0  physical reads

          0  redo size

      13225  bytes sent via SQL*Net to client

        583  bytes received via SQL*Net from client

         20  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

        273  rows processed

对比后，我们可以发现，使用函数索引的方法，在执行时间、物理逻辑读、CPU使用上有一定差异。

结论：使用函数索引处理偏值方法，在一定长度上优化查询效率和索引结构。上表的数据表明，会使逻辑物理读的消耗很大程度的减少（索引结构简化），同时连带影响执行时间的缩小。因为使用函数要进行计算，CPU使用率相对较高，在可以接受的范围内。

但是，这种方法是存在一些限制的，应用前一定要仔细规划。

首先，数据表数据要保证较大。因为毕竟函数索引的建立和搜索较普通索引消耗大，如果数据表小，带来的优化程度不能弥补消耗的成本，结果可能得不偿失。笔者进行的一系列实验中，也发现在数据量中等偏小时，这种性能优势不能凸显。

其次，列值倾斜趋势明显。通过开篇的讨论我们不难发现，列值倾斜的程度越高，使用函数索引剔出的数据量也就越大，生成的索引树结构也就越小越优化。这一点是本方法的核心！

最后，使用函数索引搜索时，搜索的取值频数越高，优化效果越好。在本例中，取值W的列有273行，可以看出明显的性能优化。当我们选择值有9条数据的Q值时，这种优化趋势可以看到，但是明显程度降低（实验结果略）。这里的原因可能是数据量小时，两种方法逻辑物理读的差异度缩小。

About Me

............................................................................................................................................. ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文博客园地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ............................................................................................................................................. ● QQ群号：230161599（满）、618766405 ● 微信群：可加我微信，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友（646634621），注明添加缘由 ● 于 2017-11-01 09:00 ~ 2017-11-30 22:00 在魔都完成 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ............................................................................................................................................. ● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/ ............................................................................................................................................. 使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。    小麦苗的微信公众号      小麦苗的DBA宝典QQ群2     《DBA笔试面宝典》读者群       小麦苗的微店 .............................................................................................................................................