17.00 - PRIMARY INDEX - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

The primary index definition.

The primary index is used by the hashing algorithm to distribute table rows across the AMPs.

Definitions that do not explicitly specify PRIMARY INDEX (column_list) or NO PRIMARY INDEX use the setting of the DBS Control parameter PrimaryIndexDefault, except for column-partitioned tables, which are always defined by default with NO PRIMARY INDEX regardless of the PrimaryIndexDefault setting. For information about DBS Control parameters, see Teradata Vantage™ - Database Utilities , B035-1102 .

For column-partitioned tables, the default table kind is MULTISET. This cannot be changed for a column-partitioned table.

For information about primary index defaults, see Teradata Vantage™ - Database Design, B035-1094.

index_column_name
A column in the column set that defines a partitioned primary index.
If you specify more than one column name, the index is created on the combined values of each column named. A maximum of 64 columns can be specified for an index, and a maximum of 32 secondary indexes can be created for one table. A multicolumn NUSI defined with an ORDER BY clause counts as two consecutive indexes in this calculation.
You can define a primary index on a column that has a UDT data type.
You cannot define a primary index on a column defined with any of the following data types:
  • BLOB
  • CLOB
  • LOB UDT
  • VARIANT_TYPE
  • ARRAY
  • VARRAY
  • Period
  • XML
  • Geospatial
  • JSON
  • DATASET
You cannot define a primary index on a column that is a row-level security constraint.
index_name
Optional name for the index.
For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
UNIQUE
The primary index is to be unique.
Any secondary indexes and the primary index can be defined to be unique with the exception of a PI whose definition does not include all of the partitioning columns, if any.

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

In this example, a unique primary index is defined on the empno column, and a unique secondary index is defined on the name column. Because the empno and name columns must always contain a value, they are assigned the NOT NULL attribute.

For display purposes, the values for sex , 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, 
       sex       CHARACTER UPPERCASE NOT NULL 
                 CHECK (sex 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.

CYCLE Description
Not specified. Specification defaults to NO CYCLE.
Specified. Numbering sequence generated is as follows: 1,000, 1,010, 1,020 … 300,000, 0, 10, 20, 30 …

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 might not be in strict increments of 10 from the last generated number. The next available number stored in DBC.IdCol.AvailValue for that particular 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 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 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 single AMP. While this can provide efficient access, row header compression and autocompression might not be as effective as a CP table with a primary AMP index, especially if the primary index is nearly 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;