SQL 可空列的索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9175591/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Index for nullable column
提问by gdoron is supporting Monica
I have an index on a nullable column and I want to select all it's values like this:
我在一个可为空的列上有一个索引，我想选择它的所有值，如下所示：
SELECT e.ename
FROM emp e;
In the explain plan I see a FULL TABLE SCAN(even a hint didn't help)
在解释计划中，我看到了FULL TABLE SCAN（即使是提示也无济于事）
SELECT e.ename
FROM emp e
WHERE e.ename = 'gdoron';
Does use the index...
是否使用索引...
I googled and found out there are no null entries in indexes, thus the first query can't use the index.
我用谷歌搜索，发现索引中没有空条目，因此第一个查询不能使用索引。
My question is simple: why there aren't null entries in indexes?
我的问题很简单：为什么索引中没有空条目？
回答by aF.
By default, relational databases ignore NULL values (because the relational model says that NULL means "not present"). So, Index does not store NULL value, consequently if you have null condition in SQL statement, related index is ignored (by default).
默认情况下，关系数据库忽略 NULL 值（因为关系模型说 NULL 意味着“不存在”）。因此，Index 不存储 NULL 值，因此如果 SQL 语句中有 null 条件，则忽略相关索引（默认情况下）。
回答by Allan
If you're getting all of the rows from the table, why do you think it should use the index? A full table scan is the most efficient means to return all of the values. It has nothing to do with the nulls not being in the index and everything to do with the optimizer choosing the most efficient means of retrieving the data.
如果您从表中获取所有行，为什么您认为它应该使用索引？全表扫描是返回所有值的最有效方法。它与不在索引中的空值无关，而与优化器选择最有效的数据检索方式无关。
@A.B.Cade: It's possible that the optimizer could choose to use the index, but not likely. Let's say you've got a table with an indexed table with 100 rows, but only 10 values. If the optimizer uses the index, it has to get the 10 rows from the index, then expand it to 100 rows, whereas, with the full-table scan, it gets all 100 rows from the get-go. Here's an example:
@ABCade：优化器有可能选择使用索引，但不太可能。假设您有一个带有索引表的表，该表有 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;
My point is largely that this question is based largely on a flawed premise: that index-scans are intrinsically better that full-table scans. That is not always true, as this scenario demonstrates.
我的观点主要是这个问题主要基于一个有缺陷的前提：索引扫描本质上比全表扫描更好。正如这个场景所展示的那样，情况并非总是如此。
回答by Farid
I am not sure the first query is pertinent in terms of index usage, at least the second could.
我不确定第一个查询在索引使用方面是否相关，至少第二个可以。
Anyway, while it is true that you cannot index a column containing a null value, there are ways to do it like for example:
无论如何，虽然您确实不能索引包含空值的列，但有一些方法可以做到，例如：
create index MY_INDEX on emp(ename, 1);
notice the , 1)at the end which does the trick.
注意, 1)最后的技巧。
