databases with flexible indexes?

Question

form wikipedia:http://en.wikipedia.org/wiki/Index_(database)

Some databases extend the power of indexing by allowing indexes to be created on functions or expressions. For example, an index could be created on upper(last_name), which would only store the upper case versions of the last_name field in the index. Another option sometimes supported is the use of "filtered" indexes, where index entries are created only for those records that satisfy some conditional expression. A further aspect of flexibility is to permit indexing on user-defined functions, as well as expressions formed from an assortment of built-in functions.

what are those databases that support flexible indexes which can be:

• On computed column like: (col1 + col2) * 100
• filtered indexes.
• indexing on user-defined functions.

and what's known terminology for this feature?

Answer 1

As far as I know, all major RDBSs support this feature. I've personally used it on MySQL and PostgreSQL, but I would be shocked if it wasn't available in MSSQL and Oracle.

Unfortunately, I do not know any special term for this.

Answer 2

In oracle they are called function based indexes.

For filtering you can be tricky and create a function that returns the value if the filter matches or null if it doesn't. Nulls are not stored in btree indexes so the index is essentially filtered. You just have to make sure you use the same function in your queries.

I believe in sql server you can create a computed column and index that.

Answer 3

SQL Server 2000+ (at least) supports indexes on function-based columns and computed columns. Indexes on functions can be replicated using indexed views on functions.

There are conditions as always but mainly around determinism (which is probably universal)

SQL Server 2008+ has filtered indexes.

AFAIK, there is no special term. They're just indexes.