17.05 - PRIMARY AMP - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

Use a primary AMP index (PA).

Rows are hash-distributed to AMPs for a column-partitioned (CP) table or join index. Column partition values are ordered on each AMP by an internal partition number and a row hash for a column-partitioned table or join index.

If you specify a PRIMARY AMP clause, you must specify a PARTITION BY clause that includes a column-partitioning level, either in the PRIMARY AMP clause or by itself in the index list.

The default Table Kind for a table with a PA is MULTISET. This cannot be changed for a table with a PA.

INDEX
Optionally, the INDEX keyword can be specified with AMP for readability.
index_name
Name of the primary AMP index. For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
(index_column_name)
The primary AMP index columns. For a composite primary index, index_column_name indicates a comma-separated list of all the index columns in parenthesis. You cannot specify the begin or end columns of a derived period column in a primary AMP index.

A row-level security constraint column cannot be a component of the primary AMP index.
You cannot define a primary AMP index on a column defined with the JSON or DATASET data type.

Example: Primary AMP Index with Column Partitioning

Following is an example of a CREATE TABLE statement for a column-partitioned (CP) table with a primary AMP index:

CREATE TABLE Sales1 (
    storeid         INTEGER NOT NULL,
    productid       INTEGER NOT NULL,
    salesdate       DATE FORMAT 'yyyy-mm-dd' NOT NULL,
    totalrevenue    DECIMAL(13,2),
    note            VARCHAR(256)
  )
  PRIMARY AMP INDEX (storeid, productid)
  PARTITION BY COLUMN;

By default for this table, each column is in a separate column partition and each of the column partitions have COLUMN format with autocompression.

For the following query, this allows a single-AMP step with column partition elimination. If this was a CP table with no primary index, all-AMP steps would be required. In step 1, three user column partitions and the delete column partition are accessed on this single AMP.

EXPLAIN
SELECT SUM(totalrevenue) FROM Sales1 s
WHERE s.storeid = 37 AND s.productid = 1466;

Explanation
---------------------------------------------------------------------------
  1) First, we do a single-AMP SUM step to aggregate from 4 column
     partitions of PLS.s by way of the primary AMP index "PLS.s.storeid =
     37, PLS.s.productid = 1466" with a condition of ("(PLS.s.productid =
     1466) AND (PLS.s.storeid = 37)").  Aggregate Intermediate Results are
     computed locally, then placed in Spool 3.  The size of Spool 3 is
     estimated with high confidence to be 1 row (23 bytes).  The estimated
     time for this step is 0.02 seconds.
  2) Next, we do a single-AMP RETRIEVE step from Spool 3 (Last Use) by
     way of the hash value of "PLS.s.storeid = 37, PLS.s.productid =
     1466" into Spool 1 (one-amp), which is built locally on that AMP.
     The size of Spool 1 is estimated with high confidence to be 1 row
     (36 bytes).  The estimated time for this step is 0.02 seconds.
  3) 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.04 seconds.

Example: Primary AMP Index with Column and Row Partitioning

The following is an example of a CREATE TABLE statement for a CP table with row partitioning and a primary AMP index:

CREATE TABLE Sales2 (
    storeid         INTEGER NOT NULL,
    productid       INTEGER NOT NULL,
    salesdate       DATE FORMAT 'yyyy-mm-dd' NOT NULL,
    totalrevenue    DECIMAL(13,2),
    note            VARCHAR(256)
  )
  PRIMARY AMP INDEX (storeid, productid)
  PARTITION BY (
      COLUMN,
      RANGE_N(salesdate BETWEEN DATE '2007-01-01' AND DATE '2014-12-31'
                                EACH INTERVAL '1' MONTH)
    );

By default for this table, each column is in a separate column partition and each of the column partitions has COLUMN format with autocompression. For the following query, this allows a single-AMP step with column and row partition elimination. If this was a CP table with no primary index, all-AMP steps would be required. In step 1, for one row partition, four user column partitions and the delete column partition are accessed on this single AMP.

EXPLAIN
SELECT SUM(totalrevenue) FROM Sales2 s
WHERE s.storeid = 37 AND s.productid = 1466 AND s.salesdate = DATE '2012-05-21';

Explanation
---------------------------------------------------------------------------
  1) First, we do a single-AMP SUM step to aggregate from 5 combined
     partitions (5 column partitions) of PLS.s by way of the primary AMP
     index "PLS.s.storeid = 37, PLS.s.productid = 1466" with a condition of
     ("(PLS.s.storeid = 37) AND ((PLS.s.productid = 1466) AND
     (PLS.s.salesdate = DATE '2012-05-21'))").  Aggregate Intermediate
     Results are computed locally, then placed in Spool 3.  The size of
     Spool 3 is estimated with high confidence to be 1 row (23 bytes).
     The estimated time for this step is 0.02 seconds.
  2) Next, we do a single-AMP RETRIEVE step from Spool 3 (Last Use) by
     way of the hash value of "PLS.s.storeid = 37, PLS.s.productid =
     1466" into Spool 1 (one-amp), which is built locally on that AMP.
     The size of Spool 1 is estimated with high confidence to be 1 row
     (36 bytes).  The estimated time for this step is 0.02 seconds.
  3) 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.04 seconds.

Primary AMP Index with Column Partitioning and ROW Format

Following is an example of a CREATE TABLE statement for a CP table with a primary AMP index that uses ROW format for the first and third column partitions (without applicable autocompression). The first column partition is a multicolumn partition. Autocompression is applied to the second column partition for totalrevenue.

CREATE TABLE Sales3 (
    ROW(storeid          INTEGER NOT NULL,
        productid        INTEGER NOT NULL,
        salesdate        DATE FORMAT 'yyyy-mm-dd' NOT NULL),
    COLUMN(totalrevenue  DECIMAL(13,2)),
    ROW(note             VARCHAR(256))
  )
  PRIMARY AMP INDEX (storeid, productid)
  PARTITION BY COLUMN;

For the following query, this allows a single-AMP step with column partition elimination. If this was a CP table with no primary index, all-AMP steps would be required. In step 1, two user column partitions are accessed since the partition being scanned has ROW format. The delete column partition does not need to be accessed in this case since the partition being scanned has ROW format.

EXPLAIN
SELECT SUM(totalrevenue) FROM Sales3 s
WHERE s.storeid = 37 AND s.productid = 1466;

Explanation
---------------------------------------------------------------------------
  1) First, we do a single-AMP SUM step to aggregate from 2 column
     partitions of PLS.s by way of the primary AMP index "PLS.s.storeid =
     37, PLS.s.productid = 1466" with a condition of ("(PLS.s.productid =
     1466) AND (PLS.s.storeid = 37)").  Aggregate Intermediate Results are
     computed locally, then placed in Spool 3.  The size of Spool 3 is
     estimated with high confidence to be 1 row (23 bytes).  The estimated
     time for this step is 0.02 seconds.
  2) Next, we do a single-AMP RETRIEVE step from Spool 3 (Last Use) by
     way of the hash value of "PLS.s.storeid = 37, PLS.s.productid =
     1466" into Spool 1 (one-amp), which is built locally on that AMP.
     The size of Spool 1 is estimated with high confidence to be 1 row
     (36 bytes).  The estimated time for this step is 0.02 seconds.
  3) 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.04 seconds.

Example: Primary AMP Index with Column Partitioning and a USI

This example defines a column-partitioned table with a primary AMP index. Note that a primary AMP index cannot be defined as unique but a unique (or nonunique) secondary index may be defined on the same columns as the primary AMP index. Each column is in its own partition with system-determined COLUMN format and autocompression except for o_comment which is in its own partition with user-specified ROW format and no autocompression. A 2-byte partitioning is defined. Row header compression and autocompression reduce the size of the table. This example assumes autocompression is the default for column partitions.

CREATE TABLE Orders (
    o_orderkey INTEGER NOT NULL,
    o_custkey INTEGER,
    o_orderstatus CHAR(1) CASESPECIFIC,
    o_totalprice DECIMAL(13,2) NOT NULL,
    o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL,
    o_shippriority INTEGER,
    ROW(o_comment VARCHAR(79)) NO AUTO COMPRESS )
  PRIMARY AMP (o_orderkey) PARTITION BY COLUMN,
  UNIQUE INDEX (o_orderkey);

Example: Primary AMP Index with Column Partitioning and USI (alternate syntax 1)

This example uses a different syntax to define the same table as the previous example. This example assumes autocompression is the default for column partitions.

CREATE TABLE Orders (
    o_orderkey INTEGER NOT NULL,
    o_custkey INTEGER,
    o_orderstatus CHAR(1) CASESPECIFIC,
    o_totalprice DECIMAL(13,2) NOT NULL,
    o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL,
    o_shippriority INTEGER,
    o_comment VARCHAR(79) )
  PRIMARY AMP INDEX (o_orderkey)
  PARTITION BY COLUMN ALL BUT (ROW(o_comment) NO AUTO COMPRESS),
  UNIQUE INDEX (o_orderkey);

Example: Primary AMP Index with Column Partitioning and USI (alternate syntax 2)

This example uses a different syntax to define the same table as the previous example. Note that columns not listed in the grouping clause are grouped together into a column partition by default but, since o_shippriority is the only one not listed in the COLUMN grouping clause, each column is in its own single-column partition as in the previous example.

CREATE TABLE Orders (
    o_orderkey INTEGER NOT NULL,
    o_custkey INTEGER,
    o_orderstatus CHAR(1) CASESPECIFIC,
    o_totalprice DECIMAL(13,2) NOT NULL,
    o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL,
    o_shippriority INTEGER,
    o_comment VARCHAR(79) )
  PRIMARY AMP (o_orderkey)
  PARTITION BY COLUMN (
      o_orderkey, o_custkey, o_orderstatus, o_totalprice,
      o_orderdate, ROW o_comment NO AUTO COMPRESS ),
  UNIQUE INDEX (o_orderkey);