1、Oracle索引原理索引的概念1、 類似書的目錄結構2、 Oracle 的“索引”對象，與表關聯的可選對象，提高SQL查詢語句的速度3、 索引直接指向包含所查詢值的行的位置，減少磁盤I/O4、 與所索引的表是相互獨立的物理結構5、 Oracle 自動使用并維護索引，插入、刪除、更新表后，自動更新索引6、 語法：CREATE INDEX index ON table (column, column.);索引分類邏輯分類single column or concatenated   對一列或多列建索引unique or 

2、;nonunique  唯一的和非唯一的索引，也就是對某一列或幾列的鍵值（key）是否是唯一的。Function-based  基于某些函數索引，當執行某些函數時需要對其進行計算，可以將某些函數的計算結果事先保存并加以索引，提高效率。 Doman  索引數據庫以外的數據，使用相對較少 物理分類B-Tree ：normal or reverse key   B-Tree索引也是我們傳統上常見所理解的索引，它又可以分為正常所引和倒序索引。Bitmap 

3、; ： 位圖所引B-TREE索引一個B樹索引只有一個根節點，它實際就是位于樹的最頂端的分支節點。可以用下圖一來描述B樹索引的結構。其中，B表示分支節點，而L表示葉子節點。    對于分支節點塊（包括根節點塊）來說，其所包含的索引條目都是按照順序排列的（缺省是升序排列，也可以在創建索引時指定為降序排列）。每個索引條目（也可以叫做每條記錄）都具有兩個字段。第一個字段表示當前該分支節點塊下面所鏈接的索引塊中所包含的最小鍵值；第二個字段為四個字節，表示所鏈接的索引塊的地址，該地址指向下面一個索引塊。在一個分支節點塊中所能容納的記錄行數由數據塊大小以及索

4、引鍵值的長度決定。比如從上圖一可以看到，對于根節點塊來說，包含三條記錄，分別為（0 B1）、（500 B2）、（1000 B3），它們指向三個分支節點塊。其中的0、500和1000分別表示這三個分支節點塊所鏈接的鍵值的最小值。而B1、B2和B3則表示所指向的三個分支節點塊的地址。    對于葉子節點塊來說，其所包含的索引條目與分支節點一樣，都是按照順序排列的（缺省是升序排列，也可以在創建索引時指定為降序排列）。每個索引條目（也可以叫做每條記錄）也具有兩個字段。第一個字段表示索引的鍵值，對于單列索引來說是一個值；而對于多列索引來說則是多個值組合在一起的。第

5、二個字段表示鍵值所對應的記錄行的ROWID，該ROWID是記錄行在表里的物理地址。如果索引是創建在非分區表上或者索引是分區表上的本地索引的話，則該ROWID占用6個字節；如果索引是創建在分區表上的全局索引的話，則該ROWID占用10個字節。 bitmap索引      位圖（bitmap）索引是另外一種索引類型，它的組織形式與B樹索引相同，也是一棵平衡樹。與B樹索引的區別在于葉子節點里存放索引條目的方式不同。從前面我們知道，B樹索引的葉子節點里，對于表里的每個數據行，如果被索引列的值不為空的，則會為該記錄行在葉子節點里維護一個對應的索

6、引條目。而位圖索引則不是這樣，其葉子節點里存放的索引條目如下圖所示。    假設某個表T里所有的記錄在列C1上只具有三個值：01、02和03。在表T的C1列上創建位圖索引以后，則葉子節點的內容如圖9-14所示。可以看到，位圖索引只有三個索引條目，也就是每個C1列的值對應一個索引條目。位圖索引條目上還包含表里第一條記錄所對應的ROWID以及最后一條記錄所對應的ROWID。索引條目的最后一部分則是由多個bit位所組成的bitmap，每個bit位就對應一條記錄。       當發出where c1='0

7、1'這樣的SQL語句時，oracle會去搜索01所在的索引條目，然后掃描該索引條目中的bitmap里所有的bit位。第一個bit位為1，則說明第一條記錄上的C1值為01，于是返回第一條記錄所在的ROWID（根據該索引條目里記錄的start ROWID加上行號得到該記錄所在的ROWID）。第二個bit位為0，則說明第二條記錄上的C1值不為01，依此類推。另外，如果索引列為空，也會在位圖索引里記錄，也就是將對應的bit位設置為0即可。       如果索引列上不同值的個數比較少的時候，比如對于性別列（男或女）等，則使用位圖索引會比

8、較好，因為它對空間的占用非常少（因為都是用bit位來表示表里的數據行），從而在掃描索引的時候，掃描的索引塊的個數也比較少。可以試想一下，如果在列的不同值非常多的列上，比如主鍵列上，創建位圖索引，則產生的索引條目就等于表里記錄的條數，同時每個索引條目里的bitmap里，只有一個1，其它都是0。這樣還不如B樹索引的效率高。如果被索引的列經常被更新的話，則不適合使用位圖索引。因為當更新位圖所在的列時，由于要在不同的索引條目之間修改bit位，比如將第一條記錄從01變為02，則必須將01所在的索引條目的第一個bit位改為0，再將02所在的索引條目的第一個bit位改為1。因此，在更新索引條目的過程中，會鎖

9、定位圖索引里多個索引條目。也就是同時只能有一個用戶能夠更新表T，從而降低了并發性。位圖索引比較適合用在數據倉庫系統里，不適合用在OLTP系統里。 HASH索引      使用HASH索引必須要使用HASH集群。建立一個集群或HASH集群的同時，也就定義了一個集群鍵。這個鍵告訴Oracle如何在集群上存儲表。在存儲數據時，所有與這個集群鍵相關的行都被存儲在一個數據庫塊上。如果數據都存儲在同一個數據庫塊上，并且將HASH索引作為WHERE子句中的確切匹配，Oracle就可以通過執行一個HASH函數和I/O來訪問數據- 而通過使用一個二元高

10、度為4的B樹索引來訪問數據，則需要在檢索數據時使用4個I/O。如圖2-5所示，其中的查詢是一個等價查詢，用于匹配HASH列和確切的值。Oracle可以快速使用該值，基于HASH函數確定行的物理存儲位置。      HASH索引可能是訪問數據庫中數據的最快方法，但它也有自身的缺點。集群鍵上不同值的數目必須在創建HASH集群之前就要知道。需要在創建HASH集群的時候指定這個值。低估了集群鍵的不同值的數字可能會造成集群的沖突(兩個集群的鍵值擁有相同的HASH值)。這種沖突是非常消耗資源的。沖突會造成用來存儲額外行的緩沖溢出，然后造成額外的I/O。如果

11、不同HASH值的數目已經被低估，您就必須在重建這個集群之后改變這個值。ALTER CLUSTER命令不能改變HASH鍵的數目。      HASH集群還可能浪費空間。如果無法確定需要多少空間來維護某個集群鍵上的所有行，就可能造成空間的浪費。如果不能為集群的未來增長分配好附加的空間，HASH集群可能就不是最好的選擇。如果應用程序經常在集群表上進行全表掃描，HASH集群可能也不是最好的選擇。由于需要為未來的增長分配好集群的剩余空間量，全表掃描可能非常消耗資源。 在實現HASH集群之前一定要小心。您需要全面地觀察應用程序，保證在實現這個選項

12、之前已經了解關于表和數據的大量信息。通常，HASH對于一些包含有序值的靜態數據非常有效。技巧：HASH索引在有限制條件(需要指定一個確定的值而不是一個值范圍)的情況下非常有用。 聚族索引      在這里還是用字典來進行類比，一般來說漢語字典中有幾種索引，如拼音、偏旁、筆畫等。字典本身的組織也是排序的，我記得一般是按照拼音排序的。這里的拼音就是聚族索引。也就是說聚族索引的組織順序和數據本身的組織順序是一致的 ，這也解釋了數據庫中只能定義一個聚族索引的原因，因為數據本身只能按一種方式進行排序。   

13、;   那聚族索引有什么特別的好處呢，這個好處就是在數據庫中執行查找一批數據的語句會比較快，因為數據已經按照聚族索引排好序了，很少的io操作就可以將數據從庫中取出。好比你在字典中查找發音從從a到c的漢字，只需要查到a的開始頁和c的結束頁，中間的所有頁都符合查詢要求，不用再一頁一頁地查找。  非聚族索引      非聚族索引就好比字典里的偏旁、筆畫索引，其 索引組織順序和數據組織順序不一致 ，因此非聚族索引可以創建多個。當查找一條數據時，非聚族索引和聚族索引的效率相差不大，但查找一批數據

14、(n)時，非聚族索引需要的io可能是聚族索引的n倍，因為非聚族索引需要一條一條地進行查找。 Oracle索引應用與類型選擇三、 唯一索引1、 何時創建：當某列任意兩行的值都不相同2、 當建立Primary Key(主鍵)或者Unique constraint(唯一約束)時，唯一索引將被自動建立3、 語法：CREATE UNIQUE INDEX index ON table (column);4、 演示四、 組合索引1、 何時創建：當兩個或多個列經常一起出現在where條件中時，則在這些列上同時創建組合索引2、 組合索引中列的順序是任意的，也無需相鄰。但是建議將最頻繁訪問的列放在列表的

15、最前面3、 演示(組合列，單獨列)五、 位圖索引1、 何時創建：列中有非常多的重復的值時候。例如某列保存了 “性別”信息。Where 條件中包含了很多OR操作符。較少的update操作，因為要相應的跟新所有的bitmap2、 結構：位圖索引使用位圖作為鍵值，對于表中的每一數據行位圖包含了TRUE(1)、FALSE(0)、或NULL值。3、 優點：位圖以一種壓縮格式存放，因此占用的磁盤空間比標準索引要小得多4、 語法：CREATE BITMAP INDEX index ON table (column, column.);5、 演示：create table bitmaptable as sel

16、ect * from indextable where owner in('SYS','PUBLIC');分析，查找，建立索引，查找六、 基于函數的索引   1、 何時創建：在WHERE條件語句中包含函數或者表達式時2、 函數包括：算數表達式、PL/SQL函數、程序包函數、SQL函數、用戶自定義函數。3、 語法：CREATE INDEX index ON table (FUNCTION(column);4、 演示必須要分析表，并且 query_rewrite_enabled=TRUE或者使用提示/*+ INDEX(ic_index)*/七、

17、反向鍵索引目的：比如索引值是一個自動增長的列：多個用戶對集中在少數塊上的索引行進行修改，容易引起資源的爭用，比如對數據塊的等待。此時建立反向索 引。性能問題：語法：重建為標準索引：反之不行八、 鍵壓縮索引比如表landscp的數據如下：site feature jobBritten Park, Rose Bed 1, PruneBritten Park, Rose Bed 1, MulchBritten Park, Rose Bed 1,SprayBritten Park, Shrub Bed 1, MulchBritten Park, Shrub Bed 1, WeedBritten Par

18、k, Shrub Bed 1, Hoe查詢時，以上3列均在where條件中同時出現，所以建立基于以上3列的組合索引。但是發現重復值很多，所以考慮壓縮特性。Create index zip_idxon landscp(site, feature, job)compress 2;將索引項分成前綴(prefix)和后綴(postfix)兩部分。前兩項被放置到前綴部分。Prefix 0: Britten Park, Rose Bed 1Prefix 1: Britten Park, Shrub Bed 1實際所以的結構為：0 Prune0 Mulch0 Spray1 Mulch1 Weed1 Hoe特點：組合索引的