15.00 - PARTITION Columns - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

PARTITION Columns

The system derives a PARTITION column for each Teradata Database base table, global temporary table, volatile table, and non-compressed join index whenever the words PARTITION BY are invoked for that base, global temporary, or volatile table in a CREATE TABLE or ALTER TABLE SQL statement (see SQL Data Definition Language).

 

System‑Derived Column Name

           Default Data Type

                Default Title

PARTITION

INTEGER

The data type for PARTITION and PARTITION#Ln columns can be cast to a different type (see “Example 8: Selection of All Active Partitions From a Table” on page 807).

PARTITION

PARTITION#Ln

n represents an INTEGER value ranging from 1 through 62, inclusive.

PARTITION#Ln

PARTITION provides the combined partition number for a row.

The system‑derived columns PARTITION#Ln, where n ranges between 1 and 62, inclusive, provide the partition number of a row for the specified level.

If the partitioning expression for a table or join index defines between 1 and 65,535 combined partitions, the partition is stored as a 2‑byte value in the row header.

If the partitioning expression for a table or join index defines more than 65,535 combined partitions, the partition is stored as an 8‑byte value in the row header.

You cannot alter a 2‑byte PPI table to become an 8‑byte PPI table. A table is created with a 2‑byte PPI if you define it with 65,535 or fewer combined partitions. To create an 8‑byte PPI table that has 65,535 or fewer combined partitions in a way that it can later be altered to have more than 65,535 combined partitions as additional partitions are needed, first create the table with more than 65,535 combined partitions and then later alter it using an ALTER TABLE … MODIFY PRIMARY INDEX request that specifies a DROP RANGE#L1 clause to create the desired initial number of combined partitions. This action retains the index as an 8‑byte PPI.

The default values for PARTITION and PARTITION#Ln values have the following valid inclusive domain range values.

 

FOR this type of table …

The valid range for PARTITION is …

PPI

1 - 9,223,372,036,854,775,807

9,223,372,036,854,775,807 is the maximum number of partitions you can define for a PPI whether that PPI is single‑level or multilevel.

nonpartitioned primary index

0

The partition number of the column-partitioning level for a row is always 1.

  • If a table has column partitioning, but no row partitioning, PARTITION and PARTITION#L1 both return 1 and PARTITION#Ln, where n is between 2 and 62, inclusive, returns 0.
  • If a table has both column partitioning and row partitioning, and the column partitioning is at level m, PARTITION#Lm is 1 and, for the column‑level partitioning, Teradata Database uses 1 as the column partition number in calculating the combined partition number value for PARTITION.
  • You should restrict the use of the system-derived columns PARTITION and PARTITION#Ln to database administrators. You can restrict usage of these system‑derived columns by restricting access to PPI tables by means of views.

    Note that PARTITION numbers can change if you alter the partitioning for a PPI table.

    The principal end user application for the values stored in the system‑derived PARTITION and PARTITION#Ln columns is to retrieve rows from specific primary index partitions, but you can specify PARTITION or PARTITION#Ln as a column name in any of the following SQL request types.

  • Any DML request to determine the partition to which various rows in a table belong.
  • A COLLECT STATISTICS request (both forms) to collect the external partition number for each row on which the statistics are collected for a base data table.
  • See “COLLECT STATISTICS (Optimizer Form)” in SQL Data Definition Language and “COLLECT STATISTICS (QCD Form)” in SQL Data Manipulation Language for details.

  • An ALTER TABLE request in the DROP RANGE WHERE clause.
  • See “ALTER TABLE” in SQL Data Definition Language for details.

  • A CREATE VIEW or CREATE RECURSIVE VIEW request.
  • See “CREATE RECURSIVE VIEW” and “CREATE VIEW” in SQL Data Definition Language for details.

    You cannot specify the system‑derived PARTITION column in any of its forms in any of the following index definition statements. This means the restrictions that exist for the system‑derived PARTITION column apply equally to the system‑derived PARTITION#Ln column set.

  • CREATE HASH INDEX (see “CREATE HASH INDEX” in SQL Data Definition Language for details).
  • CREATE INDEX (see “CREATE INDEX” and “CREATE TABLE” in SQL Data Definition Language for details).
  • CREATE JOIN INDEX (see “CREATE JOIN INDEX” in SQL Data Definition Language for details).
  • You can specify an ordinary user‑created column named partition, but if you do so, you cannot reference the system‑derived PARTITION column by that name in any of its forms. This rule applies equally to the PARTITION#L1 - PARTITION#L62 system‑derived column set. See SQL Data Definition Language for details.

    As with any other column, you must fully qualify all ambiguous PARTITION and PARTITION#Ln references. Like any other column, PARTITION and PARTITION#Ln can be qualified by a database name and table name, they can be aliased using the [AS] column_name syntax, and they can be referenced wherever a column of the table can be referenced.

     

    IF this many tables specified in a query have an explicit column named PARTITION …

    THEN …

    1

    an unqualified reference to PARTITION refers to the explicitly defined column.

    > 1

    all references to PARTITION must be qualified.

    If you reference multiple tables in a query, then all references to the system‑derived column PARTITION or the system‑derived PARTITION#Ln column set must be qualified appropriately.

    Neither PARTITION nor any member of the PARTITION#Ln column set is included in the set of columns returned when you specify the ASTERISK (*) character in a SELECT request. You must specify those columns explicitly in a select list to return them in a response set (see “Example 4: PARTITION Values Not Returned Because PARTITION Not Specified in Select List” on page 807, “Example 1” on page 813 - “Example 4” on page 815). In particular, the PARTITION and PARTITION#Ln columns cannot be accessed through a view on a table unless the view definition explicitly specifies the column in its select list (see “Example 10: Using PARTITION In View Definitions” on page 808, “Example 7” on page 815, “Example 9” on page 816, “Example 10” on page 816, “Example 13” on page 817).

    Because neither PARTITION nor PARTITION#L1 through PARTITION#62 are reserved words, you can also assign their names to user-defined columns. If you do this, however, the system‑defined PARTITION and PARTITION#Ln columns for any such table are not accessible from an SQL query because there is no way to distinguish them from the user‑defined column with the same name.

    Neither PARTITION nor PARTITION#L1 through PARTITION#62 are returned as a column by a HELP TABLE or HELP COLUMN request, nor do they appear in the data dictionary as columns of the referenced table. As a result, the create text returned by a SHOW TABLE statement does not report system‑derived PARTITION and PARTITION#Ln columns.

    Do not attempt to update partition values under any circumstances. This operation is not permitted, and the system aborts the request and returns an error if you attempt to perform it.

    The value of PARTITION or PARTITION#Ln takes no additional space in the table. Teradata Database extracts the internal partition number from the partition field in the RowID of the row header and converts the value dynamically to its external partition number (see “Base Table Row Format” on page 740). The user‑visible value in the PARTITION field of a row is the external number of the partition in which it is stored. Teradata Database returns an error to the requestor if there is no external partition number corresponding to the internal partition number requested.

    PARTITION and PARTITION#Ln are equivalent to a value expression where that expression is identical to the partitioning expression defined for the primary index of the table with column references appropriately qualified as needed (or 0 if no partitioning expression for the primary index is defined).

    This is true if, and only if, the row specifies the correct internal partition.

    For example, a query of a PPI table that specifies the predicate WHERE PARTITION <> partitioning_expression should always return 0 rows. If any rows are returned, then they are not partitioned properly, and the table should be revalidated immediately. See “ALTER TABLE” in SQL Data Definition Language for more information about revalidating a PPI.

    If the partitioning expression for a PPI table is changed, then the values of PARTITION and PARTITION#Ln for many rows in the table might also change.

    You should always collect statistics on the single‑column PARTITION and PARTITION#Ln columns for any PPI base table (You cannot collect PARTITION statistics on a global temporary table) to ensure that the Optimizer is able to use the most accurate cost estimates possible.

    Multicolumn PARTITION and PARTITION#Ln statistics are used to do single table estimations when all the columns that include the PARTITION column have single‑table equality conditions.

    If the table is partitioned by a single-column expression, then its column statistics are inherited as PARTITION statistics. In this special situation, you need not also collect single‑column PARTITION or PARTITION#Ln statistics.

    See “COLLECT STATISTICS (Optimizer Form)” in SQL Data Definition Language for details about collecting PARTITION or PARTITION#Ln statistics.

    The system-derived column PARTITION also provides the partition number of the combined partitioning expression associated with a row when a multilevel PPI has been defined on a table. The system-derived columns PARTITION#L1 through PARTITION#L62 provide the partition number associated with the corresponding level.