可空列的索引

Question

我在一个可空列上创建了索引，并希望像这样选择它的所有值：

SELECT e.ename 
FROM   emp e;

在执行计划中，我看到了一个 FULL TABLE SCAN （即使使用提示也没有帮助）

SELECT e.ename 
FROM   emp e
WHERE  e.ename = 'gdoron';

我在谷歌上查到索引中没有空条目，因此第一个查询无法使用索引。

我的问题很简单：为什么索引中没有空条目？

- gdoron is supporting Monica

---

顺便提一下，在你的第一个示例中，由于你选择了表中很大一部分的记录（在你的情况下为100％），因此可以执行完整的表扫描。无论你是否有Null值索引，优化器都会选择全表扫描来执行此查询。 - Ollie

@Ollie。这就是为什么我写下我尝试使用hint再次尝试，但没有帮助的原因... - gdoron

典型的B树索引不包括空值。位图索引将对空值进行索引。 - tbone

“我想选择它的所有值”很可能只需在您的第一个查询中添加where e.ename is not null即可完成。当然，如果您选择了任何无法仅通过索引提供服务的列，则最终将得到一个全文搜索（除非您强制使用提示，在这种情况下，它将使用您的索引，但实际上会导致更差的IO性能）。 - rejj

如果索引建在ename上，这也是意味着的话，优化器完全可以选择全索引扫描而不是全表扫描，但如果有空值的话就不行了。 - Dave Costa

@Dave Costa，我知道，但是OP的示例表明他正在返回所有行，包括空值，因此我的回答是这样的。不过我可能可以更清楚地表达。 - Ollie

Answer 1

默认情况下，关系数据库会忽略NULL值（因为关系模型认为NULL表示“不存在”）。所以，索引不会存储NULL值，因此如果您在SQL语句中有一个空值条件，相关的索引将被忽略（默认情况下）。

但是您可以克服这个问题，请查看此或此文章。

- aF.

---

我读了这篇文章，这就是为什么我问为什么它以这种方式工作。这很奇怪。 - gdoron

想一想，对于一个索引，它们如何区分所有的 NULL 值呢？ - aF.

Answer 2

如果你要获取表中的所有行，为什么认为应该使用索引呢？全表扫描是返回所有值最有效的方法。这与空值不在索引中无关，而是优化器选择检索数据最有效的方式有关。

@A.B.Cade：优化器有可能选择使用索引，但可能性较低。假设你有一个带有100行索引的表，但只有10个值。如果优化器使用了索引，它必须从索引中获取10行，然后将其扩展到100行；相反，使用全表扫描可以直接获取全部100行。以下是示例：

create table test1 (blarg varchar2(10));

create index ak_test1 on test1 (blarg);

insert into test1
select floor(level/10) from dual connect by level<=100;

exec dbms_stats.gather_table_stats('testschema','test1');

exec dbms_stats.gather_index_stats('testschema','ak_test1');

EXPLAIN PLAN FOR
select * from test1;

我的观点是，这个问题很大程度上基于一个错误的前提：索引扫描本质上比全表扫描更好。但事实并非总是如此，正如这个场景所展示的。

- Allan

---

请看DaveCosta的评论。他正在查询索引列 - 如果没有空值，它将使用INDEX FULL SCAN。 - A.B.Cade

我们不能确定它一定会使用索引，只能说它有可能使用。 - Dave Costa

我同意，但他在问题中确实说了“（即使提示也没有帮助）”。 - A.B.Cade

Answer 3

我不确定第一个查询在索引使用方面是否相关，至少第二个查询是可以的。

无论如何，虽然不能对包含空值的列进行索引，但有一些方法可以实现，例如：

create index MY_INDEX on emp(ename, 1);

请注意结尾处的, 1)，这是关键所在。

- Farid

---

你的意思不是：create index MY_INDEX on emp(NVL(ename, 'null')); 吗？ - gdoron

不是。逗号后跟数字1才是正确的形式，无需使用nvl。nvl是另一种技巧，但如果按照您编写的方式使用它，它会强制用户与字符串“null”进行比较。 - Farid

Answer 4

应该注意，位图索引 包括具有 NULL 值的行。 但是，你不应该仅仅因为想在索引中包含 NULL 值而创建位图索引。 位图索引是为特定用例设计的（详见文档）！

如果使用不当，那么整体性能可能会显著下降。

- Wernfried Domscheit