2012-03-13Over Indexing

In case the concept of function based indexing is new to you, you might be tempted to index everything. But this is the very last thing you should do. Every index causes ongoing maintenance. Function based indexes are particularly troublesome because they make it very easy to create redundant indexes.

The case insensitive search from above can be implemented with the LOWER function as well:

SELECT first_name, last_name, phone_number
  FROM employees
 WHERE LOWER(last_name) = LOWER('winand');

The query cannot use the EMP_UP_NAME index because it uses a different function. We could, of course, create a second index on LOWER(last_name) for this query. But the better solution is to use the same function for all case-insensitive searches on LAST_NAME.

Besides the effort for the initial index creation, the create index statement requires the database to consider the index for every SQL statement executed in the future. For insert, update, and delete it means to apply all table changes to the index too. Chapter 8, “Insert, Delete and Update” describes this process in more detail.