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?

Answer 1

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.

Answer 2

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.

Answer 3

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.