使用索引的誤區之六:為索引列都建立索引

來源:互聯網
上載者:User
索引 使用索引的誤區之六:為所有列都建立索引
我們知道,建立索引是為了提高查詢的效率,但是同時也應該注意到,索引增加了對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等等,在下一個討論中,我們會對這些話題詳細闡述。

 

 


相關文章

Cloud Intelligence Leading the Digital Future

Alibaba Cloud ACtivate Online Conference, Nov. 20th & 21st, 2019 (UTC+08)

Register Now >

Starter Package

SSD Cloud server and data transfer for only $2.50 a month

Get Started >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。