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 語句來建立會比較有效率。