Oracle Query Tuning – Example 3

In this query tuning example we will see how to index table columns null value to improve the performance of query. Many developers writes queries which has filter condition as NULL i.e. they are quering the tables with something IS NULL. We will also see how oracle executes with such type of queries. For our demonstration I have created EMPLOYEE table which has structure as given below.

The above query returned 1000 rows. Execution plan shows that optimizer chooses to use FULL TABLE SCAN for Employee table even there is an index present for PROJECT column. Now it’s time to know why oracle is not used an index here.

Why Oracle has not used an index?

By Default, RDBMS databases ignores the NULL Values. In simple words NULL means nothing is present. Because of this, optimizer has not used an index in above example. Now let’s see how to deal with such null values.

Solution:

We need to recreate the above index as a functional index to which we are going to add some default value. We can add any default value. I prefer 1 default value.