Bit mapping is a technique used by the Optimizer to effectively link several low selectivity indexes in a way that creates a result that drastically reduces the number of base rows that must be accessed to retrieve the desired data. The process determines common rowIDs among multiple NUSI values by means of the logical intersection operation. The method is explained in more detail in the following sections.
Restrictions for When Bit Mapping Is Used
Teradata Database only performs NUSI bit mapping when low selectivity indexed conditions are ANDed but their composite selectivity is high. When WHERE conditions are connected by a logical OR, the Optimizer typically performs a full-table scan and applies the ORed conditions to every row without considering an index.
Note that this is not always the case. If low selectivity conditions ORed conditions are ANDed with other low selectivity conditions, but the composite selectivity is high, the Optimizer may choose to use a composite NUSI or a single-column NUSI.
Computing NUSI Bit Maps
We use the following query to show how NUSI bit maps are computed.
SELECT * FROM employee_table WHERE salary_amount > 20000 AND sex_code = ‘M’ AND full_time = ‘FT’;
The following process illustrates the method for computing NUSI bit maps. The actions described occur concurrently on all AMPs.
- The literals 20,000, M, and FT are used to access the corresponding rows from the index subtables.
- 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 NUSIs defined, one for each condition in the WHERE clause.
- The AMPs equate each base table rowID in each qualifying NUSI row with a single bit in the map, and each such map bit is turned ON.
- The resulting bit maps are ANDed together to form the final bit map.
- 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. |
Determining If Bit Mapping Is Being Used
To determine whether bit mapping is being used for your NUSIs, use the EXPLAIN request modifier and examine the reports it produces for your queries.
See “EXPLAIN Request Modifier” in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146, and the section “Interpreting the Output of the EXPLAIN Request Modifier” in Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.