Using sparse indexes

SQL indexes can be created using WHERE selection predicates. These indexes can also be referred to as sparse indexes. The advantage of a sparse index is that fewer entries are maintained in the index. Only those entries matching the WHERE selection criteria are maintained in the index.

In general, the query WHERE selection must be a subset of the sparse index WHERE selection in order for the sparse index to be used.

Here is a simple example of when a sparse index can be used:

CREATE INDEX MYLIB/SPR1 on MYLIB/T1 (COL3)
WHERE  COL1=10 and COL2=20

SELECT COL1, COL2, COL3, COL4
FROM   MYLIB/T1
WHERE  COL1=10 and COL2=20 and COL3=30

It is recommended that the WHERE selection in the sparse index is kept as simple as possible. The more complex the WHERE selection, the more difficult it becomes to match the sparse index WHERE selection to the query WHERE selection. Then it is less likely that the sparse index is used. The CQE optimizer does not support sparse indexes. It does support select/omit logical files however. The SQE optimizer matches the CQE optimizer in its support for select/omit logical files and has nearly full support for sparse indexes.