DB2 for I: Derived-key and Sparse indexes

Derived-key Index (aka Function based Index)

1. Both Radix and EVI index support Derived-key Index.
2. Available OS version 6.1 onward.
3. Index’s key is output of a SQL function on the base table column.

CREATE INDEX upper_name on abc(UPPER(name))

1. Now the index will contain all the keys in upper case.
2. Its much better than using this function in a select statement like

SELECT * FROM abc where UPPER(name) = “somename”

Sparse Index

1. Both Radix and EVI index support Derived-key Index.
2. Available OS version 6.1 onward.
3. The “CREATE INDEX” statement use a “WHERE” clause to limit the number of records from the base table.
   1. Its like Select-Omit criteria in logical files.
4. The index contain the keys only for a subset of the data.

CREATE INDEX upper_name on abc(name) where age > 25