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

使用索引的误区之六：为所有列都建立索引
我们知道，建立索引是为了提高查询的效率，但是同时也应该注意到，索引增加了对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等等，在下一个讨论中，我们会对这些话题详细阐述。