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.
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;
Result:
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';
Result:
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;
Result:
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);