一、B-樹索引

索引一般是B-Tree索引，在索引結構中存儲著鍵值和鍵值的RowID，并且是一一對應的。

create [unique] index studentin student(id [desc]);

選項擇索引字段的原則:

（ORACLE在UNIQUE和主鍵字段上自動建立索引 。）

• 在WHERE子句中最頻繁使用的字段 。
• 聯接語句中的連接字段。
• 選擇高選擇性的字段(即如果很少的字段擁有相同值,即有很多獨特值,可以快速查找到所需數據的字段) .
• 在聯機事務處理(OLTP)環境下,所由并發性非常高,索引經常被修改,可以建B-TREE索引，不應該建位圖索引 。
• 不要在經常被修改的字段上建索引，可建函數索引。
• 不要在有用到函數的字段上建索引。
• B-Tree索引不包含null的數據。 
  可以建立一個“偽”復合索引解決。eg:

  create index my_ix on my_table(my_col,0);

  也可以用函數索引將不想索引，即使不是null也可以剔除。eg:

  create index a on table(decode(status,0,0))--只關心少數status為0的行。

二、復合索引

索引可以包含一個、兩個或更多個列。兩個或更多個列上的索引被稱作復合索引。例如，以下語句創建一個具有兩列的復合索引：(復合索引列數量不超過32個)

CREATE INDEX name  ON employee (emp_lname, emp_fname)

復合索引的第一列稱為前導列(leading column)。

復合索引字段排序的原則:

• WHERE子句中使用到的字段需要是復合索引的前導字段，若僅對后面的任意列執行搜索時，則應該創建另一個僅包含第二列的索引。
• 如果某個字段在WHERE子句中最頻繁使用,則在建立復合索引時,考慮把這個字段排在第一位(在CREATE INDEX語句中)
• 如果所有的字段在WHERE子句中使用頻率相同,則將低選擇性列排在最前面,將選擇性較強的列排在最后面
• 如果所有的字段在WHERE子句中使用頻率相同,如果數據在物理上是按某一個字段排序的,則考慮將這個字段放在復合索引的第一位 。
• 在主鍵索引(復合主鍵)中列的順序被強制為與列在表定義中出現的順序相同，這與 PRIMARY KEY 約束中指定的列順序無關.
• 索引列的排序方式必須與 ORDER BY 子句完全相同或完全相反。否則不能得到性能優化。

CREATE INDEX idx_example ON table1 (col1 ASC, col2 DESC, col3 ASC)

在這種情況下，以下查詢可以得到優化：

SELECT col1, col2, col3 from table1 ORDER BY col1 ASC, col2 DESC, col3 ASC

和

SELECT col1, col2, col3 from example ORDER BY col1 DESC, col2 ASC, col3 DESC

• 復合索引的前導字段is (not) null 可以使用索引。

三、位圖索引

創建語法是在普通索引創建的語法中index前加關鍵字bitmap即可,例如:

create bitmap index t_ix_執行人 on t(執行人);

位圖索引主要針對大量相同值的列而創建(例如：類別，操作員，部門ID,庫房ID等)。

索引塊的一個索引行中存儲鍵值和起止Rowid,以及這些鍵值的位置編碼。

位圖索引存儲數據的方式相對于B-Tree索引,占用的空間非常小,創建時不需要排序，定位存儲，創建和使用非常快。

位圖索引的特點：

• Bitmap索引允許鍵值為空，對位圖索引列進行is（not） null查詢時,則可以使用索引。
• Bitmap索引對表記錄的高效訪問。當使用count(XX),可以直接訪問索引就快速得出統計數據。當根據位圖索引的列進行and,or或 in(x,y,..)查詢時,直接用索引的位圖進行或運算。
• Bitmap索引對批量DML操作只需進行一次索引。
• 位圖索引由于用位圖反映數據,不同會話更新相同鍵值的同一位圖段,insert、update、delete相互操作都會發鎖定（未提交時）。
• 由于并發DML操作鎖定的是整個位圖段的大量數據行，所以位圖索引主要是用于聯機分析處理(OLAP)應用。

四、函數索引

比如執行如下一條SQL語句：

select * from emp where upper(ename) = "KING"，

即使在ename上建立了索引，還是會全表掃描emp表，將里面的ename字段改成大寫跟常量KING進行比較。 
如果我們建立一個基于函數的索引，比如：

create index emp_upper_idx on emp(upper(ename));

這個時候，我們只需要按區間掃描小部分數據，然后獲取rowid取訪問表中的數據，這個速度是比較快的。

函數索引的特點：

• 基于函數的索引，類似于普通的索引，只是普通的索引是建立在列上，而它是建立在函數上。當然這回對插入數據有一定影響，因為需要通過函數計算一下，然后生成索引。但是插入數據一般都是少量插入，而查詢數據一般數據量比較大。為了優化查詢速度，稍微降低點插入速度是可以承擔的。
• 函數索引還有一個功能，只對部分行建立索引。 
  假設有一個很大的表，有一列叫做FLAG，只可能取Y和N。假設大部分數據是Y，小部分數據是N，我們需要將N修改成Y。

如果建立一個普通索引，這個索引會非常大，而且將N修改成Y的時候，維護這個索引開銷會很大。

如果建立一個位圖索引，但這是一個事務系統（OLTP），可能有很多人同時插入記錄，或者進行修改。那么位圖索引也不適合。所以，如果我們只是在值為N的行上建立索引，就比較好辦了。 
只在值為N的行建立索引：

create index flag_index on big_table(case flag when "N" then "N" end);

這樣一個索引大小會大大降低，而且維護成本也會很低的。前提是我們只對值為N的行感興趣。

五、維護索引

• 字典表：all_indexes、user_indexes
• 重建索引：alter index dep_idx rebuild [online]
• 刪除索引：drop index dep_idx;

到此這篇關于Oracle索引的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持。