Oracle索引限制、實用準則及其建議<轉>

一個表中有幾百萬條資料，對某個欄位加了索引，但是查詢時效能並沒有什麼提高，這主要可能是Oracle索引限制造成的。
Oracle的索引有一些索引限制，在這些索引限制發生的情況下，即使已經加了索引，Oracle還是會執行一次全表掃描，查詢的效能不會比不加索引有所提高，反而可能由於資料庫維護索引的系統開銷造成效能更差。
一、下面是一些常見的Oracle索引限制問題。

1.使用不等於運算子(<>, !=)
下面這種情況，即使在列dept_id有一個索引，查詢語句仍然執行一次全表掃描 select * from dept where staff_num <> 1000; 但是開發中的確需要這樣的查詢，難道沒有解決問題的辦法了嗎？有！透過把用or語法替代不等號進行查詢，就可以使用索引，以避免全表掃描：上面的語句改成下面這樣的，就可以使用索引了。select * from dept shere staff_num < 1000 or dept_id > 1000;

2.使用 is null 或 is not null
使用 is null 或is nuo null也會限制索引的使用，因為資料庫並沒有定義null值。如果被索引的列中有很多null，就不會使用這個索引（除非索引是一個點陣圖索引，關於點陣圖索引，會在以後的blog文章裡做詳細解釋）。在sql語句中使用null會造成很多麻煩。解決這個問題的辦法就是：建表時把需要索引的列定義為非空(not null)

3.使用函式
如果沒有使用基於函式的索引，那麼where子句中對存在索引的列使用函式時，會使最佳化器忽略掉這些索引。下面的查詢就不會使用索引：select * from staff where trunc(birthdate) = '01-MAY-82'; 但是把函式應用在條件上，索引是可以生效的，把上面的語句改成下面的語句，就可以透過索引進行查詢。select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);

4.比較不匹配的資料型別
比較不匹配的資料型別也是難於發現的效能問題之一。
下面的例子中，dept_id是一個varchar2型的欄位，在這個欄位上有索引，但是下面的語句會執行全表掃描。select * from dept where dept_id = 900198; 這是因為Oracle會自動把where子句轉換成to_number(dept_id)=900198，就是3所說的情況，這樣就限制了索引的使用。把SQL語句改為如下形式就可以使用索引select * from dept where dept_id = '900198';
二、各種Oracle索引使用場合及建議
1.Oracle索引之B*Tree索引。
常規索引，多用於oltp系統，快速定位行，應建立於高cardinality列（即列的唯一值除以行數為一個很大的值，存在很少的相同值）。Create index indexname on tablename(columnname[columnname...])
2.Oracle索引之反向索引。
B*Tree的衍生產物，應用於特殊場合，在ops環境加序列增加的列上建立，不適合做區域掃描。
Create index indexname on tablename(columnname[columnname...]) reverse
3.Oracle索引之降序索引。
B*Tree的衍生產物，應用於有降序排列的搜尋語句中，索引中儲存了降序排列的索引碼，提供了快速的降序搜尋。
Create index indexname on tablename(columnname DESC[columnname...])
4.Oracle索引之點陣圖索引。
點陣圖方式管理的索引，適用於OLAP（線上分析）和DSS（決策處理）系統，應建立於低cardinality列，
適合集中讀取，不適合插入和修改，提供比B*Tree索引更節省的空間。
Create BITMAP index indexname on tablename(columnname[columnname...])
在實際應用中，如果某個欄位的值需要頻繁更新，那麼就不適合在它上面建立點陣圖索引。在點陣圖索引中，如果你更新或插入其中一條數值為N的記錄，那麼相應表中數值為N的記錄（可能成百上千條）全部被Oracle鎖定，這就意味著其它使用者不能同時更新這些數值為N的記錄，其它使用者必須要等第一個使用者提交後，才能獲得鎖，更新或插入資料，bitmap index它主要用於決策支援系統或靜態資料。
5.Oracle索引之函式索引。
B*Tree的衍生產物，應用於查詢語句條件列上包含函式的情況，
索引中儲存了經過函式計算的索引碼值。可以在不修改應用程式的基礎上能提高查詢效率。
6.Oracle索引建立策略
1）匯入資料後再建立索引
2）不需要為很小的表建立索引
3）對於取值範圍很小的欄位（比如性別欄位）應當建立點陣圖索引
4）限制表中的索引的數目
5）為索引設定合適的PCTFREE值
6）儲存索引的表空間最好單獨設定
唯一索引和不唯一索引都只是針對B樹索引而言. Oracle最多允許包含32個欄位的複合索引。由此估計出一個查詢如果使用某個索引會需要讀入的資料塊塊數。需要讀入的資料塊越多，則cost越大，Oracle也就越有可能不選擇使用index
三、Oracle索引實用原則
1.能用唯一索引，一定用唯一索引
2.能加非空，就加非空約束
3.一定要統計表的資訊，索引的資訊，柱狀圖的資訊。
4.聯合索引的順序不同，影響索引的選擇，儘量將值少的放在前面
只有做到以上四點，資料庫才會正確的選擇執行計劃。