1

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?

Grön Oktober
  • 11
  • 2
  • The index does not change the data stored in the table. –  Nov 28 '14 at 08:19
  • I didn't assume it would change the data stored in the table, I hoped it would alter the results from queries on the tables. – Grön Oktober Nov 28 '14 at 12:10
  • The select always returns data from the table, not from the index. You probably excepted an "index only scan" for that statement, but as the index data is not the same as the table data, I'm pretty sure Oracle doesn't use an "index only scan" in this situation. In fact I would consider that a bug because the query wouldn't return the data that is not stored in the table –  Nov 28 '14 at 12:16

2 Answers2

1

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
Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125
  • Thanks for explaining, now I understand why it doesn't work. Also, thanks for the creative solution. – Grön Oktober Nov 28 '14 at 12:11
  • @Grön You're welcome. If this helped you, please don't hesitate to vote up and/or accept the answer. This is the primarily way to say "thank you" on Stack Overflow. See http://stackoverflow.com/help/someone-answers – Sylvain Leroux Nov 28 '14 at 17:54
0

'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.

Shankar
  • 879
  • 8
  • 15
  • 1
    His `'null'` is just a string passed to the 2nd parameter of `NVL`. Nothing wrong with that - I often prefer to convert nulls into a string value to make them stand out in the output. The first parameter is the important one, and he's correctly passing the column name `"FOO"` to it. (His problem is that he didn't understand how an index works) – Jeffrey Kemp Nov 28 '14 at 00:29
  • As Jeffrey says, I just happened to chose the string 'null', in retrospect that was a bad choice as it could create confusion. – Grön Oktober Nov 28 '14 at 12:13
  • It can make sense, in the way that it is extra clear for the person reading the result, that the column is a NULL column. What is true, is that a NULL value is not always understood by end-users. Depending on the component in this context, something like "not applicable", or "N/A" may be easier to read. But, consider the fact somebody may one day put the exact value 'null' into that column. If you then replace reall NULL with 'null', one can't differ anymore. Maybe there's no issue, but technically there is, like when somebody starts sorting, but does NOT do as in this thread (replacing NULL). – tvCa Nov 28 '14 at 15:10