Index

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

 


IBM i developer.

View Comments
There are currently no comments.