15.00 - Using NUSIs For Complex Conditional Expressions - Teradata Database

Teradata Database Design

Teradata Database
User Guide

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.

  • There are at least two NUSI equality conditions.
  • All the NUSI conditions are ANDed.
  • Take the following request as an example.

         SELECT COUNT(*) 
         FROM LargeTable
         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.