15.00 - Computing NUSI Bit Maps - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Computing NUSI Bit Maps

We use the following previously described query to example how NUSI bit maps are computed.

     SELECT *
     FROM employee_table
     WHERE salary_amount > 20000
     AND   sex_code = ‘M’
     AND   full_time = ‘FT’;

Recall the truth table for the AND operator.

 
 

0

+

0

=

0

 

0

+

1

=

0

 

1

+

0

=

0

 

1

+

1

=

1

The following process illustrates the method for computing NUSI bit maps. The actions described occur concurrently on all AMPs.

1 The literals 20,000, M, and FT are used to access the corresponding rows from the index subtables.

2 For n-1 indexes, each AMP creates separate bit maps of all 0 values.

In this case, n-1 is 2 because there are three NUPIs defined, one for each condition in the WHERE clause.

3 Using a proprietary algorithm, the AMPs equate each base table rowID in each qualifying NUPI row with a single bit in the map, and each such map bit is turned ON.

4 The resulting bit maps are ANDed together to form the final bit map.

5 Each base table rowID in the remaining NUSI is equated to a single bit in the final bit map, and the state of that map is checked.

 

 

IF the map bit is …

THEN …

 

OFF (0)

no action is taken and the next map bit is checked.

 

ON (1)

the rowID is used to access the table row and all remaining, or residual, conditions are applied to that row.