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.
Example
The following example illustrates the bit mapping process.
The table queried contains the following set of rows.
|
Base Table |
|
|
|
|
RowID |
column_1 |
column_2 |
column_3 |
|
12,07 |
A |
B |
C |
|
22,03 |
A |
C |
C |
|
48,12 |
A |
B |
D |
|
63,15 |
B |
B |
C |
|
… |
… |
… |
… |
The query ANDs three equality conditions on three NUSI columns in the WHERE clause.
SELECT *
FROM Base_Table
WHERE column_1 = ‘A’
AND column_2 = ‘B’
AND column_3 = ‘C’;
The Optimizer evaluates the query and determines that bit mapping over the three columns is the least costly access plan.
The truth table looks like this.
|
NUSI Column Number
|
Value Required To Satisfy Condition
|
Base Table RowID List |
||||
|
12,07 |
22,03 |
48,12 |
63,15 |
… |
||
|
1 |
A |
1 |
1 |
1 |
0 |
… |
|
2 |
B |
1 |
0 |
1 |
1 |
… |
|
3 |
C |
1 |
0 |
0 |
0 |
… |
|
ANDed Sum: |
1 |
0 |
0 |
0 |
… |
|
Of the four rows examined in the example, only the row identified by RowID 12,07 qualifies and is returned to the requesting application.
Related Topics
Also see the topic “EXPLAIN Request Modifier” in SQL Data Manipulation Language, the chapter “Interpreting the Output of the EXPLAIN Request Modifier” in SQL Request and Transaction Processing, and Teradata Visual Explain User Guide.