Not NULL Constraint Influences Access Path

The optimizer can make use of explicitly defined Not NULL constraints to take advantage
of an index in order to avoid a full table scan since a B-tree index stores only not NULL values .
When count (constant) or count(*) is queried, we want to count no. of rows in the table. Hence , if there is a column which is defined as not NULL and has an index on it, the number of index entries in the index are bound to be same as the number of rows. The query optimizer uses the index to count no. of rows in the table.

Similarly, when a count (not-nullable-column) is queried, we want to count the no. of rows having not null values in the column. Since the column has a not NULL constraint on it, every row in the table will have a not null value in it and count(not-nullable-column) is same as count(*). As a result, the query optimizer can use the index on the column to process the query.
In fact, in both the cases above, any B-tree containing at least a not-nullable column can serve the purpose.

When a count (nullable-column) is queried, we want to count the no. of rows having not null values in the column. If we have an index on the column, the index will store only not NULL values and hence can be effectively used by the query optimizer to give the result.
In fact, the optimizer can use any index containing the nullable column for this purpose.

To demonstrate the above functionality, I have created a table HR.TEST with two columns – NOTNULL having not NULL constraint
– NULLABLE
. having same data as column NOTNULL but has not been declared not NULL
. has a B-tree index on it

Now I will query count for various arguments and check if optimizer can use the index on NULLABLE column.

Note that to process count(*), count(1) and count(notnull), the query optimizer uses Full Table Scan. Although the column NULLABLE has non-null values in all the rows but since it has not been explicitly declared not null , the optimizer does not know that no. of entries in index reflect the count correctly and hence does not use the index .

Now if query count(*), count(1), count(notnull) and count(nullable), the optimizer is able to avoid Full Table Index by making use of the index on NULLABLE column in all the cases . Since the column NULLABLE having index has been declared not null and optimizer knows that entries in the index represent all the rows of the table.

Now I will use the fact that even NULL values are stored in a composite index when at least one of the columns isn’t nullable. I am creating a multi column index on column NULLABLE and a dummy second column containing a not null value, so that the index will store even NULL values in column NULLABLE. To keep the size of the index small, I will assign the value zero to the dummy column.

Conclusion:
To enable the optimizer to choose index access in relevant cases,
– declare NOT NULL constraint on relevant columns or
– create a multi column index on nullable column and a dummy second column