16.20 - Partitioned and Nonpartitioned Primary Indexes - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

Primary indexes for global temporary, volatile, and standard base tables can be partitioned or nonpartitioned. Nonpartitioned NoPI tables, with the exception of column-partitioned tables, cannot be partitioned because they have no primary index to partition. See Column-Partitioned Tables.

An nonpartitioned primary index is the traditional primary index by which rows are assigned to AMPs. Apart from maintaining their storage in row hash order, no additional assignment processing of rows is performed once they are hashed to an AMP.

A partitioned primary index (PPI) permits rows to be assigned to user-defined data partitions on the AMPs, enabling enhanced performance for range queries that are predicated on primary index values. For details, see Teradata Vantage™ - Database Design, B035-1094 and Teradata Vantage™ SQL Request and Transaction Processing, B035-1142.

You can define partitioning on a single level or on multiple levels. A PPI defined on multiple levels is referred to as a multilevel PPI, or MLPPI.

Depending on the number of combined partitions for a table, its partition numbers consume either 2 bytes or 8 bytes in the row header, as the following table specifies.

IF a table has this many combined partitions … Its partition number consumes this many bytes in the row header …
  65, 535 2
> 65,535 8

Single-level column partitioning with no ADD option also consumes 2 bytes in the row header.

Partition assignment is based on how the partitioning expression is defined. The partitioning expressions for multilevel partitioning must be defined using only CASE_N or RANGE_N expressions in any combination. The functions CASE_N and RANGE_N are designed specifically to support simple partitioning expressions. See Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145. But, you can write any valid SQL expression as a partitioning expression for single-level partitioning, with the following exclusions:
  • Comparison of CHARACTER or CHARACTER SET GRAPHIC data involving columns or expressions using the Kanji1 or KanjiSJIS server character sets.
  • Any kind of user-defined function
  • Aggregate functions
  • Ordered analytical functions
  • Built-in functions
  • The RANDOM function
  • The HASHAMP and HASHBAKAMP functions

    HASHROW and HASHBUCKET are permitted.

  • The system-derived PARTITION and PARTITION#L n columns
  • Set operators
  • Subqueries
  • Columns having a BLOB, CLOB, or Geospatial data type.
You can base a partitioning expression for single-level partitioning on any of the following general forms.
  • Direct partitioning on a numeric column
  • Expressions based on 1 or more columns
  • Expressions based on the CASE_N function
  • Expressions based on the RANGE_N function

See Teradata Vantage™ - Database Design, B035-1094 for details on the various usage considerations for each of these partitioning strategies.

Partitioning expressions for multilevel partitioning can be based only on the following general forms in any combination.
  • Expressions based on the CASE_N function (see Teradata Vantage™ - Database Design, B035-1094 for details).
  • Expressions based on the RANGE_N function (see Teradata Vantage™ - Database Design, B035-1094 for details).

The following table presents the intended use of the CASE_N and RANGE_N functions for partitioning expressions.

Use this function … To define a mapping …
CASE_N between conditions to INTEGER numbers.
RANGE_N of ranges of INTEGER or DATE values to INTEGER numbers.

The partitioning expressions you can define for partitioning a table have certain restrictions regarding the data types you can specify within them and with respect to the data type of the result of the function.

The following table summarizes these restrictions.

Data Type PARTITION BY
RANGE_N CASE_N Expression
ARRAY

VARRAY

N N N
BIGINT Y X I
BLOB N N N
BYTE X X X
BYTEINT Y X I
CHARACTER Y X I
CLOB N N N
DATE Y X I
DECIMAL

NUMERIC

X X I
NUMBER (exact form) X X I
DOUBLE PRECISION

FLOAT

REAL

X X I
NUMBER (approximate form) X  X I
GRAPHIC N   X N
INTEGER Y X Y
INTERVAL YEAR X X I
INTERVAL YEAR TO MONTH X X X
INTERVAL MONTH  X  X I
INTERVAL DAY  X X I
INTERVAL DAY TO HOUR X X X
INTERVAL DAY TO SECOND X X X
INTERVAL SECOND  X X X
LONG VARCHAR Y X I
LONG VARCHAR CHARACTER SET GRAPHIC N N N
PERIOD

The BEGIN and END bound functions are valid in a partitioning expression when they are defined on a valid PERIOD column and the result can be cast implicitly to a numeric data type.

N X N
SMALLINT Y X I
TIME X X X
TIME WITH TIME ZONE X X X
TIMESTAMP Y X X
TIMESTAMP WITH TIME ZONE Y X X
UDT N N N
VARBYTE X X X
VARCHAR Y X I
VARGRAPHIC N N N
JSON N N N
XML N N N

The following table explains the abbreviations used in the previous table.

Key
Symbol Definition
I Valid for a partitioning expression.

If the type is also the data type of the result, then it must be such that it can be cast to a valid INTEGER value.

N Not valid for a partitioning expression.

If the partitioning expression is defined using a CASE_N function, then this type is not valid for the CASE_N condition.

X Valid for a partitioning expression, but cannot be the data type of the result of the expression.

If the partitioning expression is defined using a CASE_N function, then this type is valid for the CASE_N condition.

Y Valid for a partitioning expression and valid as the data type of the result of the partitioning expression.