Making predicates eligible for expression-based indexes

You can create an expression-based index to improve the performance of queries that use column-expression predicates.

About this task

Introductory concepts

Begin program-specific programming interface information. Unlike a simple indexes, an expression-based index uses key values that are transformed by an expression that is specified when the index is created. However, Db2 cannot always use an expression-based index. For example, Db2 might not be able to use an index on expression for queries that contain multiple outer joins, materialized views, and materialized table expressions.

Db2 does not use an expression-based index for queries that use sensitive static cursors.

Procedure

To enable Db2 to use an expression-based index, use the following approaches:

Create an expression-based index for queries that contain predicates that use column-expressions.
Rewrite queries that contain multiple outer joins so that a predicate that can be satisfied by an index on expression is in a different query block than the outer joins.
For example, Db2 cannot use an expression-based index for the UPPER predicate in the following query:
```
SELECT ...
FROM T1
LEFT OUTER JOIN T2
  ON T1.C1 = T2.C1
LEFT OUTER JOIN T3
  ON T1.C1 = T3.C1
WHERE UPPER(T1.C2, 'EN_US') = 'ABCDE'
```
However, you can rewrite the query so that Db2 can use an expression-based index for the UPPER predicate by placing the UPPER expression in a separate query block from the outer joins, as shown in the following query:
```
SELECT ...
FROM (
  SELECT C1
  FROM T1
  WHERE UPPER(T1.C2, 'EN_US') = 'ABCDE'
) T1
LEFT OUTER JOIN T2
  ON T1.C1 = T2.C1
LEFT OUTER JOIN T3
  ON T1.C1 = T3.C1
```
Rewrite queries that contain materialized views and table expressions so that any predicate that might benefit from an expression-based index is coded inside the view or table expression.
For example, in the following query as written, the table expression X is materialized because of the DISTINCT keyword, and Db2 cannot use an expression-based index for the UPPER predicate:
```
SELECT ...
FROM (
  SELECT DISTINCT C1, C2 FROM T1
) X
, T2
WHERE X.C1 = T2.C1
  AND UPPER(X.C2, 'En_US') = 'ABCDE'
```
However, you can enable Db2 to use an expression-based index for the UPPER predicate by rewriting the query so that the UPPER predicate is coded inside the table expression, as shown in the following example:
```
SELECT ...
FROM (
  SELECT DISTINCT C1, C2 FROM T1
  WHERE UPPER(T1.C2, 'En_US') = 'ABCDE'
) X
, T2
WHERE X.C1 = T2.C1
```