15.00 - Parameterized Queries and Single-Table Join Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Parameterized Queries and Single‑Table Join Indexes

The following parameterized query can use this join index because the Optimizer knows that the matching rows are contained in the index because the WHERE clause predicate in the query is a conjunction between the covered term zipcode and the parameterized term :N.

     USING (N VARCHAR(32)) 
     SELECT pid, name 
     FROM tp1
     WHERE zipcode IN (54455, 53066) 
     AND   name = :N;

The explanation for this query looks like the following report:

     1) First, we lock a distinct CURT."pseudo table" for read on a
        RowHash to prevent global deadlock for CURT.TP1_JI.
     2) Next, we lock CURT.TP1_JI for read.
     3) We do an all-AMPs RETRIEVE step from CURT.TP1_JI by way of an
        all-rows scan with a condition of ("((CURT.TP1_JI.zipcode = 53066 ) 
        OR (CURT.TP1_JI.zipcode = 54455 )) AND (CURT.TP1_JI.name = :N)") 
        into Spool 1 (group_amps), which is built locally on the AMPs.
        The size of Spool 1 is estimated with no confidence to be 1 row (
        64 bytes).  The estimated time for this step is 0.03 seconds.
     4) Finally, we send out an END TRANSACTION step to all AMPs involved
        in processing the request.
     -> The contents of Spool 1 are sent back to the user as the result of
        statement 1.  The total estimated time is 0.03 seconds.

Only prototyping can determine which is the better design for a given set of tables, applications, and hardware configuration.