Imagine having an index on person_id and you would like to perform the query:

select bank_account_number from bank_account where person_id = :1 or :1 is null.

Unfortunately, most databases do not store null values in the index. Database prepares an execution plan before the execution of the SQL query. This plan is cached and it is the same for all values of :1. The database doesn’t know in advance what will be a value for :1 and thus can’t use the index on person_id column because maybe :1 can be null. What a damage!

Fortunately, we can rewrite this query so we can use the index! Take a look:

//1st select
select bank_account_number from bank_account where person_id = :1 and :1 is not null
 
union all
 
//2nd select
select bank_account_number from bank_account where :1 is null

With this query, when :1 is null, the database will recognize that the first select return 0 rows. For that reason, first select won’t be executed. Only the second select will be executed. A Full table scan will be performed in this case(because there is a null in the second select and indexes doesn’t store nulls).

On the other hand, when :1 has some value, the database will recognize that the second select returns 0 rows. In that case, it will perform just the first select. But this time, the database is sure that :1 is not null in the first select(because there is :1 is not null condition) and it can use index on person_id column! Index scan will be used this time instead of a Full table scan! This will dramatically improve the time of this SQL query.

Further reading

If this was an interesting article for you, consider reading Sql tuning. I enjoyed this book and there are a lot of useful things on how to tune your sql queries. This particular example that I showed you is from the book!