15.00 - Usage Considerations and Rules for PARTITION and PARTITION#Ln Columns - Teradata Database

Teradata Database Design

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

Usage Considerations and Rules for PARTITION and PARTITION#Ln Columns

The usage considerations and rules for the system-derived PARTITION column for single‑level PPIs apply equally to multilevel partitioned primary index tables except as noted in the following rules.

  • The system-derived column PARTITION is equivalent to a value expression that is identical to combined partitioning expression derived for the primary index of the table, with appropriately qualified column references. This value is always 0 for a non-partitioned primary index.
  • The combined partitioning expression for a table defines how rows are ultimately partitioned on each AMP. The result of the combined partitioning expression for specific values of the partitioning column is referred to as the combined partition number.

    Teradata Database derives a combined partitioning expression from the partitioning expressions defined for each individual row partitioning level, if any, and a column partition number of 1 for the column‑partitioning level, if there is one.

    For n partitioning levels, where n is the number of partitioning levels defined, the combined partitioning expression is defined as follows.

    where:

     

    Equation element …

    Specifies …

    pi

    the row partitioning expression at level i, numbering from left to right, or 1 for a column partitioning level.

    ddi

     

    a constant value equal to the following product.

    dj

    the maximum partition number defined at level j.

    The parentheses delimiting the summation are not included in the combined partitioning expression. For example, this summation expands for one-, two-, three-, four-, and five-level partitioning as the following combined partitioning expressions, respectively, where pi and ddi are defined as they were in the preceding definition for a combined partitioning expression.

     

    Partitioning Level

                                                 Expanded Summation

                   1

    p1

                   2

    (p1-1)*dd1+p2

                   3

    (p1-1)*dd1+(p2-1)*dd2+p3

                   4

    (p1-1)*dd1+(p2-1)*dd2+(p3-1)*dd3+p4

                   5

    (p1-1)*dd1+(p2-1)*dd2+(p3-1)*dd3+(p4-1)*dd4+p5

    The combined partitioning expression reduces to p1 for single-level row partitioning, so there is no actual change in the usage rules for this case.

    For column partitioning, the combined partition number for a specific column partition value of a table row can be derived from the combined partition number for this table row as follows.

    where:

     

    Equation element …

    Specifies …

    combined_part_number-specific_col_part_value_of_row

    the combined partition number for a specific column partition value of a table row.

    cpn

    the combined partition for the row.

    c

    the column partition number for this column partition value within the row.

    ddi

    a constant value equal to 1.

    This is the same as computing the combined partitioning expression for a row‑partitioned table row except instead of using 1 for the column-partitioning level, this equation uses the column partition number corresponding to the specific column partition value of the table row.

    While column partitioning can be defined at any level, it is recommended in most cases to put the column-partitioning level first before any row partitioning. Some considerations that might lead to assigning the column partitioning to a lower level in the partitioning hierarchy are potential improvements for cylinder migration and temperature-based block compression effectiveness for hot and cold data.

  • You cannot reference the system-derived PARTITION column in a CREATE JOIN INDEX, CREATE HASH INDEX, or CREATE INDEX requests (see SQL Data Definition Language).
  • You can reference the system-derived columns PARTITION#L1 through PARTITION#L62 at any point in a DML request where a table column can be referenced. You can also reference the system‑derived PARTITION#Ln columns in the DROP RANGE WHERE clause of an ALTER TABLE request (see SQL Data Definition Language).
  • You can neither update these system-derived columns, nor can you assign a value or null to them with an insert operation. Both cases abort the request and return an error message to the requestor.
  • As is the case with single‑level PPI tables, if a multilevel PPI table definition explicitly specifies a column with the same name as any of the 62 system‑derived PARTITION columns, then the system‑derived column having that name cannot be accessed because the system interprets any reference to that column name for the table as a reference to the user‑defined column rather than its homonymous system‑derived column.
  • You can qualify the system‑derived PARTITION#Ln columns with a database name and table name just as you can any other table column.
  • The following list describes the details of qualifying system‑derived PARTITION#Ln columns:

  • If only one table in a query has an explicitly named column of PARTITION#Ln (where n is an integer in the range 1 - 62, inclusive), then an unqualified reference to PARTITION#Ln refers to the user‑specified column of that name in that table.
  • If more than one table in the query has an explicit user‑specified column named PARTITION#Ln, then you must qualify any references to PARTITION#Ln.
  • If there are multiple tables in the query, then you must qualify all references to the system-derived column PARTITION#Ln.
  • If there is only one table in the query, and that table does not have a user‑named column named PARTITION#Ln, then an unqualified reference to PARTITION#Ln refers to the system-derived column PARTITION#Ln for that table.
  • The data type for a PARTITION#Ln column depends on whether its PPI is the 2‑byte form or the 8‑byte form.
  •  

    FOR this form of PPI …

    THE data type for the system‑derived PARTITION#Ln column is …

    AND the default format is …

    2‑byte

    INTEGER

    INTEGER

    8‑byte

    BIGINT

    BIGINT

    When invoked, each returns the value that is the partition number of the row in the table for the specified partitioning level. The value ranges between 1 and the number of partitions defined for the specified partitioning level. For a table without a partitioning expression at that level, or for a non-partitioned primary index, the value returned is 0. For single-level partitioning, the system‑derived columns PARTITION and PARTITION#L1 are synonyms and have the same value, while the other system‑derived PARTITION#Ln columns have a value of 0.

  • Also like the system‑derived PARTITION column of single‑level PPI tables, the values of the system-derived PARTITION#Ln columns consume no space in the table.
  • When you reference a PARTITION#Ln column, the system extracts the internal partition number for the combined partitioning expression from the row and converts it to the external partition number for the corresponding level of the system-derived column.

    If no corresponding external partition number for the extracted internal partition number exists, the system aborts the request and returns an error message to the requestor.

  • A system-derived PARTITION#Ln column is equivalent to a value expression that is identical to the partitioning expression at the specified level defined for the primary index, or 0 if there is no partitioning expression for that level or if the primary index is not partitioned, of the table with appropriately qualified column references.
  • Like the system‑derived PARTITION column for single‑level PPI tables, the system‑derived PARTITION#Ln columns are not included in the list of columns returned by specifying an ASTERISK character or table_name.* when you select rows from a table. You can, however, explicitly select system-derived PARTITION#Ln column from the table.
  • Similarly, you cannot access the system‑derived PARTITION#Ln columns through a view based on an underlying multilevel partitioned primary index table unless that view explicitly includes the name of the system‑derived column in its definition. In this case, you have explicitly defined the columns in the view, so selecting either * or view_name.* from the view includes those explicit columns from the view.
  • If you attempt to select a system‑derived PARTITION#Ln column from a view in which its name is not explicitly defined for the view, the request aborts and the system returns an error message to the requestor. Because the SQL semantics of views and derived tables are identical, the system aborts the request and reports the identical error under the same circumstances with a derived table.

  • Like the system‑derived PARTITION column for single‑level PPI tables, the system does not return any system‑derived PARTITION#Ln columns in response to a HELP TABLE or HELP COLUMN request because they are derived and are not stored in the dictionary as names of physical columns in the table.
  • As is true of the system‑derived PARTITION column for single‑level PPI tables, you cannot reference a system-derived PARTITION#Ln column in a CREATE JOIN INDEX, CREATE HASH INDEX, or CREATE INDEX statement. If you attempt to do so, the system aborts the request and returns an error message to the requestor.
  • This restriction also implies that you cannot specify a system-derived PARTITION#Ln column as an index or partitioning column. Of course, you can specify a user‑defined homonymous column name as an index or partitioning column.

  • Note that if you use ALTER TABLE (see SQL Data Definition Language) to change one or more of the partitioning expressions for the primary index of an multilevel partitioned primary index table, the values of the system‑derived PARTITION#Ln columns for rows in the altered table might change.