一、B-樹索引

索引一般是B-Tree索引，在索引結(jié)構(gòu)中存儲著鍵值和鍵值的RowID，并且是一一對應(yīng)的。

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

選項擇索引字段的原則:

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

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

  create index my_ix on my_table(my_col,0);

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

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

二、復(fù)合索引

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

CREATE INDEX name  ON employee (emp_lname, emp_fname)

復(fù)合索引的第一列稱為前導(dǎo)列(leading column)。

復(fù)合索引字段排序的原則:

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

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

在這種情況下，以下查詢可以得到優(yōu)化：

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

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

三、位圖索引

創(chuàng)建語法是在普通索引創(chuàng)建的語法中index前加關(guān)鍵字bitmap即可,例如:

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

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

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

位圖索引存儲數(shù)據(jù)的方式相對于B-Tree索引,占用的空間非常小,創(chuàng)建時不需要排序，定位存儲，創(chuàng)建和使用非?？?。

位圖索引的特點：

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

四、函數(shù)索引

比如執(zhí)行如下一條SQL語句：

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

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

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

這個時候，我們只需要按區(qū)間掃描小部分?jǐn)?shù)據(jù)，然后獲取rowid取訪問表中的數(shù)據(jù)，這個速度是比較快的。

函數(shù)索引的特點：

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

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

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

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

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

五、維護(hù)索引

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

到此這篇關(guān)于Oracle索引的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評論