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

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

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.

Rules for Performing Single-Partition Scans

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.

Examples of Single-Partition BEGIN or END Bound Function Scans on Partitioned Tables

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 
   confidenceto be 1 row (56 bytes). The estimated time for this step 
   is 0.03seconds.
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.