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 (includin ...
, 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 spa ...
index
Index (or its plural form indices) may refer to:
Arts, entertainment, and media Fictional entities
* Index (''A Certain Magical Index''), a character in the light novel series ''A Certain Magical Index''
* The Index, an item on a Halo megastru ...
that is built on a generic expression, rather than one or more
column
A column or pillar in architecture and structural engineering is a structural element that transmits, through compression, the weight of the structure above to other structural elements below. In other words, a column is a compression member ...
s. 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
Db2 is a family of data management products, including database servers, developed by IBM. It initially supported the relational model, but was extended to support object–relational features and non-relational structures like JSON and ...
(since version 10.5),
Oracle Database
Oracle Database (commonly referred to as Oracle DBMS, Oracle Autonomous Database, or simply as Oracle) is a multi-model database management system produced and marketed by Oracle Corporation.
It is a database commonly used for running online ...
(since release 8i.) and
PostgreSQL
PostgreSQL (, ), also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the In ...
(since at least version 7
).
References
{{Reflist
Database management systems