Oracle數(shù)據(jù)庫(kù)索引優(yōu)化試題及答案

Oracle數(shù)據(jù)庫(kù)索引優(yōu)化試題及答案考試時(shí)間：______分鐘總分：______分姓名：______一、選擇題（請(qǐng)將正確選項(xiàng)的字母填入括號(hào)內(nèi)）1.在Oracle數(shù)據(jù)庫(kù)中，B-Tree索引適用于哪種類型的查詢操作？A.范圍查詢B.等值查詢C.列表查詢D.以上所有E.以上都不是2.以下哪個(gè)命令用于收集表和索引的統(tǒng)計(jì)信息？A.`ALTERINDEX...REBUILD`B.`DBMS_REINDEX`C.`ANALYZETABLE...COMPUTESTATISTICS`D.`DBMS_STATS.GATHER_TABLE_STATS`E.`EXPLAINPLANFOR`3.當(dāng)SQL語(yǔ)句執(zhí)行計(jì)劃中顯示“TableAccessFull”時(shí)，通常意味著什么？A.索引被成功使用B.執(zhí)行了全表掃描C.執(zhí)行了索引掃描D.執(zhí)行了反向鍵索引掃描E.數(shù)據(jù)庫(kù)參數(shù)設(shè)置錯(cuò)誤4.創(chuàng)建復(fù)合索引時(shí)，索引列的物理順序?qū)δ男╊愋偷牟樵儍?yōu)化至關(guān)重要？A.覆蓋索引查詢B.使用索引過(guò)濾的查詢C.使用WHERE子句中不同索引列的查詢D.以上所有E.以上都不是5.以下哪種索引類型最適合用于加速基于特定函數(shù)計(jì)算結(jié)果的查詢？A.哈希索引B.反向鍵索引C.函數(shù)索引D.范圍索引E.列表索引6.在執(zhí)行計(jì)劃中，“Index(Unique)”與“Index(Non-Unique)”的主要區(qū)別是什么？A.“Index(Unique)”索引的B-Tree根節(jié)點(diǎn)是惟一的B.“Index(Unique)”會(huì)檢查重復(fù)鍵值，而“Index(Non-Unique)”不會(huì)C.“Index(Unique)”通常比“Index(Non-Unique)”更慢D.“Index(Unique)”只能用于主鍵E.兩者沒(méi)有區(qū)別7.以下哪種情況是創(chuàng)建分區(qū)索引的主要優(yōu)勢(shì)之一？A.顯著減少索引大小B.簡(jiǎn)化單個(gè)索引的維護(hù)操作C.提高跨多個(gè)邏輯段的查詢性能D.以上所有E.以上都不是8.當(dāng)一個(gè)索引變得碎片化時(shí)，可能導(dǎo)致的性能問(wèn)題是？A.索引讀取命中率下降B.索引維護(hù)操作（如INSERT/UPDATE/DELETE）變慢C.數(shù)據(jù)庫(kù)的全表掃描次數(shù)增加D.以上所有E.以上都不是9.在不考慮函數(shù)索引的情況下，以下哪個(gè)SQL語(yǔ)句最有可能從索引`IX_CUSTOMER_ID`中獲益？（假設(shè)該索引包含`CUSTOMER_ID`列）A.`SELECT*FROMcustomersWHEREUPPER(CUSTOMER_ID)='C123'`B.`SELECT*FROMcustomersWHERECUSTOMER_ID='C123'`C.`SELECT*FROMcustomersWHEREINSTR(CUSTOMER_ID,'123')>0`D.`SELECT*FROMcustomersWHERECUSTOMER_IDLIKE'C123%'`E.`SELECT*FROMcustomersWHERECUSTOMER_IDRLIKE'C123'`10.以下哪種索引優(yōu)化策略可能導(dǎo)致查詢性能下降，而DML性能提升？A.刪除不再使用或低效的索引B.為高頻查詢創(chuàng)建必要的索引C.重建碎片化的索引D.為經(jīng)常進(jìn)行范圍查詢的列創(chuàng)建索引E.使用覆蓋索引減少表訪問(wèn)二、多選題（請(qǐng)將正確選項(xiàng)的字母填入括號(hào)內(nèi)）1.以下哪些情況通常建議創(chuàng)建索引？A.主鍵列B.經(jīng)常用于`JOIN`操作的列C.經(jīng)常用于`WHERE`子句進(jìn)行過(guò)濾的列D.經(jīng)常用于`ORDERBY`或`GROUPBY`子句的列E.經(jīng)常參與計(jì)算函數(shù)的列（除非創(chuàng)建函數(shù)索引）2.影響索引選擇和優(yōu)化的統(tǒng)計(jì)信息包括哪些？A.列的密度（Cardinality）B.列的數(shù)據(jù)分布（如空值比例）C.表的大小（行數(shù)）D.列的數(shù)據(jù)類型E.索引的使用頻率3.以下哪些操作可能導(dǎo)致索引碎片化？A.大量的INSERT操作B.大量的UPDATE操作（尤其修改索引列值）C.大量的DELETE操作D.索引的重建（Rebuild）E.索引的重新組織（Reorganize）4.以下哪些是復(fù)合索引的“最左前綴原則”（LeftmostPrefixRule）的含義？A.只有在查詢條件使用了復(fù)合索引的最左邊的列時(shí)，索引才會(huì)被使用B.只有在查詢條件使用了復(fù)合索引的最左邊一列或連續(xù)最左邊的多列時(shí)，索引才會(huì)被使用C.索引中列的順序?qū)λ饕氖褂脹](méi)有影響D.復(fù)合索引只能包含兩個(gè)列E.可以通過(guò)在查詢中使用函數(shù)來(lái)改變復(fù)合索引的使用方式5.以下哪些是分區(qū)索引的優(yōu)點(diǎn)？A.可以獨(dú)立地重建或刪除某個(gè)分區(qū)上的索引B.可以根據(jù)分區(qū)進(jìn)行數(shù)據(jù)備份和恢復(fù)，減少窗口期C.可以提高涉及分區(qū)鍵范圍查詢的索引性能D.分區(qū)索引的管理比非分區(qū)索引更復(fù)雜E.分區(qū)索引會(huì)顯著增加表的存儲(chǔ)空間需求6.以下哪些情況可能導(dǎo)致數(shù)據(jù)庫(kù)選擇全表掃描而不是使用索引？A.索引列包含大量空值（NULLs）B.查詢條件涉及的索引列比例（Selectivity）非常低，返回大部分行C.索引統(tǒng)計(jì)信息過(guò)時(shí)，導(dǎo)致查詢優(yōu)化器做出錯(cuò)誤選擇D.使用了函數(shù)運(yùn)算的索引列，且未創(chuàng)建函數(shù)索引E.表數(shù)據(jù)量相對(duì)于可用內(nèi)存來(lái)說(shuō)非常小7.關(guān)于唯一索引（UniqueIndex），以下說(shuō)法哪些正確？A.唯一索引保證了索引列（或列組合）值的惟一性，可以包含一個(gè)或多個(gè)列B.唯一索引會(huì)為每個(gè)不同的鍵值對(duì)存儲(chǔ)額外的行來(lái)記錄是否存在重復(fù)值C.唯一索引會(huì)阻止在索引列上插入重復(fù)值D.唯一索引在插入數(shù)據(jù)時(shí)會(huì)比非唯一索引稍微慢一些E.唯一索引可以與主鍵約束同時(shí)存在8.分析SQL語(yǔ)句執(zhí)行計(jì)劃時(shí)，可以通過(guò)觀察哪些信息來(lái)判斷索引是否被有效利用？A.估計(jì)的行數(shù)（EstimateofRows）B.實(shí)際的行數(shù)（ActualRows）C.“IndexName”或“Index(Unique)”等標(biāo)識(shí)符D.“Filter”列顯示的過(guò)濾條件是否與索引列相關(guān)E.“Rows”列的值三、填空題（請(qǐng)將答案填入橫線上）1.Oracle數(shù)據(jù)庫(kù)中，默認(rèn)的索引類型是_______索引。2.為了確保索引維護(hù)與表維護(hù)的隔離性，可以使用_______語(yǔ)句來(lái)在線重建或重新組織索引。3.使用`DBMS_XPLAN.DISPLAY`視圖或`EXPLAINPLANFOR`命令查看SQL執(zhí)行計(jì)劃，有助于分析_______的選擇和索引的使用情況。4.對(duì)于頻繁進(jìn)行范圍查詢的列，創(chuàng)建_______索引通常是有效的策略。5.當(dāng)統(tǒng)計(jì)信息表明某個(gè)索引的選擇性（Cardinality）非常低（接近0或1）時(shí)，即使該索引存在，查詢優(yōu)化器也可能選擇_______掃描。6.如果一個(gè)索引只被查詢操作使用，而沒(méi)有DML操作依賴它，并且確認(rèn)該索引不再需要，可以考慮_______該索引以節(jié)省空間和減少維護(hù)開(kāi)銷。7.在創(chuàng)建復(fù)合索引時(shí)，應(yīng)將最常用于_______操作的列放在索引的最前面。8.反向鍵索引（ReverseKeyIndex）通常用于索引哪些類型的數(shù)據(jù)，以避免_______問(wèn)題導(dǎo)致的索引失效或性能下降？四、簡(jiǎn)答題1.簡(jiǎn)述在Oracle數(shù)據(jù)庫(kù)中，創(chuàng)建索引可能帶來(lái)的好處和潛在的性能開(kāi)銷。2.描述如何判斷一個(gè)索引是否碎片化，并簡(jiǎn)述常見(jiàn)的索引維護(hù)（重建或重新組織）方法及其區(qū)別。3.解釋什么是復(fù)合索引的“最左前綴原則”，并舉例說(shuō)明其重要性。4.當(dāng)發(fā)現(xiàn)一個(gè)復(fù)雜的SQL語(yǔ)句執(zhí)行效率低下，且執(zhí)行計(jì)劃顯示未使用預(yù)期的索引時(shí)，你會(huì)采取哪些步驟來(lái)診斷和優(yōu)化這個(gè)問(wèn)題？5.比較并說(shuō)明B-Tree索引和Hash索引的主要區(qū)別、適用場(chǎng)景和局限性。五、場(chǎng)景分析題假設(shè)你是一個(gè)數(shù)據(jù)庫(kù)管理員，負(fù)責(zé)一個(gè)名為`SALES`的表，該表有百萬(wàn)級(jí)別的行數(shù)，包含以下列：`SALES_ID`(NUMBER,PRIMARYKEY),`ORDER_ID`(NUMBER),`CUSTOMER_ID`(VARCHAR2(20)),`SALESperson_ID`(VARCHAR2(10)),`SALES_DATE`(DATE),`AMOUNT`(NUMBER)你觀察到以下兩類SQL查詢非常頻繁：*查找特定銷售人員（`SALESperson_ID`）在特定時(shí)間段內(nèi)（`SALES_DATEBETWEEN'2023-01-01'AND'2023-12-31'`）的所有銷售記錄。*根據(jù)客戶ID（`CUSTOMER_ID`）查詢?cè)摽蛻舻目備N售額。目前，表上只建立了主鍵索引`PK_SALES_ID`和一個(gè)組合索引`IX_CUSTOMER_ID_AMOUNT`。請(qǐng)分析現(xiàn)有索引可能存在的問(wèn)題，并提出具體的索引優(yōu)化建議，說(shuō)明你創(chuàng)建的每個(gè)索引的理由。試卷答案一、選擇題1.D解析：B-Tree索引能高效處理范圍查詢、等值查詢和列表查詢。2.D解析：`DBMS_STATS.GATHER_TABLE_STATS`是PL/SQL包中用于收集表和索引統(tǒng)計(jì)信息的常用命令。3.B解析：“TableAccessFull”表示執(zhí)行了全表掃描，沒(méi)有使用索引。4.D解析：復(fù)合索引的效率依賴于查詢條件是否匹配索引的最左前綴或連續(xù)列。5.C解析：函數(shù)索引存儲(chǔ)了函數(shù)計(jì)算結(jié)果的值，直接用于加速包含該函數(shù)的查詢。6.B解析：“Index(Unique)”會(huì)檢查鍵值是否重復(fù)，而“Index(Non-Unique)”允許重復(fù)鍵值存在。7.D解析：分區(qū)索引在管理、維護(hù)和性能上都有優(yōu)勢(shì)，是多種場(chǎng)景下的優(yōu)選方案。8.D解析：碎片化會(huì)導(dǎo)致索引讀取效率下降、維護(hù)操作變慢，并可能間接導(dǎo)致全表掃描增多。9.B解析：選項(xiàng)B直接匹配索引列，符合索引使用條件。選項(xiàng)A因?yàn)楹瘮?shù)運(yùn)算而無(wú)法直接使用該索引。選項(xiàng)C、D、E涉及函數(shù)或部分匹配，通常不能直接使用該索引。10.E解析：覆蓋索引減少了表訪問(wèn)，但維護(hù)索引本身有開(kāi)銷，可能不適用于所有場(chǎng)景。二、多選題1.A,B,C,D解析：主鍵、常用于JOIN、WHERE、ORDERBY的列通常是創(chuàng)建索引的良好候選。2.A,B,C解析：列的基數(shù)、數(shù)據(jù)分布和表大小是查詢優(yōu)化器計(jì)算成本和選擇操作的重要依據(jù)。數(shù)據(jù)類型和頻率影響較小。3.A,B,C,E解析：INSERT、UPDATE（修改索引列）、DELETE都會(huì)導(dǎo)致數(shù)據(jù)變更和可能的碎片。重建（Rebuild）和重新組織（Reorganize）是修復(fù)碎片化的操作，不是導(dǎo)致碎片化的原因。4.B解析：最左前綴原則指查詢必須使用復(fù)合索引最左邊的列，或從最左邊開(kāi)始的連續(xù)列組合。5.A,B,C解析：分區(qū)索引允許獨(dú)立管理分區(qū)、簡(jiǎn)化備份恢復(fù)、提高范圍查詢性能。選項(xiàng)D和E描述的是其缺點(diǎn)或一般情況。6.A,B,C,D解析：高空值比例、低選擇性、過(guò)時(shí)統(tǒng)計(jì)信息、函數(shù)運(yùn)算都會(huì)導(dǎo)致優(yōu)化器選擇全表掃描。7.A,C,D,E解析：唯一索引保證惟一性、阻止重復(fù)值、比非唯一索引稍慢（因?yàn)樾枰獧z查重復(fù)）、可與主鍵共存。選項(xiàng)B描述的是內(nèi)部實(shí)現(xiàn)，非關(guān)鍵特性。8.A,C,D,E解析：可以通過(guò)估計(jì)/實(shí)際行數(shù)、索引標(biāo)識(shí)符、過(guò)濾條件、行數(shù)信息來(lái)判斷索引使用情況和執(zhí)行效率。三、填空題1.B-Tree解析：Oracle默認(rèn)為普通索引創(chuàng)建B-Tree結(jié)構(gòu)。2.ONLINE解析：`ALTERINDEX...ONLINEREBUILD/REORG`允許在索引重建/重組期間，表的其他操作可以繼續(xù)進(jìn)行。3.查詢優(yōu)化器解析：執(zhí)行計(jì)劃顯示了查詢優(yōu)化器選擇的操作路徑，包括是否使用索引。4.范圍索引解析：B-Tree索引天然支持范圍查詢。5.全表掃描解析：當(dāng)選擇性極低時(shí)，即使有索引，優(yōu)化器可能認(rèn)為全表掃描更優(yōu)。6.刪除解析：刪除無(wú)用索引可以釋放空間，減少維護(hù)負(fù)擔(dān)。7.過(guò)濾（或WHERE）解析：索引最左邊的列（或列組合）通常用于過(guò)濾數(shù)據(jù)，最先滿足查詢條件。8.高基數(shù)字段（或高基數(shù)字段）/重復(fù)鍵值解析：反向鍵索引適用于基數(shù)高的列（如序列），避免常規(guī)B-Tree索引在高基數(shù)下因節(jié)點(diǎn)分裂導(dǎo)致過(guò)多層級(jí)或頻繁失效的問(wèn)題。四、簡(jiǎn)答題1.索引好處：*顯著提高符合索引條件的查詢速度。*加速涉及`JOIN`、`WHERE`、`ORDERBY`、`GROUPBY`子句的SQL語(yǔ)句執(zhí)行。*通過(guò)主鍵或唯一索引保證數(shù)據(jù)的惟一性。*加速外鍵約束的檢查，保證參照完整性。*加速DML操作中的某些操作（如快速查找要更新的行）。索引開(kāi)銷：*占用額外的存儲(chǔ)空間。*每次INSERT、UPDATE、DELETE操作時(shí)需要維護(hù)索引，降低DML性能。*索引維護(hù)（如重建/重新組織）需要時(shí)間和資源。*過(guò)度索引會(huì)增加管理復(fù)雜度和維護(hù)成本。解析思路：分析索引對(duì)查詢（加速）和數(shù)據(jù)修改（可能減速、增加空間）兩方面的影響。2.判斷碎片化：*通過(guò)執(zhí)行計(jì)劃（ExplainPlan）觀察索引操作的I/O成本是否異常高。*使用DBA_INDEXES視圖的`UNUSABLE`或`DEFRAGged`狀態(tài)（不常用或已標(biāo)記）。*使用DBMS_SQLTUNE包或ADDM報(bào)告分析索引效率。*估算與實(shí)際讀取I/O對(duì)比（手動(dòng)計(jì)算或工具輔助）。維護(hù)方法：*`ALTERINDEX...REBUILD`：完全重建索引，速度最快，占用最長(zhǎng)時(shí)間，需要更多空間，期間索引不可用（除非ONLINE選項(xiàng)）。*`ALTERINDEX...REORG`：原地重新組織索引結(jié)構(gòu)，速度較快，空間開(kāi)銷小，期間索引可用（ONLINE選項(xiàng)）。區(qū)別：REBUILD徹底新生成索引，REORG主要調(diào)整存儲(chǔ)結(jié)構(gòu)，修復(fù)局部碎片。解析思路：描述檢測(cè)碎片化的方法，然后對(duì)比兩種主要維護(hù)方法的操作、優(yōu)缺點(diǎn)和影響。3.最左前綴原則：對(duì)于復(fù)合索引（如`IXCol1Col2Col3`），查詢優(yōu)化器只有在其WHERE子句條件明確使用`Col1`（或`Col1`和`Col2`的組合，或`Col1`、`Col2`和`Col3`的組合），且列的順序與索引一致時(shí)，才會(huì)考慮使用該索引。如果只使用`Col3`，即使`Col3`在索引中，該索引也不會(huì)被使用。重要性：決定了復(fù)合索引能被利用的范圍。不合理的設(shè)計(jì)（如將不常用于查詢的列放在前面）會(huì)浪費(fèi)索引資源。示例：索引`IXCustomerIDOrderDate`，查詢`WHERECustomerID='C100'`會(huì)使用索引，但查詢`WHEREOrderDate='2023-10-27'`不會(huì)使用此索引。解析思路：解釋原則內(nèi)容，說(shuō)明其作用（決定索引可用性），并通過(guò)例子具體化。4.診斷步驟：*分析慢查詢?nèi)罩净蚴褂胉AUTOTRACE`/`EXPLAINPLANFOR`/`DBMS_XPLAN.DISPLAY`獲取當(dāng)前執(zhí)行計(jì)劃。*檢查執(zhí)行計(jì)劃中是否顯示了預(yù)期的索引使用，或是否存在“TableAccessFull”等低效操作。*檢查索引統(tǒng)計(jì)信息是否過(guò)時(shí)（使用`DBA_INDEX_STATS`或`DBMS_STATS.GATHER_TABLE_STATS`）。*分析SQL語(yǔ)句本身是否存在語(yǔ)法問(wèn)題或可以改寫(xiě)的空間（如減少函數(shù)運(yùn)算、優(yōu)化JOIN順序等）。*檢查表和索引的存儲(chǔ)參數(shù)（如塊大小）是否合適。優(yōu)化建議：*根據(jù)分析結(jié)果，創(chuàng)建缺失的、選擇合適的復(fù)合索引（考慮列順序）。*重建或重新組織碎片化的索引。*修改或重寫(xiě)SQL語(yǔ)句，使其能更好地利用現(xiàn)有索引。*調(diào)整數(shù)據(jù)庫(kù)參數(shù)或硬件資源。解析思路：按照診斷-分析-建議的邏輯流程，列出具體操作步驟和方法。5.B-Tree索引：*特點(diǎn)：基于平衡樹(shù)結(jié)構(gòu)，支持范圍查詢和等值查詢。*適用場(chǎng)景：最常用的索引類型，適用于大多數(shù)場(chǎng)景，如主鍵、外鍵、經(jīng)常用于`=`、`>`、`<`、`BETWEEN`、`LIKE'prefix%'`的查詢。*局限性：在哈希分布均勻的數(shù)據(jù)上，查找等值操作的平均時(shí)間復(fù)雜度可能不如Hash索引。Hash索引：*特點(diǎn)：基于哈希表結(jié)構(gòu)，通過(guò)計(jì)算鍵值的哈希碼直接定位數(shù)據(jù)塊。只支持等值查詢（`=`）。*適用場(chǎng)景：適用于只進(jìn)行精確等值匹配（`=`）查詢的場(chǎng)景，且數(shù)據(jù)分布比較均勻。*局限性：不支持范圍查詢，對(duì)等值查詢的效率依賴于數(shù)據(jù)分布的均勻性，不適用于有大量NULL值的列。解析思路：分別對(duì)比B-Tree和Hash索引的結(jié)構(gòu)、支持的操作類型、主要優(yōu)點(diǎn)和缺點(diǎn)，以及各自適合的應(yīng)用場(chǎng)景。五、場(chǎng)景分析題現(xiàn)有索引問(wèn)題：*`PK_SALES_ID`是主鍵索引，有效。*`IX_C