Oracle索引(B*tree和Bitmap)學習

標籤：blog   http   ar   使用   sp   strong   on   資料   art   

在Oracle中，索引基本分為以下幾種：B*Tree索引，反向索引，降序索引，位元影像索引，函數索引，interMedia全文索引等，其中最常用的是B*Tree索引和Bitmap索引。

(1)、與索引相關視圖

查詢DBA_INDEXES視圖可得到表中所有索引的列表；訪問USER_IND_COLUMNS視圖可得到一個給定表中被索引的特定列。

(2)、複合式索引概念

當某個索引包含有多個已索引的列時，稱這個索引為組合（concatented）索引。

注意：只有在使用到索引的前置索引時才可以使用複合式索引

(3)、B*Tree索引

B*Tree索引是最常見的索引結構，預設建立的索引就是這種類型的索引。B*Tree索引在檢索高基數資料列（高基數資料列是指該列有很多不同的值）時提供了最好的效能。

DML語句：

Create index indexname on tablename(columnname[columnname...])

B-tree特性：

適合與大量的增、刪、改（OLTP）；

不能用包含OR操作符的查詢；

適合高基數的列（唯一值多）；

典型的樹狀結構；

每個結點都是資料區塊；

大多都是物理上一層、兩層或三層不定，邏輯上三層；

葉子塊資料是排序的，從左向右遞增；

在分支塊和根塊中放的是索引的範圍。

(4)、Bitmap索引

位元影像索引主要用於決策支援系統或待用資料，不支援行級鎖定。位元影像索引最好用於低cardinality列（即列的唯一值除以行數為一個很小的值，接近零）。

DML語句：

Create BITMAP index indexname on tablename(columnname[columnname...])

Bitmap特性:

適合與決策支援系統；

做UPDATE代價非常高；

非常適合OR操作符的查詢；

基數比較少的時候才能建位元影像索引。

(5)、B*tree和Bitmap的不同

在一顆 B* 樹中，通常索引條目和行之間存在一種一對一的關係：一個索引條目就指向一行；而對於位元影像索引，一個索引條目則使用一個位元影像同時指向多行。

位元影像索引適用於高度重複而且通常唯讀資料（高度重複是指相對於表中的總行數，資料只有很少的幾個不同值）。B*tree索引的話通常在訪問小資料量的情況下比較適用，比如你訪問不超過表中資料的5%，適用於一般的情況；bitmap的話在資料倉儲中使用較多，用於低基數列，比如性別之類重複值很多的欄位，基數越小越好。

(6)、導致索引失效的情況

u  使用不等於操作符（<>、!=）

通常把不等於操作符改成OR條件，就可以使用索引，以避免全表掃描

u  使用IS NULL 或IS NOT NULL

使用IS NULL 或IS NOT NULL同樣會限制索引的使用。因為NULL值並沒有被定義。在SQL語句中使用NULL會有很多的麻煩。因此建議開發人員在建表時，把需要索引的列設成NOT NULL。如果被索引的列在某些行中存在NULL值，就不會使用這個索引（除非索引是一個位元影像索引）。

u  使用函數

如果不使用基於函數的索引，那麼在SQL語句的WHERE子句中對存在索引的列使用函數時，會使最佳化器忽略掉這些索引。

u  比較不匹配的資料類型

不匹配的資料類型之間比較會讓Oracle自動限制索引的使用，即便對這個查詢執行Explain Plan也不能讓您明白為什麼做了一次”全表掃描”。

u  複合索引中的前置列沒有被作為查詢條件

複合索引中，一定要將前置列作為查詢條件，索引才會被使用

u  CBO模式下選擇的行數比例過大，最佳化器採取了全表掃描

這是基於代價的最佳化考慮

 

文章轉自：http://blog.csdn.net/nsj820/article/details/5878995

Oracle索引(B*tree和Bitmap)學習