Sparse index examples

In example S1, the query selection is a subset of the sparse index selection and consequently 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 example S2, 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

In example S3, the query selection exactly matches the sparse index selection and an index scan over the sparse index can 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 and COL3=30

In example S4, the query selection is a subset of the sparse index selection and an index scan over the sparse index can be 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 example S5, 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

In example S6, the query selection exactly matches the sparse index selection and an index scan over the sparse index can be used. The query selection is executed following the index scan to eliminate excess records from the sparse index.

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

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

In example S7, the query selection is a subset of the sparse index selection and an index scan over the sparse index can be used. The query selection is executed following the index scan to eliminate excess records from the sparse index.

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

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

In example S8, 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 or COL2=20

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

In the next example S9, the constant 'MN' was replaced by a parameter marker for the query selection. The sparse index had the local selection of COL1='MN' applied to it when it was created. The sparse index matching algorithm matches the parameter marker to the constant 'MN' in the query predicate COL1 =?. It verifies that the value of the parameter marker is the same as the constant in the sparse index; therefore the sparse index can be used.

CREATE INDEX MYLIB/SPR1 on MYLIB/T1 (COL3)
WHERE  COL1='MN' or COL2='TWINS'

SELECT COL1, COL2, COL3, COL4
FROM   MYLIB/T1
Where Col3='WIN' and (Col1=? or Col2='TWINS')

In the next example S10, the keys of the sparse index match the order by fields in the query. For the sparse index to satisfy the specified ordering, the optimizer must verify that the query selection is a subset of the sparse index selection. In this example, the sparse index can be used.

CREATE INDEX MYLIB/SPR1 on MYLIB/T1 (COL1, COL3)
WHERE  COL1='MN' or COL2='TWINS'

SELECT COL1, COL2, COL3, COL4
FROM   MYLIB/T1
Where Col3='WIN' and (Col1='MN' or Col2='TWINS')
ORDER  BY COL1, COL3

In the next example S11, the keys of the sparse index do not match the order by fields in the query. But the selection in sparse index T2 is a superset of the query selection. Depending on size, the optimizer might choose an index scan over sparse index T2 and then use a sort to satisfy the specified ordering.

CREATE INDEX MYLIB/SPR1 on MYLIB/T1 (COL2, COL4)
WHERE  COL1='MN' or COL2='TWINS'

SELECT COL1, COL2, COL3, COL4
FROM   MYLIB/T1
Where Col3='WIN' and (Col1='MN' or Col2='TWINS')
ORDER  BY COL1, COL3

The next example S12 represents the classic optimizer decision: is it better to do an index probe using index IX1 or is it better to do an index scan using sparse index SPR1? Both indexes retrieve the same number of index entries and have the same cost from that point forward. For example, both indexes have the same cost to retrieve the selected records from the dataspace, based on the retrieved entries/keys.

The cost to retrieve the index entries is the deciding criteria. In general, if index IX1 is large then an index scan over sparse index SPR1 has a lower cost to retrieve the index entries. If index IX1 is rather small then an index probe over index IX1 has a lower cost to retrieve the index entries. Another cost decision is reusability. The plan using sparse index SPR1 is not as reusable as the plan using index IX1 because of the static selection built into the sparse selection.

CREATE INDEX MYLIB/IX1 on MYLIB/T1 (COL1, COL2, COL3)

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

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