Avoid functions on index columns

Applying a function to a key column will prevent the >DBMS from being able to search the index using the column value. For example, given these two queries to retrieve a particular row:

                        select * from t where keycol = :hostvar;
                        

                        select * from t where rtrim(keycol) = :hostvar;
                        

                     

The first query can use the index to find the row very quickly. For the second query, the database must retrieve every value of the key column from either the table or the index, in order to apply the function to those values.

If possible, move the function to the other side of the expression:

                        select * from t where keycol = rpad(:hostvar, n);
                        

                     

When to use a function index

Some databases allow creating an index on a function of a column. Using a function index becomes necessary when there is no inverse function that allows moving the function to a non-indexed value. For example, most databases always use case-sensitive comparisons, and there is no inverse function for a lower function. Say an application requires a query that ignores case and padding, as in:

                        select ... from schema.tablename where lower(trim(email_address)) = lower(trim(:emailaddr));
                        

                     

To avoid a table or index pagespace scan to convert all of the email_address values to lower case, a function index could be created on that column.

                        create index schema.indexname​
                        

                           on schema.tablename (
                        

                              lower(trim(email_address))
                        

                           ) ...;
                        

                     

Watch out for implied coersion of key columns

Implied coersion of key columns can result in the same type of problems that are caused by explicit functions. For example, when the fixed length CHAR data type is being used, one side of a comparison will be padded automatically if they are different lengths. Therefore, a key column should be compared only with constants or variables of an equal or shorter length. Here are some examples that use the same CHAR(6) "gender" column that was used in the discussion of when to use CHAR vs. VARCHAR. These examples will not cause automatic padding of the GENDER column:

                        GENDER = 'MALE'
                        

                        GENDER = 'MALE  '
                        

                        GENDER = 'FEMALE'
                        

                     

These examples should be avoided because they will cause automatic padding of the GENDER column to expand its length from 6 to 7 characters:

                        GENDER = 'UNKNOWN'
                        

                        GENDER = 'FEMALE '
                        

                     

back to top

Last updated Thursday November 16, 2006