使用索引的误区之三：基于函数的索引
使用基于函数的索引（ BFI, Based Function Index ）：
从 Oracle 8i 开始，可以使用基于函数的索引来提高查询性能，
使用基于函数的索引，需要几个条件：
1， 用户需要有 create index 或者 create any index 权限
2， 用户需要有 query rewrite 或者 global query rewirte 权限
3， 设置系统参数 query_rewrite_enabled=TRUE
和 query_rewrite_integrity=enforced
4， 设置系统参数 ： COMPATIBLE= 8.1.0 .0.0 或者更高
5 ，创建了 BFI 后，需要对表进行分析
请看下面的例子：
** 首先，在没有建立函数索引的情况下，我们看到查询没有如我们想想一样使用单列（ dname ）索引： **
SQL> set autotrace traceonly
SQL> select * from dept where substr(dname,1,5)='aaa';
未选定行
已用时间 : 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'
Statistics
----------------------------------------------------------
134 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
323 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
** 下面直接建立基于函数的索引，看看是否查询是否可以使用我们建立的索引 **
SQL> create index dept_id5 on dept(substr(dname,1,5));
create index dept_id5 on dept(substr(dname,1,5))
ERROR 位于第 1 行 :
ORA-01031: 权限不足
已用时间 : 00: 00: 00.00
SQL> set autotrace off
SQL> col username format a10
SQL> col privilege format a20
SQL> select username,privilege from user_sys_privs;
USERNAME PRIVILEGE
---------- --------------------
DEMO UNLIMITED TABLESPACE
PUBLIC SELECT ANY TABLE
已用时间 : 00: 00: 00.00
SQL> select username, granted_role from user_role_privs;
USERNAME GRANTED_ROLE
---------- ------------------------------
DEMO CONNECT
DEMO RESOURCE
PUBLIC PLUSTRACE
已用时间 : 00: 00: 00.01
** 我们看到，虽然用户有 connect 和 resource 角色，但是仍然没有建立函数索引的权限。 **
** 我们使用 sysdba 身份登陆，给 demo 用户授 create any index 和 global query rewrite 权限： **
SQL> conn lunar/lunar@test1 as sysdba
已连接。
** SQL> grant create any index to demo; **
授权成功。
已用时间 : 00: 00: 00.00
** SQL> grant global query rewrite to demo; **
授权成功。
已用时间 : 00: 00: 00.00
SQL> conn demo/demo@test1
已连接。
SQL> select username,privilege from user_sys_privs;
USERNAME PRIVILEGE
---------- --------------------
DEMO CREATE ANY INDEX
DEMO GLOBAL QUERY REWRITE
DEMO UNLIMITED TABLESPACE
PUBLIC SELECT ANY TABLE
已用时间 : 00: 00: 00.00
SQL> select username, granted_role from user_role_privs;
USERNAME GRANTED_ROLE
---------- ------------------------------
DEMO CONNECT
DEMO RESOURCE
PUBLIC PLUSTRACE
已用时间 : 00: 00: 00.00
** 再修改系统参数，将 query_rewrite_enabled 设置为 true ，这个参数是动态参数，设置后可以有立杆见影的效果： **
SQL> conn /@test1 as sysdba
已连接。
SQL> show parameter query
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string FALSE
query_rewrite_integrity string enforced
SQL> alter system set query_rewrite_enabled=true;
系统已更改。
已用时间 : 00: 00: 00.00
SQL> show parameter query
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
好了，再使用 demo 用户登陆，创建函数索引
SQL> conn demo/demo@test1
已连接。
** SQL> create index dept_id5 on dept(substr(dname,1,5)); **
** 索引已创建。 ** ** **
** 已用时间 ** ** : 00: 00: 00.00 **
SQL> select index_type,index_name from user_indexes where table_name='DEPT';
INDEX_TYPE INDEX_NAME
--------------------------- ------------------------------
FUNCTION-BASED NORMAL DEPT_ID5
已用时间 : 00: 00: 00.00
可见已经创建成功了。
** 下面，我们看看查询是否会使用我们创建的函数索引： **
SQL> set autotrace traceonly
SQL> select * from dept where substr(dname,1,5)='aaa';
未选定行
已用时间 : 00: 00: 00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'
Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
323 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
** 在我们分析表之后，我们看到，查询如我们所希望的那样，使用了索引。 **
** SQL> analyze table dept compute statistics **
** 2 for table **
** 3 for all indexes **
** 4 for all indexed columns; **
表已分析。
已用时间 : 00: 00: 00.02
** SQL> select * from dept where substr(dname,1,5)='aaa'; **
未选定行
已用时间 : 00: 00: 00.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=23)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=2 Card=1 Byt
es=23)
2 1 INDEX (RANGE SCAN) OF 'DEPT_ID5' (NON-UNIQUE) (Cost=1 Ca
rd=1)
Statistics
----------------------------------------------------------
** 0 recursive calls **
** 0 db block gets **
** 1 consistent gets **
** 0 physical reads **
** 0 redo size **
323 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
通过所有的 statistics ，我们可以清楚的看到，适当的使用索引会是性能提高几倍甚至更多。