Expression index

An expression index, also known as a function based index, is a database index that is built on a generic expression, rather than one or more columns. This allows indexes to be defined for common query conditions that depend on data in a table, but are not actually stored in that table.

A common use for an expression index is to support case-insensitive searching or constraints. For example, if a web site wants to make user names case-insensitive, but still preserve the case as originally entered by the user, an index can be created on the lower-case representation of the user name:

CREATE INDEX users__last_name_lower ON users( lower( last_name ) );

That will create a unique index on "lower(last_name)". Any queries that search on "lower(last_name)" could then make use of that index:

SELECT user_id FROM users WHERE lower( last_name ) = lower( 'Smith' );

Database support
See also: Comparison of relational database management systems § Indices

Major databases which support expression indexes include: DB2 (since version 10.5[1]), Oracle Database (since release 8i.[2]) and PostgreSQL (since at least version 7[3]).

gollark: Oh yes, right, you're working on somewhat useful stuff.
gollark: ⁢ you.
gollark: What are you working on?
gollark: He was meant to be doing the obstacle course thing, didn't particularly do anything, and was told by Mr Wilde to work out how to use the xbox controller, which is doomed to failure I think.
gollark: I know Joshua *isn't*, at least?

References
  1. "What's new in DB2 10.5 for Linux, UNIX, and Windows". Retrieved 2015-08-26.
  2. "Oracle Function Based Indexes". Retrieved 2015-08-26.
  3. "PostgreSQL - User's Guide - Chapter 7. Indices and Keys". Retrieved 2015-08-26.
This article is issued from Wikipedia. The text is licensed under Creative Commons - Attribution - Sharealike. Additional terms may apply for the media files.