簡介

1.說明

　　1）索引是資料庫物件之一，用于加快資料的檢索，類似于書籍的索引，在資料庫中索引可以減少資料庫程式查詢結果時需要讀取的資料量，類似于在書籍中我們利用索引可以不用翻閱整本書即可找到想要的資訊，

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

　　3）索引在邏輯上和物理上都與相關的表和資料無關，當創建或者洗掉一個索引時，不會影響基本的表；

　　4）索引一旦建立，在表上進行DML操作時（例如在執行插入、修改或者洗掉相關操作時），oracle會自動管理索引，索引洗掉，不會對表產生影響

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

　　6）oracle創建主鍵時會自動在該列上創建索引

索引原理

1. 若沒有索引，搜索某個記錄時（例如查找name='wish'）需要搜索所有的記錄，因為不能保證只有一個wish，必須全部搜索一遍

2. 若在name上建立索引，oracle會對全表進行一次搜索，將每條記錄的name值哪找升序排列，然后構建索引條目（name和rowid），存盤到索引段中，查詢name為wish時即可直接查找對應地方

3.創建了索引并不一定就會使用，oracle自動統計表的資訊后，決定是否使用索引，表中資料很少時使用全表掃描速度已經很快，沒有必要使用索引

索引使用（創建、修改、洗掉、查看）

1.創建索引語法

CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap，創建位圖索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]                                 --指定索引在資料塊中空閑空間
[STORAGE (INITIAL n2)]
[NOLOGGING]                                  --表示創建和重建索引時允許對表做DML操作，默認情況下不應該使用
[NOLINE]
[NOSORT];                                    --表示創建索引時不進行排序，默認不適用，如果資料已經是按照該索引順序排列的可以使用

2.修改索引

1）重命名索引

alter index index_sno rename to bitmap_index;

2) 合并索引（表使用一段時間后在索引中會產生碎片，此時索引效率會降低，可以選擇重建索引或者合并索引,合并索引方式更好些，無需額外存盤空間，代價較低）

alter index index_sno coalesce;

3)重建索引

　　方式一：洗掉原來的索引，重新建立索引

　　方式二：

alter index index_sno rebuild;

3.洗掉索引

drop index index_sno;

4.查看索引

select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';

 -- eg:    
create index index_sno on student('name');
select * from all_indexes where table_name='student';

索引分類

1. B樹索引（默認索引，保存講過排序過的索引列和對應的rowid值）

1）說明：

　　1.oracle中最常用的索引；B樹索引就是一顆二叉樹；葉子節點（雙向鏈表）包含索引列和指向表中每個匹配行的ROWID值

　　2.所有葉子節點具有相同的深度，因而不管查詢條件怎樣，查詢速度基本相同

　　3.能夠適應精確查詢、模糊查詢和比較查詢

2）分類：

　　 UNIQUE,NON-UNIQUE(默認),REVERSE KEY（資料列中的資料是反向存盤的）

3）創建例子

craete index index_sno on student('sno');

4）適合使用場景：

　　列基數（列不重復值的個數）大時適合使用B數索引

2. 位圖索引

1）說明：

　　1.創建位圖索引時，oracle會掃描整張表，并為索引列的每個取值建立一個位圖（位圖中，對表中每一行使用一位（bit，0或者1）來標識該行是否包含該位圖的索引列的取值，如果為1，表示對應的rowid所在的記錄包含該位圖索引列值），最后通過位圖索引中的映射函式完成位到行的ROWID的轉換

2)創建例子

create bitmap index index_sno on student(sno);

3) 適合場景：

對于基數小的列適合簡歷位圖索引（例如性別等）

3.單列索引和復合索引（基于多個列創建）

1) 注意：

　　即如果索引建立在多個列上，只有它的第一個列被where子句參考時，優化器才會使用該索引，即至少要包含組合索引的第一列，

在大多數情況下、復合索引比單欄位索引好，很多系統就是靠新建一些合適的復合索引、使效率大幅度提高
復合索引比單欄位索引復雜、有兩個原則需把握：前綴性和可選性，

2）前綴性、可選性

㈠ 前綴性（Prefixing）

在謂詞條件中、只有將復合索引的第一個欄位作為約束條件、該復合索引才會被用上
當然、存在一種例外：Index Skip Scan

這個原理強調使用

比如：

① 假如c1的選擇性比c2高很多、且如果有大量如下查詢：
select * from t where c1= :x and c2= :y;
select * from t where c2= :y;
這個時候(c2,c1)比(c1,c2)好

② 有ID和CREATE_TIME這兩個欄位、一個是主鍵、一個是創建時間
按一般情況、創建復合索引時、需要將ID放在前面、
不過、如果以CREATE_TIME為查詢條件的SQL比較多、而ID的使用比較少
那么、我們在設計時、應該把CREATE_TIME前綴

㈡ 可選性（Selectivity）

概念可見

Selectivity介紹

這個原理強調成本

比如：

① 選擇性強的欄位放在前面、可以減少 Index Range Scan 的掃描成本
② 有個稅務系統、原先的復合索引是(月份，稅務機關代號，納稅人識別號，發票代號，收費類別)
對于這個索引、優化器并沒有選擇走索引、而是全表掃、原因如下：
● 同一月份的記錄太多了、超過1/3、全表掃成本低
● 欄位順序沒有考慮可選性
新的復合索引是(納稅人識別號，月份，收費類別，稅務機關代號，發票代號)


㈢ 復合索引好處

① 盡可能讓一個索引為更多的SQL服務
② 復合索引是排序的、ORDER BY涉及索引欄位時、可減少排序成本


我認為呢、在為復合索引欄位排序時、應該綜合考慮權衡前綴性和可選性、絕不可偏袒任何一方

4. 函式索引

1)說明：

　　1. 當經常要訪問一些函式或者運算式時，可以將其存盤在索引中，這樣下次訪問時，該值已經計算出來了，可以加快查詢速度

　　2. 函式索引既可以使用B數索引，也可以使用位圖索引；當函式結果不確定時采用B樹索引，結果是固定的某幾個值時使用位圖索引

　　3. 函式索引中可以水泥用len、trim、substr、upper（每行回傳獨立結果），不能使用如sum、max、min、avg等

2）例子：

create index fbi  on student (upper(name));
select * from student where upper(name) ='WISH';

索引建立原則總結

　　1. 如果有兩個或者以上的索引，其中有一個唯一性索引，而其他是非唯一，這種情況下oracle將使用唯一性索引而完全忽略非唯一性索引

　　2. 至少要包含組合索引的第一列（即如果索引建立在多個列上，只有它的第一個列被where子句參考時，優化器才會使用該索引）

　　3. 小表不要簡歷索引

　　4. 對于基數大的列適合建立B樹索引，對于基數小的列適合簡歷位圖索引

　　5. 列中有很多空值，但經常查詢該列上非空記錄時應該建立索引

　　6. 經常進行連接查詢的列應該創建索引

　　7. 使用create index時要將最常查詢的列放在最前面

　　8. LONG（可變長字串資料，最長2G）和LONG RAW（可變長二進制資料，最長2G）列不能創建索引

　　9.限制表中索引的數量（創建索引耗費時間，并且隨資料量的增大而增大；索引會占用物理空間；當對表中的資料進行增加、洗掉和修改的時候，索引也要動態的維護，降低了資料的維護速度）

注意事項

1. 通配符在搜索詞首出現時，oracle不能使用索引，eg：

--我們在name上創建索引；

create index index_name on student('name');

--下面的方式oracle不適用name索引

select * from student where name like '%wish%';

--如果通配符出現在字串的其他位置時，優化器能夠利用索引；如下：

select * from student where name like 'wish%';

2. 不要在索引列上使用not，可以采用其他方式代替如下：（oracle碰到not會停止使用索引，而采用全表掃描）

select * from student where not (score=100);

select * from student where score <> 100;

--替換為

select * from student where score>100 or score <100

3. 索引上使用空值比較將停止使用索引， eg：

select * from student where score is not null;