Oracle 数据库在哪些情况下会放弃索引扫描而转为全表扫描

原创于 2025-08-26 11:41:30 发布 · 369 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

数据库同时被 2 个专栏收录

124 篇文章

订阅专栏

Oracle

46 篇文章

订阅专栏

这是一个非常经典且重要的 Oracle 数据库性能调优问题。Oracle 的优化器（CBO）非常智能，它会根据成本（Cost）来决定使用索引还是全表扫描。在很多情况下，全表扫描的成本反而低于索引扫描，因此优化器会“放弃”索引。

以下是 Oracle 数据库可能放弃索引扫描而选择全表扫描的常见情况：

1. 数据量因素（最常见）

表数据量很小：如果一个表只有几个数据块（Blocks），那么直接读取这几个块的全表扫描会比“先读索引块，再根据ROWID去读数据块”的索引范围扫描更快。因为索引扫描需要两次I/O操作。
需要检索的数据量很大：当查询要返回表中很大比例的数据时（通常经验值是超过总行数的 5%-10%，但并非绝对），使用索引可能更慢。因为索引扫描是随机的、单块的I/O（ROWID访问），而全表扫描可以采用更高效的多块读（Multiblock Read）方式，顺序地一次性读取多个块，吞吐量更高。

2. 索引本身的因素

索引集群因子（Clustering Factor）过高：这是索引的一个关键属性，它衡量了索引行的存储顺序与表中数据行的存储顺序的匹配程度。
- 低集群因子：意味着相同索引键值对应的数据行基本上存储在相同的数据块中，通过索引访问效率很高。
- 高集群因子：意味着相同索引键值对应的数据行分散在表的各个数据块中。这样使用索引查询时，为了获取少数几行数据，可能不得不随机读取大量的、分散的数据块，I/O成本急剧上升。在这种情况下，CBO很可能认为全表扫描的成本更低。
索引统计信息过时或缺失：如果索引没有及时收集统计信息，或者根本没有统计信息，CBO就无法准确计算使用索引的成本，可能会错误地低估索引的成本，从而选择全表扫描。使用 DBMS_STATS 包定期收集统计信息至关重要。
索引被标记为不可用（UNUSABLE）：在对表进行批量 DML 操作（如 ALTER TABLE ... MOVE 或分区操作）后，索引可能会变为 UNUSABLE 状态。此时如果执行查询，Oracle 无法使用该索引，将被迫进行全表扫描。需要重建（REBUILD）索引才能再次使用。

3. 查询方式和SQL编写

在索引列上使用了函数或运算：例如，WHERE UPPER(name) = 'SMITH' 或 WHERE salary * 1.1 > 5000。如果索引是建立在 name 或 salary 上，由于查询条件对列进行了操作，Oracle 无法使用该索引。除非你创建了相应的函数索引（如 CREATE INDEX idx_upper_name ON emp(UPPER(name));）。
使用了隐式类型转换：例如，索引列 id 是字符串类型（VARCHAR2），但查询写的是 WHERE id = 123（数字类型）。Oracle 必须隐式地将列或值进行转换（TO_NUMBER(id) = 123 或 id = '123’），这会导致索引失效。
对NULL值的查询：B树索引不存储完全为 NULL 的条目。因此，查询 WHERE column IS NULL 无法使用普通的B树索引，会走全表扫描。如果需要频繁查询NULL值，可以考虑使用位图索引（但在高并发DML环境中需谨慎）或函数索引（如 CREATE INDEX idx_null ON table_name (CASE WHEN column IS NULL THEN ‘Y’ END);）。
使用了 != 或 NOT 操作：大多数情况下，WHERE column != ‘value’ 或 WHERE NOT (column = ‘value’) 会导致全表扫描，因为它需要检查表中的所有行。
使用 OR 条件且部分条件无索引：例如 WHERE indexed_column = ‘A’ OR unindexed_column = ‘B’。即使有一个条件能用索引，由于 OR 的存在，优化器也可能会选择全表扫描。

4. 优化器提示和设置

使用了全表扫描提示：开发人员可以在SQL中强制指定使用全表扫描，例如：SELECT /*+ FULL(employees) */ * FROM employees ...。
优化器模式（OPTIMIZER_MODE）：如果会话或系统级别的优化器模式被设置为 ALL_ROWS，优化器的目标是实现最佳吞吐量（尽可能快地完成整个查询），它更倾向于使用全表扫描。如果设置为 FIRST_ROWS_n，优化器的目标是实现最佳响应时间（尽快返回前几行），它会更倾向于使用索引。

5. 并行执行

如果表被设置了较高的并行度（DOP），Oracle 可能会认为使用并行全表扫描的成本远低于串行的索引扫描，从而选择全表扫描。

如何诊断和确认？

当发现SQL执行慢时，可以使用以下工具来查看执行计划，确认是否发生了全表扫描以及原因：

EXPLAIN PLAN FOR：

EXPLAIN PLAN FOR
SELECT * FROM your_table WHERE your_condition;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SQL*Plus 的 AUTOTRACE：

SET AUTOTRACE TRACEONLY EXPLAIN
-- 执行你的SQL

查询动态性能视图（如 V$SQL_PLAN）：
可以查看已执行SQL的真实执行计划。

在查看执行计划时，如果看到 TABLE ACCESS FULL，就说明发生了全表扫描。同时，执行计划的 Cost（成本） 和 谓词信息（Predicate Information） 可以帮助你理解优化器为什么做出这个选择。

总结：优化器选择全表扫描并非总是坏事。它的选择是基于成本的理性决策。作为DBA或开发者，我们需要理解其背后的原理，通过检查表的大小、查询条件、索引质量（特别是集群因子）和统计信息来判断这个选择是否合理。如果不合理（例如本该用索引却没用），再针对性地进行优化，如修改SQL、创建更合适的索引或刷新统计信息。