oracle的索引類型有：非唯一索引、唯一索引、位圖索引、局部有前綴分區(qū)索引、局部無前綴分區(qū)索引、全局有前綴分區(qū)索引、散列分區(qū)索引、基于函數(shù)的索引。索引需在表中插入數(shù)據(jù)后創(chuàng)建，唯一索引可用“create unique index”語句創(chuàng)建。

本教程操作環(huán)境：Windows7系統(tǒng)、Oracle 11g版、Dell G3電腦。

什么是索引？

• 索引是建立在表的一列或多個列上的輔助對象，目的是加快訪問表中的數(shù)據(jù)；
• Oracle存儲索引的數(shù)據(jù)結(jié)構是B*樹（平衡樹），位圖索引也是如此，只不過是葉子節(jié)點不同B*數(shù)索引；

• 索引由根節(jié)點、分支節(jié)點和葉子節(jié)點組成，上級索引塊包含下級索引塊的索引數(shù)據(jù)，葉節(jié)點包含索引數(shù)據(jù)和確定行實際位置的rowid。

索引說明

1）索引是數(shù)據(jù)庫對象之一，用于加快數(shù)據(jù)的檢索，類似于書籍的索引。在數(shù)據(jù)庫中索引可以減少數(shù)據(jù)庫程序查詢結(jié)果時需要讀取的數(shù)據(jù)量，類似于在書籍中我們利用索引可以不用翻閱整本書即可找到想要的信息。

2）索引是建立在表上的可選對象；索引的關鍵在于通過一組排序后的索引鍵來取代默認的全表掃描檢索方式，從而提高檢索效率

3）索引在邏輯上和物理上都與相關的表和數(shù)據(jù)無關，當創(chuàng)建或者刪除一個索引時，不會影響基本的表；

4）索引一旦建立，在表上進行DML操作時（例如在執(zhí)行插入、修改或者刪除相關操作時），oracle會自動管理索引，索引刪除，不會對表產(chǎn)生影響

5）索引對用戶是透明的，無論表上是否有索引，sql語句的用法不變

6）oracle創(chuàng)建主鍵時會自動在該列上創(chuàng)建索引

使用索引的目的：

• 加快查詢速度
• 減少I/O操作
• 消除磁盤排序（索引能加快排序速度）

何時使用索引：

• 查詢返回的記錄數(shù) 排序表<40%，對非排序表<7%
• 表的碎片較多（頻繁增加、刪除）

索引的種類

• 非唯一索引（最常用）
• 唯一索引
• 位圖索引
• 局部有前綴分區(qū)索引
• 局部無前綴分區(qū)索引
• 全局有前綴分區(qū)索引
• 散列分區(qū)索引
• 基于函數(shù)的索引

管理索引的準則

• 在表中插入數(shù)據(jù)后創(chuàng)建索引
• 在用SQL*Loader或import工具插入或裝載數(shù)據(jù)后，建立索引比較有效；

索引正確的表和列

• 經(jīng)常檢索排序大表中40%或非排序表7%的行，建議建索引；
• 為了改善多表關聯(lián)，索引列用于聯(lián)結(jié)；
• 列中的值相對比較唯一；
• 取值范圍（大：B*樹索引，?。何粓D索引）；
• Date型列一般適合基于函數(shù)的索引；
• 列中有許多空值，不適合建立索引

為性能而安排索引列

• 經(jīng)常一起使用多個字段檢索記錄，組合索引比單索引更有效；
• 把最常用的列放在最前面，例：dx_groupid_serv_id(groupid,serv_id)，在where條件中使用groupid或groupid,serv_id，查詢將使用索引，若僅用到serv_id字段，則索引無效；

合并/拆分不必要的索引。

限制每個表索引的數(shù)量

• 一個表可以有幾百個索引（你會這樣做嗎？），但是對于頻繁插入和更新表，索引越多系統(tǒng)CPU，I/O負擔就越重；

• 建議每張表不超過5個索引。

刪除不再需要的索引

• 索引無效，集中表現(xiàn)在該使用基于函數(shù)的索引或位圖索引，而使用了B*樹索引；

• 應用中的查詢不使用索引；

• 重建索引之前必須先刪除索引，若用alter index … rebuild重建索引，則不必刪除索引。

索引數(shù)據(jù)塊空間使用

• 創(chuàng)建索引時指定表空間，特別是在建立主鍵時，應明確指定表空間；
• 合理設定pctfress，注意：不能給索引指定pctused；
• 估計索引的大小和合理地設置存儲參數(shù)，默認為表空間大小，或initial與next設置成一樣大。

考慮并行創(chuàng)建索引

• 對大表可以采用并行創(chuàng)建索引，在并行創(chuàng)建索引時，存儲參數(shù)被每個查詢服務器進程分別使用，例如：initial為1M，并行度為8，則創(chuàng)建索引期間至少要消耗8M空間；

考慮用nologging創(chuàng)建索引

• 對大表創(chuàng)建索引可以使用nologging來減少重做日志；
• 節(jié)省重做日志文件的空間；
• 縮短創(chuàng)建索引的時間；
• 改善了并行創(chuàng)建大索引時的性能。

怎樣建立最佳索引?

明確地創(chuàng)建索引

create index index_name on table_name(field_name) 　　tablespace tablespace_name 　　pctfree 5 　　initrans 2 　　maxtrans 255 　　storage 　　( 　　minextents 1 　　maxextents 16382 　　pctincrease 0 　　);

創(chuàng)建基于函數(shù)的索引:

常用與UPPER、LOWER、TO_CHAR(date)等函數(shù)分類上，例：

create index idx_func on emp (UPPER(ename)) tablespace tablespace_name;

創(chuàng)建位圖索引:

對基數(shù)較小，且基數(shù)相對穩(wěn)定的列建立索引時，首先應該考慮位圖索引，例：

create bitmap index idx_bitm on class (classno) tablespace tablespace_name;

明確地創(chuàng)建唯一索引

可以用create unique index語句來創(chuàng)建唯一索引，例：

create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;

創(chuàng)建與約束相關的索引

可以用using index字句，為與unique和primary key約束相關的字段創(chuàng)建索引，例如：

alter table table_name 　　add constraint PK_primary_keyname primary key (field_name) 　　using index tablespace tablespace_name；

如何創(chuàng)建局部分區(qū)索引

• 基礎表必須是分區(qū)表；
• 分區(qū)數(shù)量與基礎表相同；
• 每個索引分區(qū)的子分區(qū)數(shù)量與相應的基礎表分區(qū)相同；
• 基礎表的子分區(qū)中的行的索引項，被存儲在該索引的相應的子分區(qū)中,例如:

　　Create Index TG_CDR04_SERV_ID_IDX On TG_CDR04(SERV_ID) 　　Pctfree 5　　Tablespace TBS_AK01_IDX 　　Storage ( 　　MaxExtents 32768　　PctIncrease 0　　FreeLists 1　　FreeList Groups 1　　) 　　local　　/

如何創(chuàng)建范圍分區(qū)的全局索引

基礎表可以是全局表和分區(qū)表。

create index idx_start_date on tg_cdr01(start_date) 　　global partition by range(start_date) 　　(partition p01_idx vlaues less than (‘0106’) 　　partition p01_idx vlaues less than (‘0111’) 　　… 　　partition p01_idx vlaues less than (‘0401’ )) 　　/

　　重建現(xiàn)存的索引
　　重建現(xiàn)存的索引的當前時刻不會影響查詢；

　　重建索引可以刪除額外的數(shù)據(jù)塊；
　　提高索引查詢效率；

alter index idx_name rebuild nologging;

　　對于分區(qū)索引：

alter index idx_name rebuild partition partiton_name nologging;

要刪除索引的原因

• 不再需要的索引；
• 索引沒有針對其相關的表所發(fā)布的查詢提供所期望的性能改善；
• 應用沒有用該索引來查詢數(shù)據(jù)；
• 該索引無效，必須在重建之前刪除該索引；
• 該索引已經(jīng)變的太碎了，必須在重建之前刪除該索引；
• 語句：
drop index idx_name;
drop index idx_name drop partition partition_name;


建立索引的代價

基礎表維護時，系統(tǒng)要同時維護索引，不合理的索引將嚴重影響系統(tǒng)資源，主要表現(xiàn)在CPU和I/O上；

插入、更新、刪除數(shù)據(jù)產(chǎn)生大量db file sequential read鎖等待；

一個表中有幾百萬條數(shù)據(jù)，對某個字段加了索引，但是查詢時性能并沒有什么提高，這主要可能是oracle的索引限制造成的。

oracle的索引有一些索引限制，在這些索引限制發(fā)生的情況下，即使已經(jīng)加了索引，oracle還是會執(zhí)行一次全表掃描，查詢的性能不會比不加索引有所提高，反而可能由于數(shù)據(jù)庫維護索引的系統(tǒng)開銷造成性能更差。

擴展知識：常見的索引限制問題

1、使用不等于操作符(<>, !=)

下面這種情況，即使在列dept_id有一個索引，查詢語句仍然執(zhí)行一次全表掃描

select * from dept where staff_num <> 1000;

但是開發(fā)中的確需要這樣的查詢，難道沒有解決問題的辦法了嗎？

有！

通過把用 or 語法替代不等號進行查詢，就可以使用索引，以避免全表掃描：上面的語句改成下面這樣的，就可以使用索引了。

select * from dept shere staff_num < 1000 or dept_id > 1000;

2、使用 is null 或 is not null

使用 is null 或is nuo null也會限制索引的使用，因為數(shù)據(jù)庫并沒有定義null值。如果被索引的列中有很多null，就不會使用這個索引（除非索引是一個位圖索引，關于位圖索引，會在以后的blog文章里做詳細解釋）。在sql語句中使用null會造成很多麻煩。

解決這個問題的辦法就是：建表時把需要索引的列定義為非空(not null)

3、使用函數(shù)

如果沒有使用基于函數(shù)的索引，那么where子句中對存在索引的列使用函數(shù)時，會使優(yōu)化器忽略掉這些索引。下面的查詢就不會使用索引：

select * from staff where trunc(birthdate) = '01-MAY-82';

但是把函數(shù)應用在條件上，索引是可以生效的，把上面的語句改成下面的語句，就可以通過索引進行查找。

select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);

4、比較不匹配的數(shù)據(jù)類型

比較不匹配的數(shù)據(jù)類型也是難于發(fā)現(xiàn)的性能問題之一。下面的例子中，dept_id是一個varchar2型的字段，在這個字段上有索引，但是下面的語句會執(zhí)行全表掃描。

select * from dept where dept_id = 900198;

這是因為oracle會自動把where子句轉(zhuǎn)換成to_number(dept_id)=900198，就是3所說的情況，這樣就限制了索引的使用。把SQL語句改為如下形式就可以使用索引

select * from dept where dept_id = '900198';

5、使用like子句

使用like子句查詢時，數(shù)據(jù)需要把所有的記錄都遍歷來進行判斷，索引不能發(fā)揮作用，這種情況也要盡量避免。

Like 的字符串中第一個字符如果是‘%’則用不到索引

Column1 like ‘a(chǎn)aa%’ 是可以的 Column1 like ‘%aaa%’用不到

6、使用IN

盡管In寫法要比exists簡單一些，exists一般來說性能要比In要高的多

用In還是用Exists的時機

當in的集合比較小的時候,或者用Exists無法用到選擇性高的索引的時候，用In要好，否則就要用Exists
例：

select count(*) from person_info where xb in (select xb_id from dic_sex);  Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1 from person_info where pid=a.pid and …);  Select * from person_info where zjhm=3101….;--將會對person_info全表掃描  Select * from person_info where zjhm =‘3101…’;--才能用到索引

假定TEST表的dt字段是date類型的并且對dt建了索引。
如果要查‘20041010’一天的數(shù)據(jù).下面的方法用不到索引

Select * from test where to_char(dt,’yyyymmdd’) =‘20041010’;

而以下將會用到索引。

select * from test where dt >=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) + 1

7、如果能不用到排序，則盡量避免排序。

用到排序的情況有
集合操作。Union ,minus ,intersect等,注：union all 是不排序的。

Order byGroup byDistinctIn

有時候也會用到排序
確實要排序的時候也盡量要排序小數(shù)據(jù)量,盡量讓排序在內(nèi)存中執(zhí)行，有文章說，內(nèi)存排序的速度是硬盤排序的1萬倍。

在排序的字段上創(chuàng)建索引，讓排序在內(nèi)存中執(zhí)行，加快排序速度。

8、在基于CBO的優(yōu)化器（花費）下，表的統(tǒng)計數(shù)據(jù)過期。也可能導致不使用索引。

解決：執(zhí)行表分析。獲取表的最新信息。

9、獲取的數(shù)據(jù)量過大，全部掃描效率更高

10、索引字段的值分散率太低，值太集中，如類型字段都是1,2， 狀態(tài)類型Y-有效/N-無效。這類型的字段最好別建索引。

盡管在這些字段上建立了索引，但對全表數(shù)據(jù)區(qū)分度不大。最后還是會全表掃描。

推薦教程：《Oracle教程》