by Markus Winand.

Table of Contents › The Where Clause ›

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.

Contents

  1. Case-Insensitive Search — UPPER and LOWER

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

  3. Over-Indexing — Avoid redundancy

Previous pageNext page

You can’t learn everything in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up. Have a look at modern-⁠sql.com as well.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the Book

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

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »

The 3-Minute Test

Table of Contents

Latest Blog Posts

Geek Ware

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“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 and GDPR | CC-BY-NC-ND 3.0 license