Db2 Expression-based Index

Summary: in this tutorial, you will learn how to use the Db2 expression-based indexes to improve the speed of queries that involves expressions.

Introduction to Db2 expression-based indexes

See the following authors table from the sample database:

This statement finds all authors whose the first name is 'John';

SELECT 
    author_id, 
    first_name, 
    last_name 
FROM 
    authors 
WHERE 
    first_name = 'John';
Code language: SQL (Structured Query Language) (sql)

To locate the authors, the query optimizer had to scan the whole authors table. Here is the query plan:

To speed up the query, you may create an index for the first_name column as follows:

CREATE INDEX ix_fname 
ON authors(first_name);
Code language: SQL (Structured Query Language) (sql)

Execute the query above again, the speed is indeed improved because the query optimizer now can leverage the ix_fname index.

However, applications may request for all authors whose last name is JOHN, the uppercase version of John. The following query returns an empty set:

SELECT 
    author_id, 
    first_name, 
    last_name 
FROM 
    authors 
WHERE 
    first_name = 'JOHN';
Code language: SQL (Structured Query Language) (sql)

The applications expect that to get all authors whose last name is John, regardless of the word form such as uppercase, title case, or lowercase.

To fix this, you use the UPPER() function:

SELECT 
    author_id, 
    first_name, 
    last_name 
FROM 
    authors 
WHERE 
    UPPER(first_name) = 'JOHN';
Code language: SQL (Structured Query Language) (sql)

Now, the query returns the expected result, but it could not leverage the ix_fname index. Because ix_fname index stores the first names in the title case.

This is why expression-based indexes come to rescue.

The expression-based indexes allow you to index data based on a general expression.

The following statement creates an expression-based index:

CREATE INDEX ix_ufname 
ON authors(UPPER(first_name));
Code language: SQL (Structured Query Language) (sql)

The ix_ufname index stores the uppercase version of data in the first_name column.

If you view the query plan of the query, you will find that the following query uses the ix_ufname index:

SELECT 
    author_id, 
    first_name, 
    last_name 
FROM 
    authors 
WHERE 
    UPPER(first_name) = 'JOHN';
Code language: SQL (Structured Query Language) (sql)

Here is the query plan:

Creating expression-based index syntax

The basic syntax of creating an expression-based index is the following:

CREATE INDEX index_name
ON table_name(expression(columns));
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the index.
• Then, specify the name of the table with the expression that includes the columns of the table.

In this tutorial, you have learned how to use the Db2 expression-based index to improve the speed of queries that involves expressions.