使用索引的誤區(qū)之六：為索引列都建立索引
2024-07-21 02:06:29
供稿：網(wǎng)友
使用索引的誤區(qū)之六：為所有列都建立索引
我們知道，建立索引是為了提高查詢的效率，但是同時(shí)也應(yīng)該注意到，索引增加了對(duì)dml操作（insert, update, delete）的代價(jià)，而且，一給中的索引如果太多，那么多數(shù)的索引是根本不會(huì)被使用到的，而另一方面我們維護(hù)這些不被使用的所以還要大幅度降低系統(tǒng)的性能。所以，索引不是越多越好，而是要恰到好處的使用。
比如說(shuō)，有些列由于使用了函數(shù)，我們要使用已有的索引（如一些復(fù)合索引）是不可能的（詳細(xì)請(qǐng)參見(jiàn)前面“函數(shù)索引”），那么就必須建立單獨(dú)的函數(shù)索引，如果說(shuō)這個(gè)函數(shù)索引很少會(huì)被應(yīng)用（僅僅在幾個(gè)特別的sql中會(huì)用到），我們就可以嘗試改寫(xiě)查詢，而不去建立和維護(hù)那個(gè)函數(shù)索引，例如：
c:/>sqlplus demo/demo
sql*plus: release 9.2.0.1.0 - production on sun oct 17 07:47:30 2004
copyright (c) 1982, 2002, oracle corporation. all rights reserved.
connected to:
oracle9i enterprise edition release 9.2.0.1.0 - production
with the olap and oracle data mining options
jserver release 9.2.0.1.0 - production
sql> show user
user is "demo"
sql> desc emp
name type nullable default comments
-------- ------------ -------- ------- --------
empno number(4)
ename varchar2(10) y
job varchar2(9) y
mgr number(4) y
hiredate date y
sal number(7,2) y
comm number(7,2) y
deptno number(2) y
sql> create index emp_id3 on emp(hiredate);
index created
1，trunc函數(shù)
sql> select empno,ename,deptno from emp where trunc(hiredate)='2004-01-01';
no rows selected
execution plan
----------------------------------------------------------
0 select statement optimizer=choose
1 0 table access (full) of 'emp'
statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
328 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
將上面的查詢轉(zhuǎn)換為：
sql> select empno,ename,deptno from emp
2 where hiredate >= to_date('2004-01-01','yyyy-mm-dd')
3 and hiredate<to_date('2004-01-01','yyyy-mm-dd')+0.999;
no rows selected
execution plan
----------------------------------------------------------
0 select statement optimizer=choose
1 0 table access (by index rowid) of 'emp'
2 1 index (range scan) of 'emp_id3' (non-unique)
statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
328 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>
2，to_char函數(shù)
sql> select empno,ename,deptno from emp
2 where to_char(hiredate,'yyyy-mm-dd')='2003-09-05';
no rows selected
execution plan
----------------------------------------------------------
0 select statement optimizer=choose
1 0 table access (full) of 'emp'
statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
328 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> select empno,ename,deptno from emp
2 where hiredate=to_date('2003-09-05','yyyy-mm-dd');
no rows selected
execution plan
----------------------------------------------------------
0 select statement optimizer=choose
1 0 table access (by index rowid) of 'emp'
2 1 index (range scan) of 'emp_id3' (non-unique)
statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
328 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>
注意，轉(zhuǎn)換時(shí)注意數(shù)據(jù)庫(kù)中對(duì)字段的精度，如'yyyymmyy'，或者'yyyymmddhh24miss'
3，to_date函數(shù)
參見(jiàn)上面的方法
4，substr函數(shù)
sql> desc dept
name null? type
----------------------------------------- -------- ----------------------------
deptno number(2)
dname varchar2(14)
loc varchar2(13)
sql> create index dept_id1 on dept(dname);
index created.
sql> select dname from dept where substr(dname,1,3)='abc';
no rows selected
execution plan
----------------------------------------------------------
0 select statement optimizer=choose
1 0 table access (full) of 'dept'
statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
221 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> select dname from dept where dname like 'abc%';
no rows selected
execution plan
----------------------------------------------------------
0 select statement optimizer=choose
1 0 index (range scan) of 'dept_id1' (non-unique)
statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
221 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>
通常，為了均衡查詢的效率和dml的效率，我們要仔細(xì)的分析應(yīng)用，找出來(lái)出現(xiàn)頻率相對(duì)較多、字段內(nèi)容較少（比如varchar2(1000)就不適合建立索引，而varchar2(10)相對(duì)來(lái)說(shuō)就適合建立索引）的列，合理的建立索引，比如有時(shí)候我們希望建立復(fù)合索引，有時(shí)候我們更希望建立單鍵索引。
事實(shí)上，oracle的索引還有很多話題，如監(jiān)視索引的使用情況，oracle 9i 推出的skip scan等等，在下一個(gè)討論中，我們會(huì)對(duì)這些話題詳細(xì)闡述。
