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 '

Last updated Thursday November 16, 2006

Copyright © 2006 How To Guides .com. Alteration of content, including addition of any function such as hypertext links or pop-up advertising, or interference with the hypertext links or other functions of this site is expressly prohibited.

DISCLAIMER: All information, links, forms, applications and other items on this site or obtained from it are provided AS IS, WITHOUT WARRANTY OF ANY KIND EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.

Add Site or Add Page to Favorites	^>	Computers > Databases >
		Functions

Functions

Avoid functions on index columns

When to use a function index

Watch out for implied coersion of key columns