15.10 - Single Partition Scans and BEGIN/END Bound Functions - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

Teradata Database access plans scan only single row partitions when there are equality constraints specified on BEGIN or END bound functions in the WHERE clause of a SELECT request and the row partitioning level of the table is defined on those functions.

The following table explains the rules for performing single‑partition scans in this situation.

 

WHEN a primary index is row partitioned on …

AND if the WHERE clause of a primary index retrieval from the table specifies …

THEN Teradata Database …

BEGIN bound function

 

 

an equality constraint on the BEGIN function

accesses only a single row partition.

an equality constraint on the END function

does not access only a single row partition.

an equality constraint on both the BEGIN and END functions

accesses only a single row partition.

END bound function

 

 

an equality constraint on the BEGIN function

does not access only a single row partition.

an equality constraint on the END function

accesses only a single row partition.

an equality constraint on both the BEGIN and END functions

accesses only a single row partition.

both a BEGIN bound function and an END bound function

 

 

an equality constraint on only the BEGIN function

does not access only a single row partition.

an equality constraint on only the END function

does not access only a single row partition.

both the BEGIN and END functions

accesses only a single row partition.

Assume that you define the following 2 tables.

     CREATE SET TABLE t11 (
       a INTEGER,
       b PERIOD(DATE))
     PRIMARY INDEX(a)
     PARTITION BY CAST ((BEGIN(b)) AS INTEGER);
 
     CREATE SET TABLE t12 (
       a INTEGER,
       b PERIOD(DATE))
     PRIMARY INDEX(a)
     PARTITION BY CAST((END(b)) AS INTEGER);
 

The relevant EXPLAIN phrase text in the reports for the examples that follow is highlighted in boldface type.

The following SELECT request uses an single‑partition scan to access the rows.

     EXPLAIN 
     SELECT * 
     FROM t11 
     WHERE BEGIN(b)=DATE '2005-02-03'; 
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct df2."pseudo table" for read on a
     RowHash to prevent global deadlock for df2.t11.
  2) Next, we lock df2.t11 for read.
  3) We do an all-AMPs RETRIEVE step from a single partition of
     df2.t11 with a condition of ("df2.t11.b = PERIOD (DATE '2005-02-03', 
    DATE '2005-02-03'")with a residual condition of ("(BEGIN(df2.t11.b ))
     = DATE '2005-02-03'") 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 (56 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.
 

The following SELECT request uses an all‑rows, all‑partitions scan to access the rows because the predicate and partitioning expression are defined on different bounds.

     EXPLAIN 
     SELECT * 
     FROM t11 
     WHERE END(b)=DATE '2005-02-03';
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct df2."pseudo table" for read on a
     RowHash to prevent global deadlock for df2.t11.
  2) Next, we lock df2.t11 for read.
  3) We do an all-AMPs RETRIEVE step from df2.t11 by way of an
     all-rows scan with a condition of ("(END(df2.t11.b ))= DATE
     '2005-02-03'") 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 (56 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.
 

The following SELECT request uses a single‑partition scan to access the rows.

     EXPLAIN 
     SELECT * 
     FROM t12 
     WHERE END(b)=DATE '2006-02-03';
 
Explanation
--------------------------------------------------------------------------
  1) First, we lock a distinct df2."pseudo table" for read on a
     RowHash to prevent global deadlock for df2.t12.
  2) Next, we lock df2.t12 for read.
  3) We do an all-AMPs RETRIEVE step from a single partition of
     df2.t12 with a condition of ("df2.t12.b = PERIOD (DATE '2006-02-03'- 
     INTERVAL '1' DAY, DATE'2006-02-03')") with a residual condition of 
     ("(END(df2.t12.b ))= DATE '2006-02-03'") 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 (56 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.
 

The following SELECT request uses a single‑partition scan to access the rows.

     EXPLAIN 
     SELECT * 
     FROM t12 
     WHERE a=1 
     AND   END (b)=DATE '2006-02-03';
 
Explanation
---------------------------------------------------------------------------
  1) First, we do a single-AMP RETRIEVE step from a single partition of
     df2.t12 by way of the primary index "df2.t12.a = 1,
     df2.t12.b = PERIOD (DATE '2006-02-03'-INTERVAL '1' DAY, DATE '2006-02-03')" 
     with a residual condition of ("((END(df2.t12.b ))= DATE '2006-02-03') AND
     (df2.t12.a = 1)") into Spool 1 (one-amp), which is built locally on that AMP.
     The size of Spool 1 is estimated with low confidence to be 1 row (56 bytes).
     The estimated time for this step is 0.02 seconds.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.02 seconds.