Within
computing
Computing is any goal-oriented activity requiring, benefiting from, or creating computing machinery. It includes the study and experimentation of algorithmic processes, and development of both hardware and software. Computing has scientific, ...
and
computer science
Computer science is the study of computation, automation, and information. Computer science spans theoretical disciplines (such as algorithms, theory of computation, information theory, and automation) to practical disciplines (includi ...
, an expression index, also known as a function based index, is a
database
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases s ...
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 In computers, case sensitivity defines whether uppercase and lowercase letters are treated as distinct (case-sensitive) or equivalent (case-insensitive). For instance, when users interested in learning about dogs search an e-book, "dog" and "Dog" a ...
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
Major databases which support expression indexes include:
IBM Db2 (since version 10.5),
Oracle Database (since release 8i.) and
PostgreSQL (since at least version 7
).
References
{{Reflist
Database management systems