15.00 - Queries Using BETWEEN, LESS THAN, GREATER THAN, or LIKE Operators - Teradata Database

Teradata Database Design

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

Queries Using BETWEEN, LESS THAN, GREATER THAN, or LIKE Operators

Assume that a query involves BETWEEN, LESS THAN, GREATER THAN, or LIKE operations on a single NUSI as in the following examples.

     CREATE INDEX (hire_date) ON employee;
 
     SELECT last_name, first_name, hire_date
     FROM employee
     WHERE hire_date BETWEEN 880101 AND 881231;
 
     SELECT last_name, first_name, hire_date
     FROM employee
     WHERE hire_date < 880101;
 
     SELECT last_name, first_name, hire_date
     FROM employee
     WHERE hire_date > 881231;

The following process outlines the approach taken to respond to these requests. This technique can be very efficient for a selective ANDed predicate when there is a composite NUSI that contains all the predicate columns. Optimizer chooses to use the NUSI if the savings in the number of data blocks to read is greater than the overhead of scanning the NUSI and writing and reading a rowID spool.

1 The Optimizer determines whether it is more efficient to do a full‑table scan of the base table rows or to scan the secondary index subtable to get the rowIDs of the qualifying base table rows. Note that ordering the NUSI values can reduce the index scan time.

For purposes of this example, assume that the NUSI access method is the more efficient technique to pursue.

2 The access plan specifies to place those rowIDs into a spool file.

3 The access plan specifies to use the resulting rowIDs to access the base table rows.