Functions


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

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 5.6 does neither support virtual columns nor function-based indexing like described below. 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.

About the Author

As an author, trainer, and coach Markus Winand specializes in helping developers cope with SQL performance issues. He also published the book SQL Performance Explained and tweets his best performance tips via @SQLPerfTips.http://winand.at/

Recent Questions at Ask.Use-The-Index-Luke.com

0
votes
1
answer
62
views
0
votes
0
answers
314
views

Fanout in R-Tree

Mar 27 at 08:07 jamie 1
tree indexing
0
votes
1
answer
98
views

Think About It

Mar 26 at 12:54 Markus Winand ♦♦ 511
reflection