Oracle程序員面試分類模擬14

Oracle程序員面試分類模擬14簡答題1.

簡述索引的特性及其應(yīng)用正確答案：一般來說索引有3大特性，索引高度比較低、索引存儲列值及索引本身有序，對這3大特性的應(yīng)用見表。

INCLUDE（江南博哥）PICTURE\d":8089/YFB12/tu/1907/j/cm/oraf11.5AD1B1.jpg"INET

另外，需要特別注意的是，索引不存儲空值。

2.

如何預估即將創(chuàng)建索引的大小?正確答案：如果當前表大小是1TB，那么在某一列上創(chuàng)建索引的話索引大概占用多大的空間?對于這個問題，Oracle提供了兩種可以預估將要創(chuàng)建的索引大小的辦法：

1)利用系統(tǒng)包DBMS_SPACE.CREATE_INDEX_COST直接得到。利用DBMS_SPACE.CREATE_TABLE_COST可以獲得將要創(chuàng)建的表的大小。

2)利用Oracle11g新特性NOTERAISEDWHENEXPLAINPLANFORCREATEINDEX。

3.

如何監(jiān)控索引的使用狀況?正確答案：在開發(fā)應(yīng)用程序時，可能會建立很多索引，那么這些索引的使用到底怎么樣，是否有些索引一直都沒有用到過，在這種情況下就需要對這些索引進行監(jiān)控，以便確定它們的使用情況，并為是否可以清除它們給出依據(jù)。

監(jiān)控索引有兩種方式：

(1)直接監(jiān)控索引的使用情況

1)設(shè)置所要監(jiān)控的索引：ALTERINDEXIDX_T_XXMONITORINGUSAGE;。

2)查看該索引有沒有被使用：SELECT*FROMV$OBJECT_USAGE;。

3)關(guān)閉監(jiān)控：ALTERINDEXIDX_T_XXNOMONITORINGUSAGE;。

查詢V$OBJECT_USAGE就可以知道數(shù)據(jù)庫對索引的使用情況了。通過一段時間的監(jiān)控，就可以確定哪些是無用的索引。另外，為了避免使用V$OBJECT_USAGE只能查詢到當前用戶下索引的監(jiān)控情況，可以使用如下語句查詢數(shù)據(jù)庫中所有被監(jiān)控索引的使用情況：

(2)通過查看歷史的執(zhí)行計劃，分析索引的使用情況

可以從視圖DBA_HIST_SQL_PLAN中獲取到數(shù)據(jù)庫中所有索引的掃描次數(shù)情況，然后根據(jù)掃描次數(shù)和開發(fā)人員溝通是否需要保留索引。

4.

哪些操作會導致索引失效?正確答案：當某些操作導致數(shù)據(jù)行的ROWID改變，索引就會完全失效?？梢苑制胀ū砗头謪^(qū)表來討論哪些操作將導致索引失效。

(1)普通表索引失效的情形

1)手動置索引無效：ALTERINDEXIND_OBJECT_IDUNUSABLE;。

2)如果對表進行MOVE操作(包含移動表空間和壓縮操作)或在線重定義表后，那么該表上所有的索引狀態(tài)會變?yōu)閁NUSABLE。MOVE操作的SQL語句為：ALTERTABLETTMOVE;。

3)若將表中LONG數(shù)據(jù)類型修改為CLOB類型，則會導致表上索引失效。

4)SQL*Loader加載數(shù)據(jù)。

在SQL*Loader加載過程中會維護索引，由于數(shù)據(jù)量比較大，在SQL*Loader加載過程中出現(xiàn)異常情況，也會導致Oracle來不及維護索引，導致索引處于失效狀態(tài)，影響查詢和加載。異常情況主要有：在加載過程中殺掉SQL*Loader進程、重啟或表空間不足等。

(2)分區(qū)表索引失效的情形

1)對分區(qū)表的某個含有數(shù)據(jù)的分區(qū)執(zhí)行了TRUNCATE、DROP操作可以導致該分區(qū)表的全局索引失效，而分區(qū)索引依然有效，如果操作的分區(qū)沒有數(shù)據(jù)，那么不會影響索引的狀態(tài)。需要注意的是，對分區(qū)表的ADD操作對分區(qū)索引和全局索引沒有影響。

2)執(zhí)行EXCHANGE操作后，全局索引和分區(qū)索引都無條件地會被置為UNUSABLE(無論分區(qū)是否含有數(shù)據(jù))。但是，若包含INCLUDINGINDEXES子句(缺省情況下為EXCLUDINGINDEXES)，則全局索引會失效，而分區(qū)索引依然有效。

3)如果執(zhí)行SPLIT的目標分區(qū)含有數(shù)據(jù)，那么在執(zhí)行SPLIT操作后，全局索引和分區(qū)索引都會被置為UNUSABLE。如果執(zhí)行SPLIT的目標分區(qū)沒有數(shù)據(jù)，那么不會影響索引的狀態(tài)。

4)對分區(qū)表執(zhí)行MOVE操作后，全局索引和分區(qū)索引都會被置于無效狀態(tài)。

5)手動置其無效：ALTERINDEXIND_OBJECT_IDUNUSABLE;。

對于分區(qū)表而言，除了ADD操作之外，TRUNCATE、DROP、EXCHANGE和SPLIT操作均會導致全局索引失效，但是可以加上UPDATEGLOBALINDEXES子句讓全局索引不失效。重建分區(qū)索引的命令為：ALTERINDEXIDX_RANG_LHRREBUILDPARTITIONP1;。

分區(qū)表的索引是否失效的情形見表。

5.

聚簇因子(ClusteringFactor)是什么?正確答案：Oracle數(shù)據(jù)庫中最普通、最為常用的即為堆表，堆表的數(shù)據(jù)存儲方式為無序存儲，當對數(shù)據(jù)進行檢索的時候，非常消耗資源，這個時候就可以為表創(chuàng)建索引了。在索引中，數(shù)據(jù)是按照一定的順序排列起來的。當新建或重建索引時，索引列上的順序是有序的，而表上的順序是無序的，這樣就存在了差異，即表現(xiàn)為聚簇因子(ClusteringFactor，簡稱CF)，也稱為群集因子或集群因子等，本書統(tǒng)一稱為聚簇因子。聚簇因子值的大小對CBO判斷是否選擇相關(guān)的索引起著至關(guān)重要的作用。

在Oracle數(shù)據(jù)庫中，聚簇因子是指按照索引鍵值排序的索引行和存儲于對應(yīng)表中數(shù)據(jù)行的存儲順序的相似程度，也就是說，表中數(shù)據(jù)的存儲順序和某些索引字段順序的符合程度。CF是基于表上索引列上的一個值，每一個索引都有一個CF值。

Oracle按照索引塊所存儲的ROwID來標識相鄰索引記錄在表塊中是否為相同塊。具體來說，計算CF的算法如下：

1)聚簇因子的初始值為1。

2)Oracle首先定位到目標索引處于最左邊的葉子塊。

3)從最左邊的葉子塊的第一個索引鍵值所在的索引行開始順序掃描，在順序掃描的過程中，Oracle會比對當前索引行的ROWID和它之前的那個索引行(它們是相鄰的關(guān)系)的ROWID，如果這兩個ROWID并不是指向同一個表塊，那么Oracle就將聚簇因子的當前值遞增1；如果這兩個ROWID是指向同一個表塊，那么Oracle就不改變聚簇因子的當前值。注意，這里Oracle在比對ROWID的時候并不需要回表去訪問相應(yīng)的表塊。

4)上述比對ROWID的過程會一直持續(xù)下去，直到順序掃描完目標索引所有葉子塊里的所有索引行。

5)上述順序掃描操作完成后，聚簇因子的當前值就是索引統(tǒng)計信息中的CLUSTERING_FACTOR，Oracle會將其存儲在數(shù)據(jù)字典里。

好的CF值接近于表上的塊數(shù)，而差的CF值則接近于表上的行數(shù)。CF值越小，相似度越高，CF值越大，相似度越低。如果CF的值接近塊數(shù)，那么說明表的存儲和索引存儲排序接近，也就是說表中的記錄很有序，這樣在做INDEXRANGESCAN的時候，讀取少量的數(shù)據(jù)塊就能得到想要的數(shù)據(jù)，代價比較小。如果CF值接近表記錄數(shù)，那么說明表的存儲和索引排序差異很大，在做INDEXRANGESCAN的時候，由于表記錄分散，所以會額外讀取多個塊，代價較高。

CF值可以通過查詢視圖DBA_INDEXES中的CLUSTERJNG_FACTOR列來獲取。通過視圖DBA_INDEXES、DBA_OBJECTS和DBA_TABLES關(guān)聯(lián)可以得到索引的相關(guān)信息，包括當前索引的大小、行數(shù)、創(chuàng)建日期、索引高度和聚簇因子等信息。

由于聚簇因子高的索引走索引范圍掃描時比相同條件下聚簇因子低的索引要耗費更多的物理I/O，所以聚簇因子高的索引走索引范圍掃描的成本會比相同條件下聚簇因子低的索引走索引范圍掃描的成本高。Oracle選擇索引范圍掃描的成本可以近似看作是和聚簇因子成正比，因此，聚簇因子值的大小實際上對CBO判斷是否走相關(guān)的索引起著至關(guān)重要的作用。其實，聚簇因子決定著索引回表讀的開銷。在Oracle數(shù)據(jù)庫中，能夠降低目標索引的聚簇因子的唯一方法就是對表中數(shù)據(jù)按照目標索引的索引鍵值排序后重新存儲。需要注意的是，這種方法可能會同時增加該表上存在的其他索引的聚簇因子的值。

可以通過如下的命令顯式的設(shè)置聚簇因子的值：

6.

什么是索引的選擇性(IndexSelectivity)?正確答案：索引的選擇性(IndexSelectivity，索引的選擇度或索引的選擇率)是指索引列中不同值的記錄數(shù)與表中總記錄數(shù)的比值。索引的選擇性的取值范圍是[0,1]。例如，某個表的記錄數(shù)是1000條，而該表的索引列的值只有900個不同的值(有100個是相同或是空)，所以，該列索引的選擇性為900/1000=0.9。對于索引的選擇性，值越高那么表示該列索引的效率也就越高?？梢允褂萌缦碌腟QL來計算索引的選擇性：

這種方法的優(yōu)點是在創(chuàng)建索引前就能評估索引的選擇性。當索引被收集了最新的統(tǒng)計信息時，可以使用如下的SQL語句查詢索引的選擇性：

毋庸置疑，主鍵的選擇性為1。選擇性越接近1，那么該索引就越好。

7.

哪幾種情況不能使用索引?正確答案：“為什么索引沒有被使用”是一個涉及面較廣的問題。有多種原因會導致索引不能被使用。首要的原因就是統(tǒng)計信息不準，第二原因就是索引的選擇度不高，使用索引比使用全表掃描效率更差。還有一個比較常見的原因，就是對索引列進行了函數(shù)、算術(shù)運算或其他表達式等操作，或出現(xiàn)隱式類型轉(zhuǎn)換，導致無法使用索引。還有很多其他原因會導致不能使用索引，這個問題在MOS(MOS即MyOracleSupport)“文檔1549181.1為何在查詢中索引未被使用”中有非常詳細的解釋。主要原因如圖所示。

不能使用索引的主要原因

下表詳細介紹了這些檢查項目：

建議讀者對每種類型的情況都做實驗，以便加深理解和印象。

8.

什么是索引分裂?索引分裂有哪幾種類型?正確答案：索引分裂(IndexBlockSplit)就是索引塊的分裂。當一次DML操作修改了索引塊上的數(shù)據(jù)，但是舊有的索引塊沒有足夠的空間去容納新修改的數(shù)據(jù)時，將分裂出一個新的索引塊，舊有塊的部分數(shù)據(jù)放到新開辟的索引塊上，這個過程就稱為索引塊的分裂，簡稱索引分裂。

在分裂的過程中前臺進程需要等待分裂完成之后才能繼續(xù)操作。如果此時其他會話也要修改這個索引塊的數(shù)據(jù)，那么將會出現(xiàn)索引塊的競爭，等待以“enq:TX-indexcontention”的形式體現(xiàn)，該事件是一個與索引分裂直接相關(guān)的等待事件。一般索引塊的分裂持有資源和釋放非常迅速，并不會對數(shù)據(jù)庫造成嚴重的性能影響，但是對表操作并發(fā)量很大的情況下可能導致嚴重的競爭。當索引分裂發(fā)生時，負責實施分裂的進程會持有相關(guān)的隊列鎖，直到該進程完成分裂操作才會釋放該隊列鎖。在這個過程中負責分裂的進程需要找到合適的新塊并將對應(yīng)的數(shù)據(jù)移動到該新塊中。若在此分裂的過程中，有其他進程INSERT數(shù)據(jù)到該索引塊中，則將進入enq:TX-indexcontention等待，直到分裂結(jié)束鎖被釋放。

索引分裂有如下幾種情況：

(1)按照分裂對象分

1)葉子節(jié)點分裂：葉子節(jié)點上沒有足夠的空間容納新插入的數(shù)據(jù)。葉子節(jié)點分裂的情況最頻繁發(fā)生，對性能影響最直接。

2)枝節(jié)點分裂：其下層的節(jié)點分裂，會導致在該節(jié)點上增加一條記錄指向新加的節(jié)點，當該節(jié)點空間不足時，會發(fā)生分裂。

3)根節(jié)點分裂：特殊的枝節(jié)點分裂，分裂需要兩個新的數(shù)據(jù)塊，將原有數(shù)據(jù)轉(zhuǎn)移至兩個新節(jié)點，原有節(jié)點上生成兩條記錄分別指向新增的數(shù)據(jù)塊。

(2)按照分裂數(shù)據(jù)塊比例分

1)9-1分裂：當事務(wù)向索引的最右側(cè)的葉節(jié)點上插入一條大于或等于現(xiàn)有索引塊上最大值的數(shù)據(jù)，且該索引塊上不存在其他未提交的事務(wù)，如果沒有足夠的空間，那么就會發(fā)生9-1分裂。絕大部分數(shù)據(jù)還保留在舊有節(jié)點上，僅有非常少的一部分數(shù)據(jù)遷移到新節(jié)點上。

2)5-5分裂：當發(fā)生5-5分裂時，有一半索引記錄仍存在當前塊，而另一半數(shù)據(jù)移動到新的節(jié)點中，舊節(jié)點和新節(jié)點上的數(shù)據(jù)比例幾乎是持平的。5-5分裂發(fā)生的條件：

①當左側(cè)節(jié)點發(fā)生新值插入時(插入到葉子節(jié)點中的索引鍵值小于該塊中的最大值)。

②當發(fā)生DML操作時，索引塊上沒有足夠空間分配新的ITL槽。

③當新插入數(shù)據(jù)大于或等于索引中最大值時，但是數(shù)據(jù)塊上還存在其他未提交的事務(wù)。

對性能來說，無論是9-1分裂，還是5-5分裂，都會影響系統(tǒng)的性能。通過10224事件可以生成索引塊分裂及刪除的trace：

索引分裂常常發(fā)生在主鍵列上，在這種情況下，可以考慮將該主鍵修改為反轉(zhuǎn)(REVERSE)類型的主鍵。若主鍵是通過序列、時間戳或按某種規(guī)則單調(diào)生成的主鍵，則可以使用反轉(zhuǎn)索引來有效地降低索引“單向右增長”(Right-GrowingIndex)的可能性。語句如下：

在發(fā)生索引分裂等待的時候，也可以根據(jù)需要將索引改造為分區(qū)索引。通過HASH將索引分成一個一個小塊，這樣競爭就不會聚集在最右邊的節(jié)點上。通過HASH分區(qū)索引在一定程度上就可以緩解插入數(shù)據(jù)的競爭問題。

9.

ISNULL如何用到索引?正確答案：ISNULL用于判斷某一列中的值是否為空。當ISNULL作為WHERE條件的時候，該列是不會用到索引的，但是可以加偽列創(chuàng)建偽聯(lián)合索引來使得ISNULL使用索引，如下：

其中，OBJ_ID和-1組成聯(lián)合索引，當執(zhí)行類似如下SQL語句時即可用到索引I_TB_OBJ_ID：

10.

索引是否必須定期重建?索引重建有哪些影響?正確答案：一般而言，極少需要重建B樹索引，基本原因是B樹索引很大程度上可以自我管理或自我平衡。認為需要重建索引的最常見理由有：

1)B-Tree索引隨著時間的推移變得不平衡(錯誤的認識)。

2)索引碎片在不斷增加，但是這些碎片會被重用。

3)索引不斷增加，刪除的空間沒有重復使用(錯誤的認識)。

4)索引聚簇因子(ClusteringFactor)不同步，可以通過重建修復(錯誤的認識)。

事實上，由于空閑的索引葉條目可以重復使用，所以大多數(shù)索引都能保持平衡和完整，插入、更新和刪除操作確實會導致索引塊周圍的可用空間形成碎片，但是一般來說這些碎片都會被正確的重用。聚簇因子可以反映給定的索引鍵值所對應(yīng)的表中的數(shù)據(jù)排序情況。重建索引不會對聚簇因子產(chǎn)生影響，要改變聚簇因子只能通過重組表的數(shù)據(jù)。

若是重建索引，則建議對以下的索引進行重建：

1)在分析(ANALYZE)指定索引之后，查詢INDEX_STATS的HEIGHT字段的值，如果HEIGHT＞=4即索引深度超過3級，那么最好重建(REBUILD)這個索引，但是如果這個值一直保持不變，那么這個索引也就不需要重建。

2)在分析(ANALYZE)指定索引之后，查詢INDEX_STATS的DEL_LF_ROWS和LF_ROWS的值，如果(DEL_LF_ROWS/LF_ROWS)*100＞=20即已刪除的索引條目至少占有現(xiàn)有索引條目總數(shù)的20%，那么表示這個索引也需要重建。

重建索引的影響非常明顯，主要有以下幾點：

1)大多數(shù)腳本都依賴INDEX_STATS動態(tài)表，此表使用以下命令填充：

盡管這是一種有效的