by Markus Winand.

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

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.

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

Contents

  1. Case-Insensitive SearchUPPER and LOWER

  2. User-Defined Functions — Limitations of function-based indexes

  3. Over-Indexing — Avoid redundancy

Previous pageNext page

About the Author

Photo of Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing. His book entitled SQL Performance Explained has become standard reading.

Buy his Book on Amazon

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy on Amazon
(paperback only)

Paperback and PDF also available at Markus’ store.

Hire Markus

…to answer your current SQL questions.

The quick and easy way to benefit from his extensive knowledge and experience.
Learn more »

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy | CC-BY-NC-ND 3.0 license