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: Inb4 punished for "hacking".
gollark: Teal looks nice.
gollark: People disagree on something != that thing is arbitrary.
gollark: Which one did you chöose then?
gollark: I tried using Haskell but it felt too annoying to work with.

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.