Oracle索引梳理系列（六）- Oracle索引种类之函数索引

2021-09-24 23:21:21

版权声明：本文发布于http://www.cnblogs.com/yumiko/，版权由Yumiko_sunny所有，欢迎转载。转载时，请在文章明显位置注明原文链接。若在未经作者同意的情况下，将本文内容用于商业用途，将保留追究其法律责任的权利。如果有问题，请以邮箱方式联系作者（793113046@qq.com）。

函数索引

1.1 概述

在实际应用中，当条件列使用函数运算进行数据匹配时，即使该列建立了索引，索引也不会被使用。

如下示例，其中在owner列上建立一个普通b-tree索引,观察两种查询方式（不使用UPPER函数及使用UPPER函数）的执行计划的区别。

--查看表上的数据分布情况，可以确定，对于索引列owner，针对scott以及bi的普通查询，一定会使用索引。
Yumiko@Sunny >select owner,count(*) from test01 group by owner;

OWNER                            COUNT(*)

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

SCOTT                                  11

BI                                      8

SYS                                 22909

--为owner列创建普通b-tree索引

Yumiko@Sunny >create index btree_owner on test01(owner);

Index created.

--验证创建的索引

Yumiko@Sunny >select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS

  2  from user_ind_columns a,user_indexes b

  3  where a.INDEX_NAME=b.INDEX_NAME and a.table_name='TEST01';

INDEX_NAME      INDEX_TYPE      TABLE_NAME      COLUMN_NAME          STATUS

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

BTREE_OWNER     NORMAL          TEST01          OWNER                VALID

--利用索引列，针对列值为BI，进行普通查询
--与预想一样，这里用到了索引扫描

Yumiko@Sunny >select * from test01 where owner='BI';

8 rows selected.

Execution Plan

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

Plan hash value: 725909888

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

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

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

|   0 | SELECT STATEMENT            |             |     1 |    92 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST01      |     1 |    92 |     2   (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

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

Statistics

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

          0  recursive calls

          0  db block gets

          5  consistent gets

         16  physical reads

          0  redo size

       2010  bytes sent via SQL*Net to client

        469  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          8  rows processed


--清空buffer_cache缓冲区，避免影响后续操作对于物理读的观察。

Yumiko@Sunny >alter system flush buffer_cache;

System altered.

--使用UPPER函数进行条件过滤，并观察执行计划

--通过执行计划，可以明显看出，未使用索引扫描，进而导致大量的物理读操作。
Yumiko@Sunny >select * from test01 where UPPER(owner)='BI';

8 rows selected.

Execution Plan

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

Plan hash value: 262542483

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

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

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

|   0 | SELECT STATEMENT  |        |   229 | 21068 |   158   (1)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| TEST01 |   229 | 21068 |   158   (1)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - filter(UPPER("OWNER")='BI')

Statistics

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

          1  recursive calls

          0  db block gets

        697  consistent gets
692  physical reads
0  redo size

       1583  bytes sent via SQL*Net to client

        469  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          8  rows processed

通过上面的示例可以看到，即使条件列建立了索引，当索引列上使用函数进行条件匹配，执行计划将不会选择索引扫描。

1.2 函数索引介绍

为了避免由于在条件匹配时引入函数，导致执行计划不再使用索引，oracle提供了基于函数的索引，进而解决上述问题，提高访问效率。

需要注意的是：
在使用函数索引时，SQL语句中的条件表达式必须与函数索引的表达式完全一致，空格、关键字大小写的可以忽略。如果不完全一致，则无法利用函数索引。

1.3 函数索引示例

紧接上面的例子，这里针对上面示例中，条件出现的函数运算UPPER(owner)建立函数索引。

--在索引列上建立函数索引
Yumiko@Sunny >create index func_owner on test01(UPPER(owner));

Index created.

--查看并验证建立的函数索引
--需要注意的，由于此索引是基于函数建立的，因此columns一列无法显示真正的列名，可以通过user_ind_expressions视图查看

Yumiko@Sunny >select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS

  2  from user_ind_columns a,user_indexes b

  3  where a.INDEX_NAME=b.INDEX_NAME and a.table_name='TEST01';

INDEX_NAME      INDEX_TYPE                TABLE_NAME      COLUMN_NAME          STATUS

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

FUNC_OWNER      FUNCTION-BASED NORMAL     TEST01          SYS_NC00014$         VALID

BTREE_OWNER     NORMAL                    TEST01          OWNER                VALID

Yumiko@Sunny >select * from user_ind_expressions where INDEX_NAME='FUNC_OWNER';

INDEX_NAME      TABLE_NAME      COLUMN_EXPRESSION              COLUMN_POSITION

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

FUNC_OWNER      TEST01          UPPER("OWNER")                               1

--打开会话追踪

Yumiko@Sunny >set autotrace trace;

--清空buffer_cache缓冲区

Yumiko@Sunny >alter system flush buffer_cache;

System altered.

--再次使用UPPER函数进行条件查询，此时执行计划使用索引扫描，进而物理读明显降低。

Yumiko@Sunny >select * from test01 where upper(owner)='BI';

8 rows selected.

Execution Plan

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

Plan hash value: 939299437

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

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

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

|   0 | SELECT STATEMENT            |            |   229 | 21068 |    19   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST01     |   229 | 21068 |    19   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | FUNC_OWNER |    92 |       |    16   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access(UPPER("OWNER")='BI')

Statistics

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

         32  recursive calls

          0  db block gets

          8  consistent gets
19  physical reads
0  redo size

       1583  bytes sent via SQL*Net to client

        469  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          8  rows processed

通过上面的示例可以看到，由于建立了函数索引，执行计划重新选择了索引扫描，物理读（physical reads）明显降低。

1.4 常用的oracle索引视图

较为重要的oracle索引视图如下：
dba_indexes
user_indexes
dba_ind_columns
user_indexes
dba_expressions
user_expressions

其中：
dba_indexes与user_indexes视图，主要涵盖了索引的参数、状态以及关联的表信息，但不包含具体的列信息。
dba_ind_columns与user_ind_columns视图，主要涉及具体的索引列的信息。
dba_expressions与user_expressions视图，主要针对函数索引，可以查看具体的函数信息。

上一篇：SQL常用日期转换


下一篇：Oracle索引梳理系列（四）- Oracle索引种类之位图索引