你的位置:首页 > 数据库

[数据库]Oracle索引总结(六)


函数索引

1.1 概述

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

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

--查看表上的数据分布情况,可以确定,对于索引列owner,针对scott以及bi的普通查询,一定会使用索引。
Yumiko@Sunny >select owner,count(*) from test01 group by owner;OWNER COUNT(*)------------------------------ ----------SCOTT 11BI 8SYS 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$ VALIDBTREE_OWNER NORMAL TEST01 OWNER VALIDYumiko@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视图,主要针对函数索引,可以查看具体的函数信息。