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

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

http://blog.youkuaiyun.com/lunar2000/archive/2004/11/24/193501.aspx

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

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

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

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

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>

注意，转换时注意数据库中对字段的精度，如'yyyymmyy'，或者'yyyymmddhh24miss'

3，to_date函数

参见上面的方法

4，substr函数

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的效率，我们要仔细的分析应用，找出来出现频率相对较多、字段内容较少（比如varchar2(1000)就不适合建立索引，而varchar2(10)相对来说就适合建立索引）的列，合理的建立索引，比如有时候我们希望建立复合索引，有时候我们更希望建立单键索引。

事实上，oracle的索引还有很多话题，如监视索引的使用情况，Oracle 9i 推出的SKIP SCAN等等，在下一个讨论中，我们会对这些话题详细阐述。