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

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

使用基于函数的索引(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

 

我们看到,虽然用户有connectresource角色,但是仍然没有建立函数索引的权限。

 

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

评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值