Putting NVL in an INDEX does not seem to work (Oracle 11.2.0.4.0)

Question

I am trying to understand why NVL works fine in a direct SELECT, but not in an INDEX. Here is shown how it works perfectly before creating the INDEX (columnn foo is 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

Now I try to create an INDEX, so I don't have to put NVLs in SELECTS:

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

But when I re-run the original SELECT, I don't get the expected 'null' strings:

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

I most likely have misunderstood something. Can you see what seems to be the problem with the INDEX?

Answer 1

'NULL' is not equal to NULL. When you put something inside quotes, it is a string.

That apart, why do you want to check if a variable is NULL and then use a NVL function to display another NULL? Doesn't make sense.

Answer 2

Index do not "add" or "expand" your table in the sense they don't add extra "query-able" data. They only help to retrieve data efficiently. As they can check quickly if there is some value in a column, they are also implied when forcing uniqueness of values. Not much more.

Given your explanations, you are in fact looking for virtual columns instead:

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;

Producing:

ID      FOO2
1001    null
1002    null