使用索引的误区之六：为索引列都建立索引

使用索引的误区之六：为所有列都建立索引

我们知道，建立索引是为了提高查询的效率，但是同时也应该注意到，索引增加了对 DML 操作（ insert, update, delete ）的代价，而且，一给中的索引如果太多，那么多数的索引是根本不会被使用到的，而另一方面我们维护这些不被使用的所以还要大幅度降低系统的性能。所以，索引不是越多越好，而是要恰到好处的使用。

** 比如说，有些列由于使用了函数，我们要使用已有的索引（如一些复合索引）是不可能的（详细请参见前面“函数索引”），那么就必须建立单独的函数索引，如果说这个函数索引很少会被应用（仅仅在几个特别的 sql 中会用到），我们就可以尝试改写查询，而不去建立和维护那个函数索引，例如： **

C:>sqlplus dem o/demo

SQL*Plus: Release 9.2.0.1.0 - Production on Sun Oct 17 07:47:30 2004

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 函数 **

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

将上面的查询转换为：

SQL> select empno,ename,deptno from emp

2 where hiredate >= to_date('2004-01-01','yyyy-mm-dd')

3 and hiredate

  1<to_date('2004-01-01','yyyy-mm-dd')+0.999; 'emp'="" 'emp_id3'="" (by="" (disk)="" (memory)="" (non-unique)="" (range="" 0="" 1="" 2="" 328="" 372="" \----------------------------------------------------------="" access="" block="" bytes="" calls="" client="" consistent="" db="" execution="" from="" gets="" index="" no="" of="" optimizer="CHOOSE" physical="" plan="" processed="" reads="" received="" recursive="" redo="" roundtrips="" rowid)="" rows="" scan)="" select="" selected="" sent="" size="" sorts="" sql="" sql*net="" statement="" statistics="" table="" to="" via="">
  2
  3** 2  ** ** ，  to_char  函数  **
  4
  5SQL&gt; select empno,ename,deptno from emp 
  6
  72  where to_char(hiredate,'yyyy-mm-dd')='2003-09-05'; 
  8
  9no rows selected 
 10
 11Execution Plan 
 12
 13\---------------------------------------------------------- 
 14
 150  SELECT STATEMENT Optimizer=CHOOSE 
 16
 171  0  TABLE ACCESS (FULL) OF 'EMP' 
 18
 19Statistics 
 20
 21\---------------------------------------------------------- 
 22
 230  recursive calls 
 24
 250  db block gets 
 26
 273  consistent gets 
 28
 290  physical reads 
 30
 310  redo size 
 32
 33328  bytes sent via SQL*Net to client 
 34
 35372  bytes received via SQL*Net from client 
 36
 371  SQL*Net roundtrips to/from client 
 38
 390  sorts (memory) 
 40
 410  sorts (disk) 
 42
 430  rows processed 
 44
 45SQL&gt; select empno,ename,deptno from emp 
 46
 472  where hiredate=to_date('2003-09-05','yyyy-mm-dd'); 
 48
 49no rows selected 
 50
 51Execution Plan 
 52
 53\---------------------------------------------------------- 
 54
 550  SELECT STATEMENT Optimizer=CHOOSE 
 56
 571  0  TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 
 58
 592  1  INDEX (RANGE SCAN) OF 'EMP_ID3' (NON-UNIQUE) 
 60
 61Statistics 
 62
 63\---------------------------------------------------------- 
 64
 650  recursive calls 
 66
 670  db block gets 
 68
 691  consistent gets 
 70
 710  physical reads 
 72
 730  redo size 
 74
 75328  bytes sent via SQL*Net to client 
 76
 77372  bytes received via SQL*Net from client 
 78
 791  SQL*Net roundtrips to/from client 
 80
 810  sorts (memory) 
 82
 830  sorts (disk) 
 84
 850  rows processed 
 86
 87SQL&gt;
 88
 89** 注意，转换时注意数据库中对字段的精度，如  'yyyymmyy'  ，或者  'yyyymmddhh24miss'  **
 90
 91** 3  ** ** ，  to_date  函数  **
 92
 93参见上面的方法 
 94
 954  ，  substr  函数 
 96
 97SQL&gt; desc dept 
 98
 99Name  Null?  Type 
100
101\----------------------------------------- -------- ---------------------------- 
102
103DEPTNO  NUMBER(2) 
104
105DNAME  VARCHAR2(14) 
106
107LOC  VARCHAR2(13) 
108
109SQL&gt; create index dept_id1 on dept(dname); 
110
111Index created. 
112
113SQL&gt; select dname from dept  where substr(dname,1,3)='abc'; 
114
115no rows selected 
116
117Execution Plan 
118
119\---------------------------------------------------------- 
120
1210  SELECT STATEMENT Optimizer=CHOOSE 
122
1231  0  TABLE ACCESS (FULL) OF 'DEPT' 
124
125Statistics 
126
127\---------------------------------------------------------- 
128
1290  recursive calls 
130
1310  db block gets 
132
1337  consistent gets 
134
1350  physical reads 
136
1370  redo size 
138
139221  bytes sent via SQL*Net to client 
140
141372  bytes received via SQL*Net from client 
142
1431  SQL*Net roundtrips to/from client 
144
1450  sorts (memory) 
146
1470  sorts (disk) 
148
1490  rows processed 
150
151SQL&gt; select dname from dept  where dname like 'abc%'; 
152
153no rows selected 
154
155Execution Plan 
156
157\---------------------------------------------------------- 
158
1590  SELECT STATEMENT Optimizer=CHOOSE 
160
1611  0  INDEX (RANGE SCAN) OF 'DEPT_ID1' (NON-UNIQUE) 
162
163Statistics 
164
165\---------------------------------------------------------- 
166
1670  recursive calls 
168
1690  db block gets 
170
1711  consistent gets 
172
1730  physical reads 
174
1750  redo size 
176
177221  bytes sent via SQL*Net to client 
178
179372  bytes received via SQL*Net from client 
180
1811  SQL*Net roundtrips to/from client 
182
1830  sorts (memory) 
184
1850  sorts (disk) 
186
1870  rows processed 
188
189&lt;SPAN lang=EN-US style="mso-hansi</to_date('2004-01-01','yyyy-mm-dd')+0.999;>