2012-03-13Functions
The index in the previous section has improved the performance considerably, but requires to you to store the names in all caps. This section explains how to remove this restriction without losing performance.
- DB2
Function based indexes available for DB2 on zOS but not on other systems.
The backup solution is to create a real column in the table that holds the result of the function or expression. The column must be maintained by a trigger or by the application layer—whatever is more appropriate. The new column can be indexed. The where clause must use the new column (without the expression).
- MySQL
MySQL does, as of version 5, neither support function based indexes nor virtual columns. MySQL is case-insensitive by default, but that can be controlled on column level. Virtual columns are in the queue for version 6.
The backup solution is to create a real column in the table that holds the result of the function or expression. The column must be maintained by a trigger or by the application layer—whatever is more appropriate. The new column can be indexed. The where clause must use the new column (without the expression).
- Oracle
The Oracle database supports function based indexes since release 8i. Virtual columns were additionally added with 11g.
- PostgreSQL
PostgreSQL fully supports Indexes on Expressions since release 7.4 (partially supported since 7.2)
- SQL Server
SQL Server supports Computed Columns that can be indexed since release 2000.
share and subscribe
RSS FeedFlattr this! Follow me on TwitterShare at Google+Like on Facebook