使用索引的誤區(qū)之三：基于函數(shù)的索引
2024-07-21 02:11:39
供稿：網(wǎng)友
使用索引的誤區(qū)之三：基于函數(shù)的索引
使用基于函數(shù)的索引（bfi, based function index）：
從oracle 8i開始，可以使用基于函數(shù)的索引來提高查詢性能，
使用基于函數(shù)的索引，需要幾個(gè)條件：
1， 用戶需要有create index或者create any index權(quán)限
2， 用戶需要有query rewrite或者global query rewirte權(quán)限
3， 設(shè)置系統(tǒng)參數(shù) query_rewrite_enabled=true
和 query_rewrite_integrity=enforced
4， 設(shè)置系統(tǒng)參數(shù) ：compatible=8.1.0.0.0 或者更高
5，創(chuàng)建了bfi后，需要對表進(jìn)行分析
請看下面的例子：
首先，在沒有建立函數(shù)索引的情況下，我們看到查詢沒有如我們想想一樣使用單列（dname）索引：
sql> set autotrace traceonly
sql> select * from dept where substr(dname,1,5)='aaa';
未選定行
已用時(shí)間: 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
下面直接建立基于函數(shù)的索引，看看是否查詢是否可以使用我們建立的索引
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: 權(quán)限不足
已用時(shí)間: 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
已用時(shí)間: 00: 00: 00.00
sql> select username, granted_role from user_role_privs;
username granted_role
---------- ------------------------------
demo connect
demo resource
public plustrace
已用時(shí)間: 00: 00: 00.01
我們看到，雖然用戶有connect和resource角色，但是仍然沒有建立函數(shù)索引的權(quán)限。
我們使用sysdba身份登陸，給demo用戶授create any index 和 global query rewrite權(quán)限：
sql> conn lunar/[email protected] as sysdba
已連接。
sql> grant create any index to demo;
授權(quán)成功。
已用時(shí)間: 00: 00: 00.00
sql> grant global query rewrite to demo;
授權(quán)成功。
已用時(shí)間: 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
已用時(shí)間: 00: 00: 00.00
sql> select username, granted_role from user_role_privs;
username granted_role
---------- ------------------------------
demo connect
demo resource
public plustrace
已用時(shí)間: 00: 00: 00.00
再修改系統(tǒng)參數(shù)，將query_rewrite_enabled設(shè)置為true，這個(gè)參數(shù)是動(dòng)態(tài)參數(shù)，設(shè)置后可以有立桿見影的效果：
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;
系統(tǒng)已更改。
已用時(shí)間: 00: 00: 00.00
sql> show parameter query
name type value
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string true
query_rewrite_integrity string enforced
好了，再使用demo用戶登陸，創(chuàng)建函數(shù)索引
sql> conn demo/[email protected]
已連接。
sql> create index dept_id5 on dept(substr(dname,1,5));
索引已創(chuàng)建。
已用時(shí)間: 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
已用時(shí)間: 00: 00: 00.00
可見已經(jīng)創(chuàng)建成功了。
下面，我們看看查詢是否會(huì)使用我們創(chuàng)建的函數(shù)索引：
sql> set autotrace traceonly
sql> select * from dept where substr(dname,1,5)='aaa';
未選定行
已用時(shí)間: 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
在我們分析表之后，我們看到，查詢?nèi)缥覀兯Ｍ哪菢樱褂昧怂饕?br>
sql> analyze table dept compute statistics
2 for table
3 for all indexes
4 for all indexed columns;
表已分析。
已用時(shí)間: 00: 00: 00.02
sql> select * from dept where substr(dname,1,5)='aaa';
未選定行
已用時(shí)間: 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，我們可以清楚的看到，適當(dāng)?shù)氖褂盟饕龝?huì)是性能提高幾倍甚至更多。
