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);
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))
) ...;
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
Printer-friendly PDF* format:
You are currently viewing this page in XHTML 1 Style Sheet* format (* see Clicklets for more infomation). This document is also available in XHTML 1*XML*HTML 4*HTML 5 Style Sheet*HTML 5 XML*HTML 5 non-XML* XHTML 2* XHTML Mobile* WML Mobile* and printer-friendly PDF* formats. This is accomplished with Single Source Publishing, a content management system that uses templates in XSLT style sheets provided by XML Styles .com to transform the source content for various content delivery channels. There is also RDF* metadata that describes the content of this document.
