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 were planned for MySQL 6 but were introduced in MariaDB 5.2 only.

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

Photo of Markus Winand
Markus Winand tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at http://winand.at/

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

0
votes
0
answers
34
views

How to query for "previous page" with keyset pagination?

2 days ago alextsg 1
pagination postgresql
0
votes
0
answers
65
views
2
votes
1
answer
148
views

Table Names in Plural Form

Jul 31 at 16:43 simas 36
table