Functional Indexes

8.5. Functional Indexes

For a functional index, an index is defined on the result of a function applied to one or more columns of a single table. Functional indexes can be used to obtain fast access to data based on the result of function calls.

For example, a common way to do case-insensitive comparisons is to use the lower function:

SELECT * FROM test1 WHERE lower(col1) = 'value';

This query can use an index, if one has been defined on the result of the lower(column) operation:

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

The function in the index definition can take more than one argument, but they must be table columns, not constants. Functional indexes are always single-column (namely, the function result) even if the function uses more than one input field; there cannot be multicolumn indexes that contain function calls.

Tip: The restrictions mentioned in the previous paragraph can easily be worked around by defining a custom function to use in the index definition that computes any desired result internally.

Home
Online Resources
General
Beginner Tutorials
MySQL
PostgreSQL
Oracle
mSQL
Microsoft SQL
Contact Us
Random quote of the moment:
The moving cursor writes, and having written, blinks on.
 
http://www.sql.org/
 
Proudly designed and hosted by OmegaSphere, providers of all your Web Hosting, SSL Certificate, and Domain Name needs!