Expression index
≈ 3 min read
Names in other languages
No names in other languages are held for this entry yet.
Article bodies are English and are not machine-translated. A name in another language is not a translation of the article.
Within computing and computer science, 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' );
Within computing and computer science, 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== 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==
Text derived from https://en.wikipedia.org/w/index.php?oldid=1078912399 under CC BY-SA 4.0; modifications have been made.