7. Index 的優先等級

當一個 Table 有建立多個 Index 時， Oracle 優化器會依照下列原則來優先使用 Index :

• 當優化器為Rule-Based時優先使用where條件最前面的 Index :

例如一段 SQL 如下 :

SQL> select /*+ RULE */ * from employees  where manager_id = 100 and department_id = 60;

已知 employees 於 manager_id 上有Index EMP_MANAGER_IX ，於department_id 有Index EMP_DEPARTMENT_IX ，以這個查詢來說， EMP_MANAGER_IX 與 EMP_DEPARTMENT_IX 這兩個 Index 都可以使用，當優化器為 Rule-Based 時，優化器會使用 And-Equal 條件來同時引用兩個 Index ，但放置在 where 條件最前面的 Index 會最先被使用，因此這個查詢 EMP_MANAGER_IX 將優先被使用 :

若將 where 條件的順序交換，則 EMP_DEPARTMENT_IX 優先被使用 :

SQL> select /*+ RULE */ * from employees  where department_id = 60 and manager_id = 100;

• 當優化器為Cost-Based時使用選擇性最高的 Index :

以同樣的這段 SQL 來說 :

SQL> select * from employees  where manager_id = 100 and department_id = 60;

分析欄位的 distinct value ， manager_id 為 18 、 department_id 為 11 :

manager_id 的 num_distinct 大於 department_id ，所以 manager_id 的選擇性會比較好，所以這句 SQL 會使用 EMP_MANAGER_IX 這個 Index :

• Unique Index 將優先被使用 :

同樣以 employees 為例，查詢中使用了 employee_id 與 department_id 條件 :

SQL> select * from employees         where department_id = 60 and  employee_id = 101;

已知 employee_id 建立為 primary key (EMP_EMP_ID_PK) ，而EMP_DEPARTMENT_IX 為 Non-unique Index，則優化器會選擇 Primary Key 使用 :

這個道理也不難理解，由於 PK 、 Unique Index 為選擇性最佳的 Index ，所以必然優先被使用。

• 若欄位被重複定義建立在不同的 Index ，則複合索引優先被使用 :

例如欄位 LAST_NAME 被重複建立於單一索引 EMP_LNAME_IX 與複合索引EMP_NAME_IX 上 :

當使用了 LAST_NAME 作為條件，則複合索引優先被使用 :

SQL> select * from employees where last_name='David'

一般來說，複合索引的選擇性通常是優於單一欄位索引，因此複合索引在多數時候會優先被使用。

在了解 Index 的優先等級後，那麼我們在檢視執行計畫時，就可以先簡單的判斷執行計畫的合理性，這些需要優先被使用的 Index 是不是都有被使用到，如果某個 Index 該被使用而實際上優化器沒有選擇它，那接下來我們就要來探討是不是有些情況造成優化器無法選擇而造成 Index 失效的情況。

6. Index 的建立法則

Index 的建立必須要以資料的選擇性 (Selectivity) 為原則來建立，我們使用 Index 的目的，就是要能夠快速地過濾出資料， Index 是否有效率取決於它是否能夠過濾出最少的資料，其中最佳實踐就是 Primary Key 與 Unique Index ，因為這兩者都保證資料必須是唯一值，透過這兩者所過濾出來的資料筆數一定是 1 筆，這也是能過濾出來的最小值，因此 PK 與 Unique Index 是最有效率的 Index ，其餘的 Non-Unique Index 就必須要考慮到選擇性 (Selectivity) 的問題。

選擇性 (Selectivity) 是指所查詢的資料量占所有資料的比例，若我們檢索的資料幾乎接近全部的資料量，那使用全表掃描會比使用 Index 來的適合，若是檢索的資料量只占整體的一小部份，那麼使用 Index 來快速過濾資料就相對比較適合；我們以ㄧ個例子來做說明，首先列出 employees 這個 Table 當中每個department_id 於有多少筆資料 :

若我們檢索 department_id = 50 ，總共有 45 筆資料，占全部資料約 42% ，這時有可能會選擇以 Full Table Scan 來做查詢 :

若檢索 department_id = 100 ，只有 6 筆資料，占全部資料僅 5% ，此時就會以 Index 來做查詢 :

由上述的例子可知，若資料的選擇性不好 (Bad Selectivity，意即檢索的資料占絕大多數比例) ，即使有 Index 也不一定會使用它。一般來說，當檢索的資料占所有資料的比例小於 15% 的時候，比較適合建立 Index 。

在了解資料選擇性的概念之後，就可以開始對一段 SQL 來建立 Index ， SQL Statement 中 where 條件的欄位，就是我們用來建立 Index 的目標，因為where 條件與資料的選擇性息息相關，假設一段查詢如下:

SQL> select * from employees  where employee_id = 100 and department_id = 50;

這時 employee_id 與 department_id 這兩個欄位都是建立 Index 所考慮到的目標，由資料的選擇性來看，一個員工只會有一個員工 id 且不會重覆，所以 employee_id = 100 只會有一筆資料，比起 department_id = 50  有 45 筆資料來得適合，因此以這個查詢來說，我們應該對 employee_id 這個欄位建立 Index 。

如果將 employee_id 與 department_id 建立一個複合索引 (Composite Index) ，是否效能會更好 ? 以這個例子來說，建立單欄位索引的選擇性與建立複合索引的選擇性是相同 (employee_id = 100  與 employee_id = 100 and department_id = 50 兩個條件都只會檢索到一筆資料) ，而複合索引所需要的儲存空間與 I/O 也較多，因此複合索引在此是不適合的。

是否需要建立複合索引 (Composite Index) 也是以資料的選擇性為原則來做考量，例如一段查詢如下 :

SQL> select * from employees        where first_name = 'William' and last_name = 'Smith';

此時須考量到 employees 可能有多個員工的 first_name 為 William ，同樣也有可能有多個 last_name 為 Smith ，但只會有一位員工叫做 'Smith William' ，就算是同名同姓，這樣子的筆數也是相對少，因此對這個查詢來說，建立一個含有 (last_name , first_name) 的 Composite Index 選擇性較高，比建立單個欄位的Index來得適合。不過對於複合索引來說，還需要考慮到欄位的順序，最常被使用的欄位應該放在複合索引的最前面，因為優化器會依複合索引中欄位的順序來比對 where 條件是否有符合的欄位可做檢索，若複合索引的第一個欄位不存在 where 條件中，即使 where 條件含有複合索引中第二或第三個欄位，優化器也不會選擇使用複合索引，或是使用較差的 Index Skip Scan 來做查詢。

若 SQL 語句有 join 的情況下，同樣的需考慮哪個 Table 的選擇性較好，例如 :

SQL> select last_name||' '||first_name,department_name         from employees a,departments b where a.department_id=b.department_id;

此時共有兩個欄位 a.department_id 與 b.department_id 可供選擇來作為Index ，以這個例子來說，同樣是名叫 department_id 的欄位，對於存放部門資訊的 departments 這個 Table 來說， 部門名稱是不會重複的，所以它的 department_id 是唯一值；而存放員工資訊的 employees 這個 Table ， department_id 是用來紀錄每個員工的所在部門，不同的 employees_id 可能會對應到相同的 department_id ，綜合來說，使用 departments 的 department_id 會比 employees 的 department_id 能夠過濾出來較少的資料，所以將 Index 建立在 departments 的 department_id 上比較適合。

另一種可以建立複合索引的情況，就是將查詢的欄位也包括在 Index 裡面，這個做法是為了減少回表所消耗的時間，例如 :

SQL> select object_name from objects where object_id = 100;

以這個查詢來說，建立複合索引 (object_id , object_name) 會比只建立單一欄位 (object_id) 的索引來得適合，因為複合索引(object_id , object_name) 已經把將要查詢的字段 object_name 包含進去，無需再由ROWID 回去反查 Table 中 object_name 的欄位。

既然資料的選擇性 (Selectivity) 這麼重要，那麼實務上要如何判斷欄位的選擇性好不好 ? 透過 dba_tab_columns 當中的 num_distinct 欄位可以快速的判斷哪一個欄位的選擇性較好， num_distinct 代表這個欄位的 distinct value ，數值越大代表數據的種類越多，如果 num_distinct 的數值等於整個 Table 的全部筆數，那麼表示這個欄位的資料都是唯一值，所以它的 distinct value 等於總筆數，換句話說，只要是 num_distinct 越大，代表這個 where 條件越能夠過濾出越少資料，也就越適合建立 Index ，使用 num_distinct 的概念，我們再來判斷上述這句查詢的哪個欄位適合建立 Index :

SQL> select last_name||' '||first_name,department_name         from employees a,departments b where a.department_id=b.department_id;

查詢 dba_tab_columns 得到這兩個 Table 欄位的 distinct value :

employees - department_id 的 distinct value 為 11

departments – department_id 的 distinct value 為 27

所以 departments 的 department_id 比較適合用來建立 Index 。

只要掌握了選擇性 (Selectivity) 的概念與 distinct value 的用法，就可以建立有效率的 Index ，避免無端、過多 Index 的情況。

5. Index 的掃描方式

Index 的掃描資料方式可分為 Index Unique Scan 、 Index Range Scan與 Index Fast Full Scan ，其中 Index Fast Full Scan 是以 multi block 進行掃描，其餘是以 single block 一次只讀取一個 Block 進行掃描。針對這三種掃描方式，我們建立一個測試 table 來做說明 :

SQL> create table objects as select * from dba_objects;

於 object_id 欄位建立一個 Unique Index :

SQL> create unique index IX_OBJECT_ID on objects(object_id);

於 object_name 欄位建立一個 Non-Unique Index :

SQL> create unique index IX_OBJECT_NAME on objects(object_name);

當我們對具有 Unique Index 的欄位進行 "=" 條件檢索時，此時會以 Index Unique Scan 進行掃描 :

對具有 Non-Unique Index 的欄位進行 "=" 條件檢索時，則是以 Index Range Scan 進行掃描 :

當進行範圍檢索時，不論此欄位具有 Unique Index 或是 Non-Unique Index 時，都會使用 Index Range Scan :

若是所要查詢的列都包含在 Index 裡面的話，便可直接從 Index 返回資料，無需再由 ROWID 回去反查 Table 的列，這種情況就會發生 Index Fast Full Scan ，但前提是此欄位必須為 Not Null ，否則會變成 Full Table Scan 。例如只針對 object_id 這個具有 Index 的欄位做 select ，當 object_id 存在 Null 值時會發生 Full Table Scan :

刪除 Null 的資料並將欄位屬性設定為 Not Null 之後，同樣的查詢就會變成 Index Fast Full Scan :

在 Oracle 10.2.0.5 版本開始，多了一個 Index Skip Scan 的功能，以往複合欄位的 Index 必須 where 條件有使用到第一個欄位才會用到此 Index ，而 Index Skip Scan 則是當 where 條件沒使用到複合索引的第一個欄位時所使用的 Index 掃描方式，例如 employees 這個 Table 有複合索引 EMP_NAME_IX (LAST_NAME,FIRST_NAME) :

此時查詢的 where 條件沒使用到第一個欄位 (LAST_NAME) ，就會使用 Index Skip Scan 進行掃描 :

SQL> select last_name,first_name from employees         where first_name='David';

而 Index Skip Scan 的原理是 Recursive SQL 會將原本的查詢改寫為具有第一個欄位 (LAST_NAME) 的查詢，並將所有 LAST_NAME 的數值 Union 起來，透過 Recursive SQL ，原本的查詢就會變成 :

SQL> select last_name,first_name from employees          where last_name='Austin' and first_name='David' UNION  select last_name,first_name from employees   where last_name='Ben' and first_name='David' UNION select last_name,first_name from employees         where last_name='Lee' and first_name='David';

由於 Recursive SQL 使用了第一個欄位 (LAST_NAME) ，所以這個複合索引也就可以使用到了，但是這個方式的缺點也是顯而易見的，假設 LAST_NAME 的筆數很多的話， Recursive SQL 這樣子處理是相對沒有效率的，因此在大部分時候 Index Skip Scan 是很沒有效率的，表面上雖然是使用到 Index ，但這個掃描方式不見得會比較快。

使用 Index 最主要的目的就是能過快速的找到資料，因此能夠過濾出越少資料的掃描方式是最好的，首選當然就是 Index Unique Scan ，這也是為什麼建議 Table 上都要有 Primary Key ，因為 PK 是唯一值且 Not Null ，一定是使用 Index Unique Scan ，過濾出來的資料是最少所以是最有效率的，其次大多時候我們看到的都是 Index Range Scan ，此時 Index 掃描有沒有效率，就與資料的選擇性 (selectivity) 有關。

4. Index 介紹

在進行 SQL Tuning 時，建立 Index 可以加快查詢的速度，這是眾所皆知的，但為何 Index 可以加快查詢的速度，原因在於 Index 儲存了 Rowid 這項重要的資訊， Rowid 表示資料存放的實體物理位址，知道資料的實體物理位址後，搜尋起來自然就比較快速。例如一個查詢使用了 where id = 20 這個條件 :

假如此 Table 的 ID 欄位有建立 Index ，那麼首先會去此 Index 找到 id = 20 這筆資料，然後同時獲得 Rowid ，然後再藉由 Rowid 回去 Table 找出剩下欄位的資料，這個動作俗稱回表，透過 Index 的 Rowid 資訊，很快地就能夠獲得所要查詢的這筆資料。

Oracle Index 的架構可分為 B-Tree Index 與 Bitmap Index ，不論是 B-Tree 或者是 Bitmap Index 本質上都是一個樹狀結構，差別在於 Leaf Block 記錄的內容不同而已。

B-Tree Index 的 Leaf 記錄 Index Header 、 Key Length 、 Key Value與Row id四個值。

Index Header

Key Length

Key Value

RowID

例如一個查詢的 where 條件為  department_id = 20 ，便會從 B-Tree Index 中找出 Value = 20 的 Leaf Block 與 RowID ，然後再從 RowID 回去反查 Table 其他欄位的值。當欄位資料的重複性較小時，適合建立 B-Tree Index 。

Bitmap 索引的 Leaf 是以二進位的方式 (0與1) 來表示每筆資料在 block上的相對位置，例如以 region 這個 table 來說， region_name 有 East 、 West 與 Center 三種 Value ，所以 Bitmap 索引會建立三個 Leaf 如下 :

假設 Region_Name 的第一筆資料為 East ，則 Bitmap Index 便會在 East這個 Leaf 上將其標示為 1 ，而另外兩個 Leaf 則標示為 0 ；第二筆資料為 West ，所以會在 West 這個 Leaf 上標示為 1 ，另外兩個 Leaf 則標示為 0 … 以此類推。當我們一個查詢的 where 條件為 Region_Name='East' 的時候，便會從 Bitmap Index 返回 East 這個 Leaf 所有等於 1 的資料列。當欄位資料的重複性較大時，較適合 Bitmap Index 。

索引的種類大致上可分為 Unique Index 、 Non-Unique Index 、Composite Index 與 Fuction Index 。 Unique Index 要求欄位的值必須不可重複，可以使用 create unique index 的語法建立， 或者是建立 primary key 的同時，系統也會自動建立一個 Unique Index ； Non-Unique Index 即一般使用 create index 這個語法所建立的 Index ，對於欄位的值並無特殊要求； Composite Index 指的是一個索引包含兩個或兩個以上的欄位所組成，例如我們可以建立一個 Composite Index 如下 :

SQL> create Index IX_ID_NAME on regions(region_id,region_name);

Function Index 表示對使用函數轉換過的欄位建立 Index ，但此函數必須限定系統內建的函數才行，無法使用自行定義的 Function 來建立 Index 。例如我們可以建立一個 Function Index 如下 :

SQL> create index IX_FNAME on employees(upper(first_name));

Index 的使用對於 SQL 的效能有直接的影響，一般都會在表格上選擇重要的欄位建立 Index 以提高數據查詢的效率，雖然使用 Index 可以提高查詢的效率，但是我們也必須了解使用 Index 需付出的代價。其一， Index 需要空間來儲存；其二，當 Table 中的資料被修改時， Index 本身相對的資料也會做修改，因此當 Table 進行 DML 操作時，會產生額外的 I/O 來修改 Index ，Table 建立的 Index 越多，對I/O效能的影響也越大；其三，若 Table 建立的 Index 過多，在 Cost-Based 的計算下有可能誤判而選擇了一個校能較差的 Index 。 

Index 並不是 SQL 效能的萬靈丹，過多或是不必要的 Index 反而會導致查詢的反應時間變慢，我們可以透過 alter index <index_name> monitoring usage 的指令來監控某個 Index 是否有被使用，例如 :

SQL> alter index hr.ix_region1 monitoring usage; (啟用監控) SQL> alter index hr.ix_region1 nomonitoring usage; (取消監控)

使用 Index 的 Schema Owner 可以透過 v$object_usage 來查詢 Index 監控的結果 :

SQL> select * from v$object_usage;

若是使用 sys 則使用下列語法查詢 :

SQL> select u.name owner, io.name index_name, t.name table_name,        decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,        decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,        ou.start_monitoring start_monitoring,        ou.end_monitoring end_monitoring   from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou    where i.obj# = ou.obj#      and io.obj# = ou.obj#      and t.obj# = i.bo#      and u.user# = io.owner#;

在 Oracle 12cR2 的版本之後多了 Index Usage Tracking 這個新功能，預設是啟用的 ( "_iut_stat_collection_type" 這個參數預設為 SAMPLED ) ，系統會自動監控 Index 的使用，直接查詢 DBA_INDEX_USAGE 就可以檢視 Index 是否有被使用 :

Index 並不是建的越多越好，需要搭配 SQL 語句來建立會比較有效率。