索引的特點(diǎn)

• 對(duì)于具有只讀特性或較少插入比规、更新或刪除操作的大表通常可以提高查詢(xún)速度
• 可以對(duì)表的一列或多列建立索引
• 建立索引的數(shù)量沒(méi)有限制
• 索引需要磁盤(pán)存儲(chǔ)米间，需要Oracle自動(dòng)維護(hù)
• 索引對(duì)用戶(hù)透明堵未，是否使用索引是Oracle決定的

全表掃描

Oracle讀取表中所有行，此時(shí)通過(guò)多塊都操作可以減少I(mǎi)O的次數(shù)渣聚，利用多塊讀可以提高全表掃描的速度，只有在全表掃描的情況下才能使用多塊讀僧叉，在較大的表上不建議使用全表掃描奕枝，如果讀取表的數(shù)據(jù)總量超過(guò)5%-10%，那么通過(guò)全表掃描彪标，并行查詢(xún)可能會(huì)使得我們的路徑采用全表掃描。

通過(guò)ID（RowID）

ROWID是數(shù)據(jù)行所存儲(chǔ)的數(shù)據(jù)塊地址掷豺，ROWID指出了數(shù)據(jù)文件捞烟、塊號(hào)、行號(hào)当船，是最快查詢(xún)數(shù)據(jù)的方式题画，這種方式不會(huì)使用多塊讀，而是采用單塊讀的方式德频。

使用索引

在索引中除了存儲(chǔ)索引值苍息，還存儲(chǔ)了對(duì)應(yīng)的ROWID。索引掃描的步驟：

• 掃描索引得到相應(yīng)的ROWID
• 通過(guò)ROWID從表中讀取相應(yīng)的數(shù)據(jù)
  數(shù)據(jù)的選擇性越高（身份證）壹置，表數(shù)據(jù)在數(shù)據(jù)塊中的分布越集中竞思，索引的性能越好。

索引掃描類(lèi)型

• 索引唯一掃描：主鍵钞护、唯一鍵盖喷，Oracle通常返回一個(gè)數(shù)據(jù)行
• 索引范圍掃描：在唯一鍵上使用染個(gè)操作符（>,<,<>,>=,<=,between）；在組合索引难咕，只使用部分列進(jìn)行查詢(xún)课梳，導(dǎo)致查詢(xún)多行距辆；對(duì)非唯一索引列上進(jìn)行查詢(xún)
• 索引全表掃描：查詢(xún)出的數(shù)據(jù)必須全部從索引中得到
• 索引快速掃描：掃描索引塊中的所有數(shù)據(jù)，這點(diǎn)與索引全表掃描相似暮刃，但是索引快速掃描不進(jìn)行數(shù)據(jù)的排序跨算，在這種方式下可以使用多塊讀功能，也可以使用并行讀功能椭懊，最大化數(shù)據(jù)的吞吐量诸蚕。

限制索引的使用情況

• 使用不等于運(yùn)算符（<>,!=）：
• 使用 IS NULL或 IS NOT NULL：如果被索引的列在某些行中存在NULL值，在索引列中就不會(huì)有相應(yīng)的條目灾搏，位圖索引對(duì)于NULL列會(huì)進(jìn)行記錄挫望，因此位圖索引對(duì)于NULL搜索通常較快。
• 使用函數(shù)
• 比較不匹配的數(shù)據(jù)類(lèi)型：

集群因子

簡(jiǎn)單的說(shuō)狂窑，Index Clustering Factor是通過(guò)一個(gè)索引掃描一張表媳板，需要訪問(wèn)的表的數(shù)據(jù)塊的數(shù)量，即對(duì)I/O的影響泉哈，也代表索引鍵存儲(chǔ)位置是否有序蛉幸。

• 如果越有序，即相鄰的鍵值存儲(chǔ)在相同的block丛晦，那么這時(shí)候Clustering Factor的值就越低奕纫；
• 如果不是很有序，即鍵值是隨機(jī)的存儲(chǔ)在block上烫沙，這樣在讀取鍵值時(shí)匹层，可能就需要一次又一次的去訪問(wèn)相同的block，從而增加了I/O锌蓄。
  Clustering Factor的計(jì)算方式如下：
  (1)升筏、掃描一個(gè)索引(large index range scan)；
  (2)瘸爽、比較某行的rowid和前一行的rowid您访，如果這兩個(gè)rowid不屬于同一個(gè)數(shù)據(jù)塊，那么cluster factor增加1剪决；
  (3)灵汪、整個(gè)索引掃描完畢后，就得到了該索引的clustering factor。
  如果clustering factor接近于表存儲(chǔ)的塊數(shù)，說(shuō)明這張表是按照索引字段的順序存儲(chǔ)的勘究。如果clustering factor接近于行的數(shù)量，那說(shuō)明這張表不是按索引字段順序存儲(chǔ)的担锤。在計(jì)算索引訪問(wèn)成本的時(shí)候，這個(gè)值十分有用乍钻。Clustering Factor乘以選擇性參數(shù)(selectivity)就是訪問(wèn)索引的開(kāi)銷(xiāo)肛循。
  如果這個(gè)統(tǒng)計(jì)數(shù)據(jù)不能真實(shí)反映出索引的真實(shí)情況铭腕，那么可能會(huì)造成優(yōu)化器錯(cuò)誤的選擇執(zhí)行計(jì)劃。另外如果某張表上的大多數(shù)訪問(wèn)是按照某個(gè)索引做索引掃描多糠，那么將該表的數(shù)據(jù)按照索引字段的順序重新組織累舷，可以提高該表的訪問(wèn)性能。

二元高度

二元高度就是Oracle數(shù)據(jù)庫(kù)中幫助數(shù)據(jù)庫(kù)管理員來(lái)做好這個(gè)工作的工具夹孔。索引二元高高度對(duì)把Rowid返回給用戶(hù)進(jìn)程時(shí)所要求的I/0數(shù)量起到非常關(guān)鍵的作用被盈。數(shù)據(jù)庫(kù)管理員只要了解這個(gè)即可，而不需要花費(fèi)很長(zhǎng)的時(shí)間去搞明白什么叫做二元高度搭伤。在Oracle數(shù)據(jù)庫(kù)中只怎，系統(tǒng)視圖sys.dba_indexes就保存在索引的二元高度信息。如下圖所示的SQL語(yǔ)句怜俐，可以查詢(xún)處索引的二元高度的值身堡。 字段Blevel表示二元高度;Index_name則表示索引的名稱(chēng)。一般來(lái)說(shuō)拍鲤，二元高度越低越好(最低為0)贴谎。作為數(shù)據(jù)庫(kù)管理員，就是需要相方設(shè)法讓這個(gè)二元高度的值變?yōu)?季稳。雖然這個(gè)目標(biāo)看起來(lái)比較簡(jiǎn)單擅这，但是實(shí)現(xiàn)起來(lái)卻有相當(dāng)大的困難。

二元高度主要隨著表中索引的非NULL值以及索引列中值的寬度而變化景鼠。如果索引列上大量的行被刪除仲翎，那么他的二元高度不會(huì)降低，重建索引會(huì)降低铛漓，如果一個(gè)索引中被刪除的行接近20%-25%溯香，重建索引會(huì)降低二元高度。

直方圖

在分析表和索引時(shí)票渠，直方圖用于記錄數(shù)據(jù)的分布逐哈，通過(guò)獲取該信息芬迄，基于成本的優(yōu)化器就可以決定使用返回少量行的索引问顷，而避免使用基于限制條件返回許多行的索引。直方圖的使用不受索引的限制禀梳，我們可以在表的任何列上構(gòu)建直方圖（一般是在索引上構(gòu)建）
??構(gòu)建直方圖的最主要的原因就是：幫助優(yōu)化器在表中數(shù)據(jù)嚴(yán)重傾斜時(shí)做出更好的選擇杜窄。如果一個(gè)表中的列上（通常使用索引）數(shù)據(jù)發(fā)生嚴(yán)重的傾斜。那么在這個(gè)列上建立直方圖將非常有意義算途，這樣優(yōu)化器就知道什么時(shí)候該使用索引塞耕，什么時(shí)候不需要使用索引。

建立索引

CREATE [UNIQUE|BITMAP]  INDEX[schema.] index_name  
                               ---UNIQUE:說(shuō)明該索引是唯一索引  BITMAP:創(chuàng)建位圖索引
    ON [schema.] table_name
(column_name[DESC|ASC][, column_name[DESC|ASC]]...) ---   DESC|ASC:說(shuō)明創(chuàng)建的索引
為降序或者升序排列
[REVERSE]                                         --REVERSE:說(shuō)明創(chuàng)建反向鍵索引
[TABLESPACE  tablespace_name]    ----  TABLESPACE:說(shuō)明要?jiǎng)?chuàng)建
 的索引所存儲(chǔ)的表空間
[PCTFREE n]                           ----PCTFREE:索引塊中預(yù)留的空間比例         
[INITRANS n]                         ---INITRANS:每一個(gè)索引塊中分配的事務(wù)數(shù)
[MAXTRANS n]            ---MAXTRANS:每一個(gè)索引塊中分配的最多事務(wù)數(shù)
[instorage state]         --instorage state:說(shuō)明索引中區(qū)段extent如何分配
[LOGGING|NOLOGGING]        ----LOGGING|NOLOGGING：說(shuō)明要記錄（不記錄）索引
 相關(guān)的操作嘴瓤，并保存在聯(lián)機(jī)重做日志中
[NOSORT]                ---NOSORT:不需要在創(chuàng)建索引時(shí)按鍵值進(jìn)行排序

查看索引

USER_IND_COLUMNS：查詢(xún)索引列相關(guān)的信息
USER_INDEXES：查詢(xún)索引信息扫外，DROPPED表示該對(duì)象是否被刪除

B樹(shù)索引

索引的頂層為根莉钙，它包括指向索引中下一層次的條目。下一層次為分支塊筛谚，它又指向位于索引中下一層索引中下一層次的塊磁玉，最底層的是葉節(jié)點(diǎn)，它包含指向表行的索引條目驾讲。葉塊是雙向關(guān)聯(lián)的蚊伞，這邊與按鍵值升序或降序掃描索引；

這里寫(xiě)圖片描述

一個(gè)索引條目包含以下組件：

• 條目頭：存儲(chǔ)列數(shù)和鎖定信息
• 鍵列長(zhǎng)度/值對(duì)：用于定義鍵中的列大小吮铭，后面跟隨列值（此類(lèi)長(zhǎng)度/值對(duì)的數(shù)目就是索引中的最大列數(shù)）时迫。

在非分區(qū)表的B 樹(shù)索引中：

• 當(dāng)多個(gè)行具有相同的鍵值時(shí)，如果不壓縮索引谓晌，鍵值會(huì)出現(xiàn)重復(fù)
• 當(dāng)某行包含的所有鍵列為NULL 時(shí)掠拳，該行沒(méi)有對(duì)應(yīng)的索引條目。因此扎谎，當(dāng)WHERE 子句指定了NULL 時(shí)碳想，將始終執(zhí)行全表掃描

對(duì)表執(zhí)行DML 操作時(shí)，Oracle 服務(wù)器會(huì)維護(hù)所有索引毁靶。下面說(shuō)明對(duì)索引執(zhí)行DML 命令產(chǎn)生的效果：

• 執(zhí)行插入操作導(dǎo)致在相應(yīng)塊中插入索引條目胧奔。
• 刪除一行只導(dǎo)致對(duì)索引條目進(jìn)行邏輯刪除。已刪除行所占用的空間不可供后面新的葉條目使用预吆。

• 更新鍵列導(dǎo)致對(duì)索引進(jìn)行邏輯刪除和插入龙填。PCTFREE 設(shè)置對(duì)索引沒(méi)有影響，但創(chuàng)建時(shí)除外拐叉。即使索引塊的空間少于PCTFREE 指定的空間岩遗，也可以向索引塊添加新條目。

  
  
  這里寫(xiě)圖片描述

位圖索引

位圖索引使用位圖標(biāo)識(shí)被索引的列值凤瘦，它適用于沒(méi)有大量更新任務(wù)的數(shù)據(jù)倉(cāng)庫(kù)宿礁，因?yàn)槭褂梦粓D索引時(shí)，每個(gè)位圖索引項(xiàng)與表中大量的行有關(guān)聯(lián)蔬芥，當(dāng)表中有大量更新梆靖、刪除、插入時(shí)笔诵，位圖索引相應(yīng)地需要做大量的更改返吻，而且索引所占用的磁盤(pán)空間也會(huì)明顯增加，并且索引在更新時(shí)受影響的索引需要鎖定乎婿，所以位圖索引不適合大量更新操作的OLTP系統(tǒng)测僵。

位圖發(fā)揮最大威力的場(chǎng)合是：當(dāng)一個(gè)表中包含了多個(gè)位圖索引，Oracle就可以合并從每個(gè)位圖索引得到的結(jié)果集谢翎，快速刪除不必要數(shù)據(jù)捍靠，對(duì)于較低基數(shù)的位圖索引來(lái)說(shuō)沐旨，位圖索引的尺寸遠(yuǎn)遠(yuǎn)小于B樹(shù)索引，因此可以大大減少I(mǎi)O的數(shù)量榨婆。

對(duì)于位圖索引的列希俩，列值的數(shù)量要求較少或者中等（索引列基數(shù)較小）纲辽。如列的基數(shù)是4颜武，Oracle為每個(gè)唯一鍵創(chuàng)建一個(gè)位圖，然后把與鍵值相關(guān)聯(lián)的ROWID保存為位圖拖吼，最多可以包括30列鳞上。

對(duì)于非常大的表來(lái)說(shuō)，在多個(gè)低基數(shù)列上建立位圖索引是一個(gè)很好的選擇吊档。對(duì)于位圖索引來(lái)說(shuō)篙议，即使從表中讀取很多行，也會(huì)使用位圖索引怠硼。例如在一個(gè)sex列上建立索引鬼贱，每次從表中讀取半數(shù)的數(shù)據(jù)行，但是還是會(huì)使用位圖索引香璃。

這里寫(xiě)圖片描述

位圖索引插入問(wèn)題

• 位圖索引在批處理（單用戶(hù)）操作中加載表（插入操作）方面通常比B樹(shù)做的好
• 當(dāng)有多個(gè)會(huì)話(huà)同時(shí)向表中插入數(shù)據(jù)行時(shí)不應(yīng)該使用位圖索引
• 當(dāng)每條記錄都增加一個(gè)新值時(shí)这难，B-樹(shù)索引要比位圖索引快3倍
  在B樹(shù)索引中，可以實(shí)現(xiàn)行級(jí)鎖葡秒，但是在位圖索引中姻乓，因?yàn)閷?duì)ROWID進(jìn)行壓縮存放（一個(gè)ROWID范圍+位圖），因此每次鎖定的都是整個(gè)的ROWID范圍眯牧，因此對(duì)表中的位圖索引列進(jìn)行更新的時(shí)候蹋岩，并發(fā)性很差，容易導(dǎo)致死鎖学少。select不會(huì)受到影響剪个。
  位圖索引有很多限制：
• 基于規(guī)則的優(yōu)化器不會(huì)考慮位圖索引
• 當(dāng)執(zhí)行alter table 語(yǔ)句并修改包括位圖索引列時(shí)，會(huì)使得位圖索引失效
• 位圖索引不包含任何列數(shù)據(jù)版确，不能用于任何類(lèi)型的完整性檢查
• 位圖索引不能被聲明為唯一索引
• 位圖索引最大的長(zhǎng)度為30

Hash索引

使用HASH索引必須要使用HASH集群扣囊。建立一個(gè)集群或HASH集群的同時(shí)，也就定義了一個(gè)集群鍵阀坏。

這里寫(xiě)圖片描述

??這個(gè)鍵告訴Oracle如何在集群上存儲(chǔ)表如暖。在存儲(chǔ)數(shù)據(jù)時(shí)笆檀，所有與這個(gè)集群鍵相關(guān)的行都被存儲(chǔ)在一個(gè)數(shù)據(jù)庫(kù)塊上忌堂。如果數(shù)據(jù)都存儲(chǔ)在同一個(gè)數(shù)據(jù)庫(kù)塊上，并且將HASH索引作為WHERE子句中的確切匹配酗洒，Oracle就可以通過(guò)執(zhí)行一個(gè)HASH函數(shù)和I/O來(lái)訪問(wèn)數(shù)據(jù)-- 而通過(guò)使用一個(gè)二元高度為4的B樹(shù)索引來(lái)訪問(wèn)數(shù)據(jù)士修，則需要在檢索數(shù)據(jù)時(shí)使用4個(gè)I/O枷遂。如下圖所示，其中的查詢(xún)是一個(gè)等價(jià)查詢(xún)棋嘲，用于匹配HASH列和確切的值酒唉。Oracle可以快速使用該值，基于HASH函數(shù)確定行的物理存儲(chǔ)位置沸移。

這里寫(xiě)圖片描述

HASH索引可能是訪問(wèn)數(shù)據(jù)庫(kù)中數(shù)據(jù)的最快方法痪伦，但它也有自身的缺點(diǎn)。集群鍵上不同值的數(shù)目必須在創(chuàng)建HASH集群之前就要知道雹锣。需要在創(chuàng)建HASH集群的時(shí)候指定這個(gè)值网沾。低估了集群鍵的不同值的數(shù)字可能會(huì)造成集群的沖突(兩個(gè)集群的鍵值擁有相同的HASH值)。這種沖突是非常消耗資源的蕊爵。沖突會(huì)造成用來(lái)存儲(chǔ)額外行的緩沖溢出辉哥，然后造成額外的I/O。如果不同HASH值的數(shù)目已經(jīng)被低估攒射，您就必須在重建這個(gè)集群之后改變這個(gè)值醋旦。ALTER CLUSTER命令不能改變HASH鍵的數(shù)目。
??HASH集群還可能浪費(fèi)空間会放。如果無(wú)法確定需要多少空間來(lái)維護(hù)某個(gè)集群鍵上的所有行饲齐，就可能造成空間的浪費(fèi)。如果不能為集群的未來(lái)增長(zhǎng)分配好附加的空間咧最，HASH集群可能就不是最好的選擇箩张。
如果應(yīng)用程序經(jīng)常在集群表上進(jìn)行全表掃描，HASH集群可能也不是最好的選擇窗市。由于需要為未來(lái)的增長(zhǎng)分配好集群的剩余空間量先慷，全表掃描可能非常消耗資源。

在實(shí)現(xiàn)HASH集群之前一定要小心咨察。您需要全面地觀察應(yīng)用程序论熙，保證在實(shí)現(xiàn)這個(gè)選項(xiàng)之前已經(jīng)了解關(guān)于表和數(shù)據(jù)的大量信息。通常摄狱，HASH對(duì)于一些包含有序值的靜態(tài)數(shù)據(jù)非常有效脓诡。
技巧：
HASH索引在有限制條件(需要指定一個(gè)確定的值而不是一個(gè)值范圍)的情況下非常有用。

反向鍵索引

反向鍵索引是指在創(chuàng)建索引過(guò)程中對(duì)索引列創(chuàng)建的索引值的字節(jié)反向媒役，使用反向鍵索引的好處是將值連續(xù)插入到索引中的反向鍵能避免爭(zhēng)用祝谚。

情景：
1，insert繁忙酣衷，主鍵是用序列號(hào)（每次加1）交惯，主鍵是有索引的，用序列號(hào)生成席爽，因此相鄰的索引記錄就可能存在于同一個(gè)數(shù)據(jù)塊中意荤，引起數(shù)據(jù)塊競(jìng)爭(zhēng)，導(dǎo)致性能下降只锻。
2玖像，隨著時(shí)間增長(zhǎng)，久值被刪除齐饮，新值被插入捐寥，逐漸的序列號(hào)很大，索引樹(shù)是往序列號(hào)大的一面偏祖驱，樹(shù)的深度加深上真，所以索引效率低下，造成嚴(yán)重的性能問(wèn)題羹膳。

反向鍵索引并不是一種被廣泛使用的索引方式睡互，主要適用于使用等值運(yùn)算符“=”進(jìn)行的查詢(xún)。它的索引鍵值的分布是分散式的陵像，并不是按照索引列的有序方式存儲(chǔ)就珠，因而無(wú)法進(jìn)行范圍化的查詢(xún)。反向鍵索引的優(yōu)勢(shì)：一方面醒颖，它可以平衡I/O妻怎，有效地減少應(yīng)用并發(fā)時(shí)的爭(zhēng)用，另一方面泞歉，多數(shù)情況下數(shù)據(jù)在堆表中是按照插入的順序而存儲(chǔ)在鄰近的位置上逼侦，所以數(shù)據(jù)塊在內(nèi)存中被再次使用的可能性是比較大的⊙遥總的來(lái)說(shuō)榛丢，大多數(shù)情況下反向鍵索引帶來(lái)的性能收益往往小于其所花費(fèi)的代價(jià)，應(yīng)用范圍偏窄挺庞，在特殊情況下可以靈活使用晰赞。

基于函數(shù)索引

注意事項(xiàng)

• 基于函數(shù)的索引只能針對(duì)一種函數(shù)，其他函數(shù)不起作用
• 控制索引的數(shù)量选侨，因?yàn)閷?duì)DML有影響

監(jiān)控索引的使用

• 啟動(dòng)監(jiān)控：alter index *** monitoring usage
• 一定周期后關(guān)閉監(jiān)控：alter index *** nomonitoring usage
• 查看監(jiān)控情況：查詢(xún)?cè)噲Dv$object_usage,也可以使用explan的輸出和使用SQL trace等工具

重建索引

索引需要維護(hù)掖鱼，不然如果建立了索引的表中有大量的刪除和插入操作，會(huì)使得索引很大援制，因?yàn)樵趧h除操作后戏挡，刪除值所占用的索引空間不能被索引自動(dòng)重新使用，而插入操作會(huì)不斷使得索引變大晨仑，對(duì)于大表和DML操作很頻繁的表褐墅，索引的維護(hù)是很重要的拆檬。Oracle提供了一個(gè)Rebuild命令來(lái)重建索引，使得索引空間可以重用刪除值所占用的空間掌栅，使得索引更加緊湊。
??使用索引重建不會(huì)影響索引的使用码泛，但是有些限制條件如果不能使用DML和DDL操作猾封，如果使用聯(lián)機(jī)重建索引的方式，可以執(zhí)行DML操作噪珊，但是不能執(zhí)行DDL操作晌缘。

alter index idx_a rebuild;
alter index idx_a rebuild tablespace idx_tb;
alter index idx_a rebuild pctfree 30 storage (next 100k);
alter index idx_a rebuild online;

維護(hù)索引:修改索引的各種參數(shù)

增加索引磁盤(pán)空間：增加后可以通過(guò)查詢(xún)user_segments查看

alter index idx_a allocate extend;

合并索引碎片:可以釋放磁盤(pán)空間

alter index idx_a coalease;

刪除索引

如果經(jīng)過(guò)索引監(jiān)控發(fā)現(xiàn)索引無(wú)效，或者處理效率考慮暫時(shí)刪除該索引痢站，則使用drop index即可磷箕。