Index for a nullable column

Question

I have an index in a column with a null value, and I want to highlight all its values ​​as follows:

SELECT e.ename FROM emp e; 

In terms of explanation, I see a FULL TABLE SCAN (not even a hint helped)

 SELECT e.ename FROM emp e WHERE e.ename = 'gdoron'; 

Uses an index ...

I googled and found out that there are no null entries in the indexes, so the first query cannot use the index.

My question is simple: why are there no null entries in the indexes?

Answer 1

By default, relational databases ignore NULL values ​​(because the relational model says NULL means no). Thus, the index does not store the NULL value, therefore, if you have a null condition in the SQL statement, the associated index is ignored (by default).

But you can solve this problem, check the IT or IT article.

Answer 2

If you get all the rows from a table, why do you think it should use an index? A full table scan is the most effective way to return all values. It has nothing to do with zeros that are not in the index, and everything related to the optimizer choosing the most efficient ways to retrieve data.

---

@ABCade: Perhaps the optimizer can use the index, but hardly. Say you have a table with an indexed table with 100 rows, but only 10 values. If the optimizer uses an index, it should get 10 rows from the index, and then expand it to 100 rows, while when scanning at full table, it gets all 100 rows from get-go. Here is 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 of view basically lies in the fact that this question is mainly based on an erroneous premise: indexed crawls are inherently better than crawling a full table. This is not always the case as this scenario demonstrates.

Answer 3

I'm not sure if the first query is appropriate in terms of using the index, at least the second one can.

In any case, although it is true that you cannot index a column containing a null value, there are ways to do this, for example:

 create index MY_INDEX on emp(ename, 1); 

pay attention to , 1) at the end, which does the trick.