Using derived key indexes

Edit online

SQL indexes can be created where the key is specified as an expression. This type of key is also referred to as a derived key.

Suppose the following index is defined:

  CREATE INDEX TOTALIX ON EMPLOYEE(SALARY+BONUS+COMM AS TOTAL)

Now, return all the employees whose total compensation is greater than 50000.

  SELECT * FROM EMPLOYEE 	    
   WHERE SALARY+BONUS+COMM > 50000     
   ORDER BY SALARY+BONUS+COMM

The optimizer uses the index TOTALIX with index probe to satisfy both the WHERE selection and the ordering criteria.

There are some special considerations for matching the derived key with an expression used in the query.

No expression matching is done to match index key constants with host variables used in a query. This includes implicit parameter marker conversion performed by the database manager.
```
CREATE INDEX D_IDX1 ON EMPLOYEE (SALARY/12 AS MONTHLY)
```
In this example, return all employees whose monthly salary is greater than 3000. Use a host variable to provide the value used by the query.
```
long months = 12;
EXEC SQL SELECT * FROM EMPLOYEE WHERE SALARY/:months > 3000
```
The optimizer does not use the index since there is no support for matching the host variable value months in the query to the constant 12 in the index.
For a dynamic SQL statement, using the QAQQINI option PARAMETER_MARKER_CONVERSION with value *NO can be used to prevent conversion of constants to parameter markers. This technique allows for improved derived index key matching. However, because of the performance implications of using this QAQQINI setting, take care with its usage.
In general, expressions in the index must match the expression in the query.
```
SELECT * FROM EMPLOYEE 	    
  WHERE SALARY+COMM+BONUS > 50000 
```
In this example, the SALARY+COMM+BONUS expression is in a different order than the index key SALARY+BONUS+COMM and would not match.
It is recommended that derived index keys be kept as simple as possible. The index is less likely to be selected when a complex query expression and a complex index key expression need to be matched.
The CQE optimizer has limited support for matching derived key indexes.