ORACLE索引總結

 

一．B樹索引0.     B樹索引中不存在非唯一的條目。1)        在非唯一索引中，ORACLE會把rowid作為一個額外的列追加到鍵上，使得鍵唯一。Exp：create index I on T( x , y ) ,從概念上講就是Create unique index I on T(x , y , rowid)。ORACLE會首先按索引索引值排序，然後再按照rowid升序排序。2)        在唯一索引中，資料只按索引索引值排序。

1． 大多數情況下，B樹索引的高度都是2或者3，所以一般情況下，在索引中找到一個鍵只需要2或3次I/O。

2． B樹索引所有葉子塊都應該在同一層上，並且葉子節點實際上都是雙向鏈表，這樣在進行索引區間掃描(index range scan)的時候，只需通過葉子節點的向前或者向後就可以了，無需再對索引結構進行導航。

3． 適當對B樹索引存在重複值的列進行壓縮, 可以增加快取命中率，使I/O數減少，因為相關的條目可能都存在在一個塊中。（Exp:create index I on T(userid , username) username=’steven’這個值可能就會對應於多個rowid放在同一個索引塊中）；但是帶來的負面作用是使索引結構複雜化，維護索引更耗時，查詢索引佔用CPU更多的時間。（壓縮適合用於多列索引中）

4． B樹索引的反向鍵索引主要用於緩解索引右側緩衝區忙等待。適合用於類似於sequence產生的PK主鍵上，因為這些列不太會需要使用區間掃描，也就是不會用到max(PK),min(PK),between and或者where PK < 200等查詢

5． 如果在查詢中會有order by colum1 asc,column2 desc, 試著在建立索引時create index I on T(colum1 asc,column2 desc) , 因為ORACLE INDEX預設是DESC排序，在索引中排序總比在磁碟中排序好得多。

6． 適合B樹索引使用的2種情況：1)        訪問表中占很小比例的行2)        根本不訪問表，所需查詢的資料全部在索引中3)        一種特殊的用法，使用索引訪問表的全部行，這樣的做法是為了使查詢最初的回應時間很短，不是針對總輸送量進行的最佳化。

7． 索引是按索引鍵順序儲存，索引會按鍵的有序順序進行訪問。索引指向的塊則隨機儲存在堆中。

8． 建議：在thin表中使用B樹索引查詢<2-3%的列，fat表中<20-25%的列。 二．函數索引1. 函數索引的好處: Exp: function(column1) 如果表中有1000行，即便column1列上建有索引，索引在此時也不被使用，function會執行1000次。 2． 建立函數索引會使插入/更新效能稍有下降，但是查詢速度有了極大的提高。另外如果更新的時候沒有涉及到建立函數索引的這個列，那就不會產生額外的開銷。 3．   部分行建立索引：1)        某一列只有很少的基數，例如只有Y和N2)        通常只會查詢where column1 = ‘N’3)        並且值為N的行占很小的比例4)        在此列上建立索引，由於很大的一部分索引空間是浪費的(等於Y的)，不會使用到的，所以我們可以在值為’N’的那些行上面建立索引。5)        Create index I on T(decode(column1 , ‘N’, ‘N’))6)        利用的是B樹索引特性，如果列值為null,在索引中就沒有相應的條目。 4．    使用函數索引來保證複雜的約束：(某個條件成立時，X,Y,Z必須唯一)1)        如果項目表，項目有2種狀態，ACTIVE和INACTIVE2)        希望ACTIVE的項目必須有唯一的項目名，INACTIVE的項目沒有此要求3)        此時項目狀態這列上我們不適合建立唯一鍵4)        但是我們可以建立一個唯一索引5)        Create unique index I on T(decode(STATUS , ‘ACTIVE’ , NAME))6)        利用的還是B樹索引特性，如果列值為null,在索引中就沒有相應的條目。 三．索引的一些常見問題1．B樹索引特性，如果列值為null,在索引中就沒有相應的條目。1)        Create table T (x int , y int)2)        Create unique index I T(x , y)3)        Insert into T values(1 , 1)4)        Insert into T values(null , 1)5)        Insert into T values(1 , null)6)        Insert into T values(null , null)7)        此時索引中只有三行.(null , null)不在索引行中8)        Insert into T values(null , null) --成功插入 9)        Insert into T values(1 , null)    --報錯，違反index唯一性10)     所以我們可以看到ORACLE中null <> null (null代表N/A)11)     Where x is null   --這個查詢無法使用索引，因為（null,null）不在索引中，如果ORACLE使用索引就會得到錯誤的答案12)     同樣能夠得出結論，如果在一個允許null的列上面建立索引，x is null也不會使用索引13)     可以使用索引的條件Create table T (x int , y int not null); 2.         外鍵建立索引是需要的 3.         索引跳躍式掃描1)        Create index I T(x , y)2)        Select * from T where x=5; --此時最佳化器可能不會使用索引3)        Select x , y from T where x=5;      --可能使用索引，因為所需要的內容都在索引中4)        索引跳躍式掃描—skip scan5)        如果y只有2個基數，Y和N ,oracle會採用index 6)        Select * from T where x=5;   --會經過如下處理7)        Select * from T where x=5 and y=’Y’8)        Union all9)        Select * from T where x=5 and y=’N’; 4.         如果索引建立在一個允許null的列上面, select * from T就會使用全表掃描，不使用索引，因為null的行並不在索引行中，所以不會使用index統計數目。 5.         select * from T where x=5 等價於select * from T where to_number(x)=’5’,由於存在隱式轉換，所以x上的索引不會得到使用 6.         where trunc(date) = trunk(sysdate) 可以轉換成 date >=trunc(sysdate) and date<trunk(sysdate+1) 7.         定期分析表如果發現Oracle 在有索引的情況下，沒有使用索引，這並不是Oracle 的最佳化器出錯。在有些情況下，Oracle 確實會選擇全表掃描（Full Table Scan）,而非索引掃描（Index Scan）。這些情況通常可能是：
　　1. 表未做statistics, 或者 statistics 陳舊，導致 Oracle 判斷失誤。
　　　　最常見的例子，是以下這句sql 語句：
    select count(*) from T;
　　在未作statistics 之前，它使用全表掃描，需要讀取6000多個資料區塊（一個資料區塊是8k）, 做了statistics 之後，使用的是 INDEX (FAST FULL SCAN) ，只需要讀取450個資料區塊。但是，statistics 做得不好，也會導致Oracle 不使用索引。 8.         有些情況下單列索引不如複合索引有效率！甲、   有一種情況是顯而易見的，那就是，當sql 語句所查詢的列，全部都出現在複合索引中時，此時由於 Oracle 只需要查詢索引塊即可獲得所有資料，當然比使用多個單列索引要快得多。（此時，這種最佳化方式被稱為 Index only access path）。需要注意的是：where 子句中的這個欄位，必須是複合索引的第一個欄位乙、   還有一種情況就是where coid>=130000 and issuedate >= to_date ('2001-07-20', 'yyyy-mm-dd') 。此時複合索引I_mytabs_test ( coid, issuedate)的效果遠遠好於兩個單列索引：I_mytabs1(coid), I_mytabs2(issuedate)。