Using NUSIs For Complex Conditional Expressions
Complex conditional expressions can be based on non-unique secondary indexes. Bit mapping is often used to solve such expressions when they are applied to a very large table. NUSI bit mapping can be used if the following statements are both true for the conditions under consideration.
Take the following request as an example.
WHERE NUSI_1 = ’condition_1’
AND NUSI_2 = ’condition_2’
AND NUSI_3 = ’condition_3’
AND NUSI_4 = ’condition_4’;
To resolve this request, a bit map is built for n-1 referenced indexes (see stage 2 in the process documented on the following page). For this example, n=4, so the system builds three bit maps. In each bit map, every qualifying data row bit is turned ON.
The bit maps are ANDed to form one large bit map, which is held in a spool file. The ON bits are used to access the result data rows directly.
The most selective index of the four accesses rows based on whether the bit is ON for the rowID in the ANDed bit map for the rowID from the previous NUSI.