Oracle索引使用解析：哪些SQL语句能真正用到索引？

在日常的 数据库 性能调优工作中，理解索引何时生效是开发者和DBA的基本功。我们一起来看看这个经典的场景：

假设在 Oracle 数据库中，我们通过以下语句创建了一个测试表 T，并为其 object_id 字段创建了索引。注意，T 表的 object_id 字段允许为 NULL。

create table t as select * from dba_objects;
create index idx_t_object_id on t(object_id);

现在的问题是，针对这个表结构，以下哪些 SQL 语句会利用到我们创建的 idx_t_object_id 索引来返回记录？

选项如下：

A、select count(*) from t;

B、select count(object_id) from t where object_id is not null;

C、select avg(object_id) from t ；

D、select avg(object_id) from t where object_id is not null;

E、select count(object_id) from t;

F、select count(*) from t where object_id is not null;

核心知识点解析

要正确回答这个问题，需要理解 Oracle 索引的这几个关键行为：

1. 索引与 NULL 值：在 B-Tree 索引中，所有键值（KEY）为 NULL 的记录通常不会被存储。因此，如果一个列允许为 NULL，那么仅基于该列的索引无法覆盖表中的所有行。
2. 聚合函数的行为：像 COUNT(column) 这样的聚合函数，会自动忽略该列的 NULL 值，只统计非 NULL 的行。而 COUNT(*) 则是统计表中的所有行，不论字段值是否为 NULL。
3. 优化器的选择：当查询可以通过扫描一个更小、更紧凑的索引来获得结果，而无需访问表时，优化器通常会选择“索引快速全扫描（INDEX FAST FULL SCAN）”或“索引全扫描（INDEX FULL SCAN）”，这比全表扫描要高效得多。

逐项分析

基于以上原则，我们来分析每个选项：

• *A、`select count() from t;** 此查询需要统计**表中所有行的数量**。由于object_id索引不包含NULL值，因此它无法提供完整的行数信息（如果表中有object_id为NULL的行）。为了得到准确结果，优化器必须进行全表扫描（FULL TABLE SCAN），而不会使用idx_t_object_id` 索引。

• B、select count(object_id) from t where object_id is not null;
  这个查询的意图很明确：统计 object_id 非空的行数。由于 WHERE 子句过滤掉了 NULL 值，结果集完全落在了索引覆盖的范围内。优化器可以仅通过扫描 idx_t_object_id 索引（索引快速全扫描）就得到结果，无需回表。因此，这条语句会利用索引。

• C、select avg(object_id) from t;
  计算整个表 object_id 列的平均值。AVG(column) 函数同样会忽略该列的 NULL 值。虽然索引包含了所有非 NULL 的 object_id 值，但要计算平均值，还需要知道参与计算的行数。如果表中有 object_id 为 NULL 的行，仅扫描索引无法得知这些行的存在（它们不在索引中），从而可能影响对总行数的判断（用于计算平均值）。因此，优化器通常不会冒险使用索引，而是选择全表扫描。

• D、select avg(object_id) from t where object_id is not null;
  与选项B同理，WHERE object_id is not null 确保了数据处理范围完全被索引覆盖。要计算平均值，需要非 NULL 值的总和以及它们的个数，这些信息都可以通过扫描 idx_t_object_id 索引直接获得（因为索引键值就是 object_id 本身）。所以，这条语句也会利用索引。

• E、select count(object_id) from t;
  如前所述，COUNT(column) 只统计该列非 NULL 的行。这正好与索引所包含的数据范围（所有非 NULL 的 object_id）完全一致。因此，优化器可以高效地使用索引快速全扫描来获得精确计数，这是典型的索引适用场景。

• F、select count(*) from t where object_id is not null;*
  这个查询容易被误解。WHERE object_id is not null 限定了范围，但 `COUNT()` 仍然是要统计满足条件的行数。所有满足 object_id is not null 条件的行，其 object_id 值都必然存在于 idx_t_object_id 索引中。优化器可以通过扫描索引来计数，而无需访问表。因此，这条语句同样会利用索引**。

总结

综上所述，能有效利用到 idx_t_object_id 索引的SQL语句是：B、D、E、F。

理解索引、NULL 值以及聚合函数之间的微妙关系，对于编写高效的 SQL语句 至关重要。希望这个分析能帮助你更深入地掌握 Oracle 索引的使用场景。如果你在实践中有其他有趣的发现，欢迎到 云栈社区 分享和讨论。