Oracle的Index－2（轉）

Oracle的Index－2[@more@]二講，索引也有好壞 索引有 B tree 索引， Bitmap 索引， Reverse b tree 索引， 等。最常用的是 B tree 索引。 B 的全稱是Balanced , 其意義是，從 tree 的 root 到任何一個leaf ，要經過同樣多的 level. 索引可以只有一個欄位（Single column）, 也可以有多個欄位（Composite）,最多32個欄位，8I 還支援 Function-based index. 許多developer 都傾向於使用單列B 樹索引。 所謂索引的好壞是指： 1，索引不是越多越好。特別是大量從來或者幾乎不用的索引，對系統只有損害。OLTP系統每表超過5個索引即會降低效能，而且在一個sql 中， Oracle 從不能使用超過 5個索引。 2，很多時候，單列索引不如複合索引有效率。 3，用於多表連結的欄位，加上索引會很有作用。 那麼，在什麼情況下單列索引不如複合索引有效率呢？有一種情況是顯而易見的，那就是，當sql 語句所查詢的列，全部都出現在複合索引中時，此時由於 Oracle 只需要查詢索引塊即可獲得所有資料，當然比使用多個單列索引要快得多。（此時，這種最佳化方式被稱為 Index only access path） 除此之外呢？我們還是來看一個例子吧： 在 HP（Oracle 8.1.7） 上執行以下語句： select count(1) from mytabs where coid>=130000 and issuedate >= to_date ('2001-07-20', 'yyyy-mm-dd')。 一開始，我們有兩個單列索引：I_mytabs1(coid), I_mytabs2(issuedate), 下面是執行情況： COUNT(1) ---------- 6427 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=384 Card=1 Bytes=11) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_MYTABS' (Cost=384 Card =126 Bytes=1386) 3 2 INDEX (RANGE SCAN) OF 'I_MYTABS2' (NON-UNIQUE) (Cost=11 Card=126) Statistics ---------------------------------------------------------- 172 recursive calls 1 db block gets 5054 consistent gets 2206 physical reads 0 redo size 293 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 1 rows processed 可以看到，它讀取了7000個資料塊來獲得所查詢的 6000多行。 現在，去掉這兩個單列索引，增加一個複合索引I_mytabs_test ( coid, issuedate), 重新執行，結果如下： COUNT(1) ---------- 6436 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=11) 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF 'I_MYTABS_TEST' (NON-UNIQUE) (Cost=3 Card=126 Bytes=1386) Statistics ---------------------------------------------------------- 806 recursive calls 5 db block gets 283 consistent gets 76 physical reads 0 redo size 293 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1 rows processed 可以看到，這次只讀取了300個資料塊。 7000塊對300塊，這就是在這個例子中，單列索引與複合索引的代價之比。這個例子提示我們， 在許多情況下，單列索引不如複合索引有效率。 可以說，在索引的設定問題上，其實有許多工作可以做。正確地設定索引，需要對應用進行總體的分析。