将NVL放在索引中似乎不起作用(Oracle 11.2.0.4.0)

Question

我试图理解为什么NVL在直接选择中工作正常，但在索引中不能。下面显示了它在创建 INDEX 之前是如何完美工作的（columnn foo 是 varchar2）：

SELECT id,foo FROM bar WHERE foo IS NULL;
1001
1002
SELECT id, NVL("FOO", 'null') FROM bar WHERE foo IS NULL;
1001    null
1002    null

现在我尝试创建一个索引，所以我不必将 NVL 放在 SELECT 中：

CREATE INDEX "BUZ_UTV3"."IX_NULL_FOO" 
ON "BUZ_UTV3"."BAR" (NVL("FOO", 'null')) 
TABLESPACE "TEST01_BUZUTV3";

但是当我重新运行原始 SELECT 时，我没有得到预期的"空"字符串：

SELECT id,foo FROM bar WHERE foo IS NULL;
1001
1002

我很可能误解了什么。你能看到索引的问题是什么吗？

Answer 1

索引不会

"添加"或"扩展"您的表，因为它们不会添加额外的"可查询"数据。它们只有助于有效地检索数据。由于它们可以快速检查列中是否有某些值，因此在强制值的唯一性时也会暗示它们。仅此而已。

鉴于您的解释，您实际上是在寻找虚拟列：

alter table bar
      add foo2 varchar2(20)  -- <--- or whatever type you need
      generated always as (NVL(foo, 'null')) virtual;
select id, foo2 from bar where foo is null;

生产：

ID      FOO2
1001    null
1002    null

Answer 2

'NULL

' 不等于 NULL。当你把东西放在引号里时，它是一个字符串。

除此之外，为什么要检查一个变量是否为 NULL，然后使用 NVL 函数显示另一个 NULL？没有意义。