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?
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).
Answered By – aF.
Answer Checked By – Mary Flores (BugsFixing Volunteer)