2010-08-21User Defined Functions

A case-insensitive search is just one use case for function based indexes. Other functions can be “indexed” as well. In fact, almost every function—even user defined functions—can be used in an index definition.

But there is one important exception. It is, for example, not possible to use the current time as part of an index definition. Neither directly, nor indirectly as in the following example:

CREATE FUNCTION get_age(date_of_birth DATE) 
RETURN NUMBER
AS
BEGIN
  RETURN 
    TRUNC(MONTHS_BETWEEN(SYSDATE, date_of_birth)/12);
END;
/

The function uses the current date (SYSDATE) to calculate the age based on the supplied date of birth. You can use this function in all parts of an SQL query. For example, in the select-list and the where clause:

SELECT first_name, last_name, get_age(date_of_birth)
  FROM employees
 WHERE get_age(date_of_birth) = 42;

Although it’s a very elegant and simple way search for all 42-year-old employees, it is not possible to tune this statement with a function based index because the GET_AGE function is not deterministic. That means, the result of the function call is not exclusively determined by its parameters. Only functions that always return the same result for the same parameters—functions that are deterministic—can be indexed.

The reason for this limitation is simple. When inserting a new row, the database calls the function and stores the result in the index. But there is no database process that would ever update the age afterwards. The index is only updated whenever the table is updated.

Besides being deterministic, PostgreSQL and Oracle requires functions to be declared deterministic. Hence, you have to use the keyword DETERMINISITC (Oracle) or IMMUTABLE (PostgreSQL) for functions you plan to use in an index definition.

Other examples for functions that cannot be “indexed“ are random number generators and all functions that depend on environment variables. That are, for example, the Oracle NLS (National Language Support) settings.