Single Partition Scans and BEGIN/END Bound Functions | Teradata Vantage - Single Partition Scans and BEGIN/END Bound Functions - Analytics Database - Teradata Vantage

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