Single Partition Scans and BEGIN/END Bound Functions | Teradata Vantage - 17.10 - Single Partition Scans and BEGIN/END Bound Functions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

An access plan can scan a single row partition when there are equality constraints specified on BEGIN or END bound functions in the WHERE clause of a SELECT request and a 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 the 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 two 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';
The following shows a portion of the EXPLAIN output:
...
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.
...

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';
...
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.
...

The following SELECT request uses a single-partition scan to access the rows:

EXPLAIN
SELECT *
FROM t12
WHERE END(b)=DATE '2006-02-03';
...
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.
...

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';
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.
...