The index on LAST_NAME has improved the performance considerably, but it requires you to search using the same case (upper/lower) as is stored in the database. This section explains how to lift this restriction without a decrease in performance.
- DB2
DB2 supports function based indexes on zOS for a while, but only since version 10.5 on LUW. The use of user-defined functions in indexes is not allowed.
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 is case-insensitive by default, but that can be controlled on column level. Starting with version 5.7 MySQL can create indexes on generated columns.
The backup solution for older versions 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.
