使用索引的误区之三：基于函数的索引
2024-07-21 02:11:39
供稿：网友
使用索引的误区之三：基于函数的索引
使用基于函数的索引（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/[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 demo/[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 /@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/[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，我们可以清楚的看到，适当的使用索引会是性能提高几倍甚至更多。
