使用索引的误区之三：基于函数的索引

使用索引的误区之三：基于函数的索引

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