15.00 - OR Equality Condition - Teradata Database

Teradata Database Design

Teradata Database
User Guide

OR Equality Condition

Consider the following simple query with an ORed predicate set.

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

If both columns are indexed, as they are in this particular case, then a possible Optimizer plan would include the following stages.

1 Use their hash codes to find the indexed rows in both NUSI tables.

2 Retrieve the rowIDs of any qualifying base table rows and place them into single spool file.

3 Sort the rowIDs to eliminate duplicates.

4 Use the resulting spool to access the qualified base table rows.

This processing could be done on one NUSI if the set of ORed predicates is specified on the same indexed column, or extended to more than two NUSIs, of course, but the likelihood of that being a profitable exercise for the Optimizer to undertake vanishes as the number of rows that qualify the ORed predicate set increases. A full‑table scan could take less time to perform than looking up the NUSI or multiple NUSIs for each value in the ORed predicate set, spool file production, and duplicate elimination required by this method.