Oracle数据库索引优化试题及答案

Oracle数据库索引优化试题及答案考试时间：______分钟总分：______分姓名：______一、选择题（请将正确选项的字母填入括号内）1.在Oracle数据库中，B-Tree索引适用于哪种类型的查询操作？A.范围查询B.等值查询C.列表查询D.以上所有E.以上都不是2.以下哪个命令用于收集表和索引的统计信息？A.`ALTERINDEX...REBUILD`B.`DBMS_REINDEX`C.`ANALYZETABLE...COMPUTESTATISTICS`D.`DBMS_STATS.GATHER_TABLE_STATS`E.`EXPLAINPLANFOR`3.当SQL语句执行计划中显示“TableAccessFull”时，通常意味着什么？A.索引被成功使用B.执行了全表扫描C.执行了索引扫描D.执行了反向键索引扫描E.数据库参数设置错误4.创建复合索引时，索引列的物理顺序对哪些类型的查询优化至关重要？A.覆盖索引查询B.使用索引过滤的查询C.使用WHERE子句中不同索引列的查询D.以上所有E.以上都不是5.以下哪种索引类型最适合用于加速基于特定函数计算结果的查询？A.哈希索引B.反向键索引C.函数索引D.范围索引E.列表索引6.在执行计划中，“Index(Unique)”与“Index(Non-Unique)”的主要区别是什么？A.“Index(Unique)”索引的B-Tree根节点是惟一的B.“Index(Unique)”会检查重复键值，而“Index(Non-Unique)”不会C.“Index(Unique)”通常比“Index(Non-Unique)”更慢D.“Index(Unique)”只能用于主键E.两者没有区别7.以下哪种情况是创建分区索引的主要优势之一？A.显著减少索引大小B.简化单个索引的维护操作C.提高跨多个逻辑段的查询性能D.以上所有E.以上都不是8.当一个索引变得碎片化时，可能导致的性能问题是？A.索引读取命中率下降B.索引维护操作（如INSERT/UPDATE/DELETE）变慢C.数据库的全表扫描次数增加D.以上所有E.以上都不是9.在不考虑函数索引的情况下，以下哪个SQL语句最有可能从索引`IX_CUSTOMER_ID`中获益？（假设该索引包含`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.以下哪种索引优化策略可能导致查询性能下降，而DML性能提升？A.删除不再使用或低效的索引B.为高频查询创建必要的索引C.重建碎片化的索引D.为经常进行范围查询的列创建索引E.使用覆盖索引减少表访问二、多选题（请将正确选项的字母填入括号内）1.以下哪些情况通常建议创建索引？A.主键列B.经常用于`JOIN`操作的列C.经常用于`WHERE`子句进行过滤的列D.经常用于`ORDERBY`或`GROUPBY`子句的列E.经常参与计算函数的列（除非创建函数索引）2.影响索引选择和优化的统计信息包括哪些？A.列的密度（Cardinality）B.列的数据分布（如空值比例）C.表的大小（行数）D.列的数据类型E.索引的使用频率3.以下哪些操作可能导致索引碎片化？A.大量的INSERT操作B.大量的UPDATE操作（尤其修改索引列值）C.大量的DELETE操作D.索引的重建（Rebuild）E.索引的重新组织（Reorganize）4.以下哪些是复合索引的“最左前缀原则”（LeftmostPrefixRule）的含义？A.只有在查询条件使用了复合索引的最左边的列时，索引才会被使用B.只有在查询条件使用了复合索引的最左边一列或连续最左边的多列时，索引才会被使用C.索引中列的顺序对索引的使用没有影响D.复合索引只能包含两个列E.可以通过在查询中使用函数来改变复合索引的使用方式5.以下哪些是分区索引的优点？A.可以独立地重建或删除某个分区上的索引B.可以根据分区进行数据备份和恢复，减少窗口期C.可以提高涉及分区键范围查询的索引性能D.分区索引的管理比非分区索引更复杂E.分区索引会显著增加表的存储空间需求6.以下哪些情况可能导致数据库选择全表扫描而不是使用索引？A.索引列包含大量空值（NULLs）B.查询条件涉及的索引列比例（Selectivity）非常低，返回大部分行C.索引统计信息过时，导致查询优化器做出错误选择D.使用了函数运算的索引列，且未创建函数索引E.表数据量相对于可用内存来说非常小7.关于唯一索引（UniqueIndex），以下说法哪些正确？A.唯一索引保证了索引列（或列组合）值的惟一性，可以包含一个或多个列B.唯一索引会为每个不同的键值对存储额外的行来记录是否存在重复值C.唯一索引会阻止在索引列上插入重复值D.唯一索引在插入数据时会比非唯一索引稍微慢一些E.唯一索引可以与主键约束同时存在8.分析SQL语句执行计划时，可以通过观察哪些信息来判断索引是否被有效利用？A.估计的行数（EstimateofRows）B.实际的行数（ActualRows）C.“IndexName”或“Index(Unique)”等标识符D.“Filter”列显示的过滤条件是否与索引列相关E.“Rows”列的值三、填空题（请将答案填入横线上）1.Oracle数据库中，默认的索引类型是_______索引。2.为了确保索引维护与表维护的隔离性，可以使用_______语句来在线重建或重新组织索引。3.使用`DBMS_XPLAN.DISPLAY`视图或`EXPLAINPLANFOR`命令查看SQL执行计划，有助于分析_______的选择和索引的使用情况。4.对于频繁进行范围查询的列，创建_______索引通常是有效的策略。5.当统计信息表明某个索引的选择性（Cardinality）非常低（接近0或1）时，即使该索引存在，查询优化器也可能选择_______扫描。6.如果一个索引只被查询操作使用，而没有DML操作依赖它，并且确认该索引不再需要，可以考虑_______该索引以节省空间和减少维护开销。7.在创建复合索引时，应将最常用于_______操作的列放在索引的最前面。8.反向键索引（ReverseKeyIndex）通常用于索引哪些类型的数据，以避免_______问题导致的索引失效或性能下降？四、简答题1.简述在Oracle数据库中，创建索引可能带来的好处和潜在的性能开销。2.描述如何判断一个索引是否碎片化，并简述常见的索引维护（重建或重新组织）方法及其区别。3.解释什么是复合索引的“最左前缀原则”，并举例说明其重要性。4.当发现一个复杂的SQL语句执行效率低下，且执行计划显示未使用预期的索引时，你会采取哪些步骤来诊断和优化这个问题？5.比较并说明B-Tree索引和Hash索引的主要区别、适用场景和局限性。五、场景分析题假设你是一个数据库管理员，负责一个名为`SALES`的表，该表有百万级别的行数，包含以下列：`SALES_ID`(NUMBER,PRIMARYKEY),`ORDER_ID`(NUMBER),`CUSTOMER_ID`(VARCHAR2(20)),`SALESperson_ID`(VARCHAR2(10)),`SALES_DATE`(DATE),`AMOUNT`(NUMBER)你观察到以下两类SQL查询非常频繁：*查找特定销售人员（`SALESperson_ID`）在特定时间段内（`SALES_DATEBETWEEN'2023-01-01'AND'2023-12-31'`）的所有销售记录。*根据客户ID（`CUSTOMER_ID`）查询该客户的总销售额。目前，表上只建立了主键索引`PK_SALES_ID`和一个组合索引`IX_CUSTOMER_ID_AMOUNT`。请分析现有索引可能存在的问题，并提出具体的索引优化建议，说明你创建的每个索引的理由。试卷答案一、选择题1.D解析：B-Tree索引能高效处理范围查询、等值查询和列表查询。2.D解析：`DBMS_STATS.GATHER_TABLE_STATS`是PL/SQL包中用于收集表和索引统计信息的常用命令。3.B解析：“TableAccessFull”表示执行了全表扫描，没有使用索引。4.D解析：复合索引的效率依赖于查询条件是否匹配索引的最左前缀或连续列。5.C解析：函数索引存储了函数计算结果的值，直接用于加速包含该函数的查询。6.B解析：“Index(Unique)”会检查键值是否重复，而“Index(Non-Unique)”允许重复键值存在。7.D解析：分区索引在管理、维护和性能上都有优势，是多种场景下的优选方案。8.D解析：碎片化会导致索引读取效率下降、维护操作变慢，并可能间接导致全表扫描增多。9.B解析：选项B直接匹配索引列，符合索引使用条件。选项A因为函数运算而无法直接使用该索引。选项C、D、E涉及函数或部分匹配，通常不能直接使用该索引。10.E解析：覆盖索引减少了表访问，但维护索引本身有开销，可能不适用于所有场景。二、多选题1.A,B,C,D解析：主键、常用于JOIN、WHERE、ORDERBY的列通常是创建索引的良好候选。2.A,B,C解析：列的基数、数据分布和表大小是查询优化器计算成本和选择操作的重要依据。数据类型和频率影响较小。3.A,B,C,E解析：INSERT、UPDATE（修改索引列）、DELETE都会导致数据变更和可能的碎片。重建（Rebuild）和重新组织（Reorganize）是修复碎片化的操作，不是导致碎片化的原因。4.B解析：最左前缀原则指查询必须使用复合索引最左边的列，或从最左边开始的连续列组合。5.A,B,C解析：分区索引允许独立管理分区、简化备份恢复、提高范围查询性能。选项D和E描述的是其缺点或一般情况。6.A,B,C,D解析：高空值比例、低选择性、过时统计信息、函数运算都会导致优化器选择全表扫描。7.A,C,D,E解析：唯一索引保证惟一性、阻止重复值、比非唯一索引稍慢（因为需要检查重复）、可与主键共存。选项B描述的是内部实现，非关键特性。8.A,C,D,E解析：可以通过估计/实际行数、索引标识符、过滤条件、行数信息来判断索引使用情况和执行效率。三、填空题1.B-Tree解析：Oracle默认为普通索引创建B-Tree结构。2.ONLINE解析：`ALTERINDEX...ONLINEREBUILD/REORG`允许在索引重建/重组期间，表的其他操作可以继续进行。3.查询优化器解析：执行计划显示了查询优化器选择的操作路径，包括是否使用索引。4.范围索引解析：B-Tree索引天然支持范围查询。5.全表扫描解析：当选择性极低时，即使有索引，优化器可能认为全表扫描更优。6.删除解析：删除无用索引可以释放空间，减少维护负担。7.过滤（或WHERE）解析：索引最左边的列（或列组合）通常用于过滤数据，最先满足查询条件。8.高基数字段（或高基数字段）/重复键值解析：反向键索引适用于基数高的列（如序列），避免常规B-Tree索引在高基数下因节点分裂导致过多层级或频繁失效的问题。四、简答题1.索引好处：*显著提高符合索引条件的查询速度。*加速涉及`JOIN`、`WHERE`、`ORDERBY`、`GROUPBY`子句的SQL语句执行。*通过主键或唯一索引保证数据的惟一性。*加速外键约束的检查，保证参照完整性。*加速DML操作中的某些操作（如快速查找要更新的行）。索引开销：*占用额外的存储空间。*每次INSERT、UPDATE、DELETE操作时需要维护索引，降低DML性能。*索引维护（如重建/重新组织）需要时间和资源。*过度索引会增加管理复杂度和维护成本。解析思路：分析索引对查询（加速）和数据修改（可能减速、增加空间）两方面的影响。2.判断碎片化：*通过执行计划（ExplainPlan）观察索引操作的I/O成本是否异常高。*使用DBA_INDEXES视图的`UNUSABLE`或`DEFRAGged`状态（不常用或已标记）。*使用DBMS_SQLTUNE包或ADDM报告分析索引效率。*估算与实际读取I/O对比（手动计算或工具辅助）。维护方法：*`ALTERINDEX...REBUILD`：完全重建索引，速度最快，占用最长时间，需要更多空间，期间索引不可用（除非ONLINE选项）。*`ALTERINDEX...REORG`：原地重新组织索引结构，速度较快，空间开销小，期间索引可用（ONLINE选项）。区别：REBUILD彻底新生成索引，REORG主要调整存储结构，修复局部碎片。解析思路：描述检测碎片化的方法，然后对比两种主要维护方法的操作、优缺点和影响。3.最左前缀原则：对于复合索引（如`IXCol1Col2Col3`），查询优化器只有在其WHERE子句条件明确使用`Col1`（或`Col1`和`Col2`的组合，或`Col1`、`Col2`和`Col3`的组合），且列的顺序与索引一致时，才会考虑使用该索引。如果只使用`Col3`，即使`Col3`在索引中，该索引也不会被使用。重要性：决定了复合索引能被利用的范围。不合理的设计（如将不常用于查询的列放在前面）会浪费索引资源。示例：索引`IXCustomerIDOrderDate`，查询`WHERECustomerID='C100'`会使用索引，但查询`WHEREOrderDate='2023-10-27'`不会使用此索引。解析思路：解释原则内容，说明其作用（决定索引可用性），并通过例子具体化。4.诊断步骤：*分析慢查询日志或使用`AUTOTRACE`/`EXPLAINPLANFOR`/`DBMS_XPLAN.DISPLAY`获取当前执行计划。*检查执行计划中是否显示了预期的索引使用，或是否存在“TableAccessFull”等低效操作。*检查索引统计信息是否过时（使用`DBA_INDEX_STATS`或`DBMS_STATS.GATHER_TABLE_STATS`）。*分析SQL语句本身是否存在语法问题或可以改写的空间（如减少函数运算、优化JOIN顺序等）。*检查表和索引的存储参数（如块大小）是否合适。优化建议：*根据分析结果，创建缺失的、选择合适的复合索引（考虑列顺序）。*重建或重新组织碎片化的索引。*修改或重写SQL语句，使其能更好地利用现有索引。*调整数据库参数或硬件资源。解析思路：按照诊断-分析-建议的逻辑流程，列出具体操作步骤和方法。5.B-Tree索引：*特点：基于平衡树结构，支持范围查询和等值查询。*适用场景：最常用的索引类型，适用于大多数场景，如主键、外键、经常用于`=`、`>`、`<`、`BETWEEN`、`LIKE'prefix%'`的查询。*局限性：在哈希分布均匀的数据上，查找等值操作的平均时间复杂度可能不如Hash索引。Hash索引：*特点：基于哈希表结构，通过计算键值的哈希码直接定位数据块。只支持等值查询（`=`）。*适用场景：适用于只进行精确等值匹配（`=`）查询的场景，且数据分布比较均匀。*局限性：不支持范围查询，对等值查询的效率依赖于数据分布的均匀性，不适用于有大量NULL值的列。解析思路：分别对比B-Tree和Hash索引的结构、支持的操作类型、主要优点和缺点，以及各自适合的应用场景。五、场景分析题现有索引问题：*`PK_SALES_ID`是主键索引，有效。*`IX_C