Index for nullable column

Question

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)

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?

Solution 1

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

But you can suprass this problem, check THIS or THIS article.

Solution 2

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:

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.

Comments

[gdoron is supporting Monica]

I read this article, this is why I'm asking why this is working this way. It's weird.

[aF.]

Well think about this, in a index how can they distinct from all NULL's?

[A.B.Cade]

see DaveCosta 's comment. He's querying the index column - if not for the nulls it would have used a INDEX FULL SCAN

[Dave Costa]

Well, we can't say that it definitely would have used the index, just that it could have.

[gdoron is supporting Monica]

Didn't you mean: create index MY_INDEX on emp(NVL(ename, 'null'));

[Farid]

nope. comma followed by the digit 1 is the correct form. without the need to use nvl. nvl is another trick to use it but it forces the user to compare with the string 'null' if used as you wrote it.

[A.B.Cade]

I agree, but he did state in the question "(even a hint didn't help)"