Examples: PRIMARY INDEX - Teradata Vantage

Example: Specifying a UPI and a USI on the Same Table

A unique primary index is defined on the empno column, and a unique secondary index is defined on the name column. The empno and name columns must contain a value, and are therefore assigned the NOT NULL attribute.

For display purposes, the values for gender , race , and mstat are defined using the UPPERCASE column attribute.

     CREATE TABLE employee (
       emp_no    SMALLINT FORMAT '9(5)' 
                 CHECK (emp_no >= 10001 AND emp_no <= 32001) NOT NULL, 
       name      VARCHAR(12) NOT NULL, 
       dept_no   SMALLINT FORMAT '999' 
                 CHECK (deptno >= 100 AND dept_no <= 900), 
       job_title VARCHAR(12), 
       salary    DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99' 
                 CHECK (salary >= 1.00 AND salary <= 999000.00), 
       yrs_exp   BYTEINT FORMAT 'Z9' 
                 CHECK(yrs_exp >= -99 AND yrs_exp <=99), 
       dob       DATE FORMAT 'MMMbDDbYYYY' NOT NULL, 
       gender    CHARACTER UPPERCASE NOT NULL 
                 CHECK (gender IN ('M','F')),
       race      CHARACTER UPPERCASE, 
       m_stat    CHARACTER UPPERCASE 
                 CHECK (m_stat IN ('S','M','D','U')),
       edlev     BYTEINT FORMAT 'Z9' 
                 CHECK (ed_lev >=0 AND ed_lev <=22) NOT NULL, 
       h_cap     BYTEINT FORMAT 'Z9' 
                 CHECK (h_cap >= -99 AND h_cap <= 99)
     UNIQUE PRIMARY INDEX (emp_no), 
     UNIQUE INDEX (name);

Example: Specifying an Identity Column as a Unique Primary Index

This example creates an identity column that is used as the primary index for the table. Values for idnum are generated starting with 1,000 and incrementing by 10 until the specified MAXVALUE of 300,000 is reached.

The numbers generated for rows inserted into the table are the following: 1,000, 1,010, 1,020, …, 300,000 and then an error is returned when the maximum value is reached.

    CREATE TABLE t (
      idnum INTEGER GENERATED ALWAYS AS IDENTITY
                   (START WITH 1000
                    INCREMENT BY 10
                    MINVALUE 0
                    MAXVALUE 300000),
      phone INTEGER)
    UNIQUE PRIMARY INDEX(idnum);

This example does not specify a CYCLE parameter.

No warning message is returned when numbers are cycled. However, if an idnum value is still in existence when an attempt is made to recycle it, the request aborts because idnum is the UPI for the table, so all its values must be unique.

If there is a system restart, the numbering sequence may not be in strict increments of 10 from the last generated number. The next available number stored in DBC.IdCol.AvailValue for that table is retrieved and numbering begins from there. Unassigned numbers last reserved and stored in the cache are lost.

Example: Specifying an Identity Column as a Unique Primary Index Using CYCLE

This example also creates an identity column that is used as the primary index for the table. Values for idnum are generated starting with 1,000 and decremented by 10 until the specified MINVALUE of 1 is reached. Because CYCLE is specified, no error is returned and numbering proceeds as follows: 1,000, 990, 980 … 10, 100,000, 99,990, 99,980 …

However, if an idnum value is still in existence when an attempt is made to recycle it, the request aborts because idnum is the UPI for the table, so all its values must be unique.

CREATE TABLE t (
  idnum INTEGER GENERATED ALWAYS AS IDENTITY
               (START WITH 1000
                INCREMENT BY -10
                MINVALUE 1
                MAXVALUE 100000 CYCLE),
  phone INTEGER)
UNIQUE PRIMARY INDEX(idnum);

Example: Specifying a Primary Index and a Primary Key

The request in this example specifies both a primary index and primary key.

The Primary Key (column_1) is mapped to a unique secondary index. The UNIQUE constraint on column_3 also is mapped to a USI. These two unique secondary indexes cannot be null. The Unique Primary Index (column_2) can be null; however, no more than one row in the table can have a null UPI value.

CREATE TABLE good_5 (
  column_1 INTEGER NOT NULL PRIMARY KEY,
  column_2 INTEGER,
  column_3 INTEGER NOT NULL UNIQUE,
  column_4 INTEGER)
UNIQUE PRIMARY INDEX (column_2);

Example: Primary Index with Column Partitioning

Following is an example of a CREATE TABLE statement for a CP table with a primary index:

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

By default, each column is in a separate column partition and each column partition has a COLUMN format with autocompression. For the following query, this allows a single-AMP step with hashed access on this AMP and column partition elimination. In step 1, for a single hash value, three user column partitions and the delete column partition are accessed on this AMP. While this can provide efficient access, row header compression and autocompression may be less effective than a CP table with a primary AMP index, especially if the primary index is uncommon or unique.

EXPLAIN
SELECT SUM(totalrevenue) FROM Sales4 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 index "PLS.s.storeid =
     37, PLS.s.productid = 1466" with no residual conditions.  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: Unique Primary Index with Column Partitioning

This example defines a column-partitioned table with a unique primary index. Each column is in its own partition with system-determined ROW format. A 2-byte partitioning is defined. By default, a column-partitioned table is a multiset table. MULTISET can be explicitly specified but SET must not be specified. The number of bytes per row is increased by 86 due to the row headers per column partition. 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) )
  UNIQUE PRIMARY INDEX (o_orderkey) PARTITION BY COLUMN;