15.00 - AND Equality Condition - Teradata Database

Teradata Database Design

Teradata Database
User Guide

AND Equality Condition

Consider the following simple query with an ANDed predicate set.

     SELECT last_name, first_name, salary_amount
     FROM employee
     WHERE department_number = 500
     AND   job_code = 2147;

Whether the Optimizer includes these NUSIs in its query plan varies depending on their relative selectivity.


IF …

THEN the Optimizer …

only one of the NUSIs is strongly selective

uses that NUSI alone in its query plan.

both NUSIs are weakly selective individually, but strongly selective when combined

creates a bit-mapped intersection of their common rowIDs and uses them in its query plan (see “NUSI Bit Mapping” on page 479).

both NUSIs are weakly selective both when examined individually and when combined

selects neither for its query plan and instead specifies a full‑table scan when no other index option provides a less costly estimate.