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.

If you like my way of explaining things, you’ll love my book.

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
57
views

We want to buy the book but I can't

Jul 18 at 21:36 Markus Winand ♦♦ 541
book
0
votes
2
answers
120
views
0
votes
0
answers
780
views

Performance very bad in Postgresql 9.3

Jul 08 at 11:54 Markus Winand ♦♦ 541
performance issue