With expression-based indexes, you can create an index that includes expressions. The performance of queries that involves expressions is improved if the database manager chooses an index that is created on the same expressions. Expression-based indexes are best suited when you want an efficient evaluation of queries that involve a column expression. Simple index keys consist of a concatenation of one or more specified table columns. Compared to simple indexes, the index key values of expression-based indexes are not the same as values in the table columns. The values are transformed by the expressions that you specify.
You can create the index with the CREATE INDEX statement. If an index is created with the UNIQUE option, the uniqueness is enforced against the values that are stored in the index. The uniqueness is not enforced against the original column values.
Create a table
- CREATE TABLE EXP_TEST (FIRST CHAR(20), LAST CHAR(20)) ORGANIZE BY ROW
Insert records into the table
- INSERT INTO EXP_TEST VALUES (‘John’, ‘Stefan’)
Create a function based index on the FIRST column
- CREATE INDEX name_upper on EXP_TEST (UPPER(FIRST))
- RUNSTATS ON TABLE EXP_TEST AND INDEXES ALL
Examine the explain plans for the following two queries
- SELECT * FROM EXP_TEST WHERE UPPER(LAST) = ‘Stefan’
- SELECT * FROM EXP_TEST WHERE UPPER(FIRST) = ‘John’
- Try some other scalar functions (like functions on date or timestamp columns or string functions on character columns) to see what is possible with function based indexes.
mailto :- ramyeles@in[dot]ibm[dot]com