使用索引的误区之三：基于函数的索引
使用基于函数的索引（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，我们可以清楚的看到，适当的使用索引会是性能提高几倍甚至更多。
