15.00 - Restrictions for When Bit Mapping Is Used - Teradata Database

Teradata Database Design

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

Restrictions for When Bit Mapping Is Used

Teradata Database only performs NUSI bit mapping when weakly selective indexed conditions are ANDed. 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 the weakly selective ORed conditions are ANDed with other weakly selective conditions, but the composite selectivity is high, the Optimizer may choose to use a composite NUSI or a single-column NUSI.

 

IF all conditions are ANDed together and …

AND their composite selectivity is …

THEN the Optimizer…

one index is strongly selective

 

selects it alone and applies the remaining constraints to the selected rows as residual constraints.

all of the indexes are weakly selective

also weakly selective

performs a full‑table scan and applies the conditions to every row.

all of the indexes are weakly selective

strongly selective

can instruct each AMP to construct bit maps to determine which rowIDs their local NUSI rows have in common and then access just those rows, applying the conditions to them exclusively.

For example, consider the following SELECT statement with three WHERE conditions, each of which has a weakly selective NUSI defined on its column.

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

Suppose that the index on salary_amount has 30% selectivity, the index on sex_code 50% selectivity, and the index on full_time 70% selectivity. The individual selectivity of these indexes is very weak.

If the overlap among their selectivities is such that the effect of combining their relative weaknesses results in a significant strength, then the Optimizer instructs the AMPs to use bit mapping to do just that, and the end result is vastly improved performance of the retrieval in comparison with a full‑table scan.

In this example, the overlap selectivity is 9%, which is both significantly better than any of the individual selectivities of the NUSIs in the query and typically better than a full‑table scan. This assumes fewer than 10 rows per data block. The figure must be adjusted for larger data blocks that can contain many more rows.