DB2 for I: Derived-key and Sparse indexes
Derived-key Index (aka Function based Index)
- Both Radix and EVI index support Derived-key Index.
- Available OS version 6.1 onward.
- Index’s key is output of a SQL function on the base table column.
CREATE INDEX upper_name on abc(UPPER(name))
- Now the index will contain all the keys in upper case.
- Its much better than using this function in a select statement like
SELECT * FROM abc where UPPER(name) = “somename”
Sparse Index
- Both Radix and EVI index support Derived-key Index.
- Available OS version 6.1 onward.
- The “CREATE INDEX” statement use a “WHERE” clause to limit the number of records from the base table.
- Its like Select-Omit criteria in logical files.
- The index contain the keys only for a subset of the data.
CREATE INDEX upper_name on abc(name) where age > 25