15.00 - Partitioned and Unpartitioned Primary Indexes - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Partitioned and Unpartitioned Primary Indexes

Primary indexes for global temporary, volatile, and standard base tables can be partitioned or unpartitioned. Unpartitioned NoPI tables, with the exception of column‑partitioned tables (see “Column‑Partitioned Tables” on page 577), which cannot be partitioned because they have no primary index to partition.

An unpartitioned 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 (see Database Design and SQL Request and Transaction Processing for details).

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 functions CASE_N and RANGE_N (see SQL Functions, Operators, Expressions, and Predicates) are designed specifically to support simple partitioning expressions, but you can write any valid SQL expression as a partitioning expression for single‑level partitioning (the partitioning expressions for multilevel partitioning must be defined using only CASE_N or RANGE_N expressions in any combination) 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#Ln 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 Database Design 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 Database Design for details).
  • Expressions based on the RANGE_N function (see Database Design 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.