Expression-based indexes improve query performance

Starting with DB2® Version 10.5, you can use an extension to the CREATE INDEX statement to create an index that contains an expression-based key.

Using this expression-based key capability, you can create an index on data that is not stored in a table. When you create an index with an expression-based key, the results of the expression are stored in the index.

An SQL statement that contains a predicate that includes an expression can benefit from an index whose definition includes expression-based keys. With this feature, the database manager can choose from many highly efficient index-based methods to access the data and evaluate the predicate. As a result, performance is improved for queries that contain expressions.

An expression in an index is also useful if the query references the expression in other ways. For example, the query might return that expression in its results, or it might order the result set by that expression.

To benefit from efficient evaluation of expressions that are specified at run time, use an expression-based key, rather than other types of indexes whose keys only consist of one or more table columns.