基于函数的索引

转自：http://blog.sina.com.cn/s/blog_3f2ef1180100mi3y.html

 

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