Sparse indexes

You can use the SQL CREATE INDEX statement to create a sparse index using SQL selection predicates.

Last release users were given the ability to use the SQL CREATE INDEX statement to create a sparse index using a WHERE condition. With this support, the query optimizer recognizes and considers sparse indexes during its optimization. If the query WHERE selection is a subset of the sparse index WHERE selection, then the sparse index is used to implement the query. Use of the sparse index usually results in improved performance.

Examples

In this example, the query selection is a subset of the sparse index selection and an index scan over the sparse index is used. The remaining query selection (COL3=30) is executed following the index scan.

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

In this example, the query selection is not a subset of the sparse index selection and the sparse index cannot be used.

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

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