• 欢迎访问搞代码网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站!
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏搞代码吧

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

sqlserver 搞代码 3年前 (2018-06-16) 102次浏览 已收录 0个评论

使用索引的误区之三:基于函数的索引
使用基于函数的索引(BFI, Based Function Index):

oracle 8i开始,可以使用基于函数的索引来提高查询性能,

 

http://www.gaodaima.com/35910.html使用索引的误区之三:基于函数的索引_sqlserver

使用基于函数的索引,需要几个条件:

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 [email protected] 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 [email protected]

已连接。

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 [email protected] 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 [email protected]

已连接。

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

欢迎大家阅读《使用索引的误区之三:基于函数的索引_sqlserver,跪求各位点评,若觉得好的话请收藏本文,by 搞代码


喜欢 (0)
[搞代码]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址