15.00 - Miscellaneous Topics About Modifying the Partitioning of a Table or Join Index - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Miscellaneous Topics About Modifying the Partitioning of a Table or Join Index

The following miscellaneous rules and restrictions apply to modifying the partitioning of a database object.

  • You cannot alter a table with 2‑byte partitioning to have 8‑byte partitioning.
  • You cannot alter a table with 8‑byte partitioning to have 2‑byte partitioning even if the number of combined partitions decreases to be fewer than 65,536.
  • If you specify neither PARTITIONED BY nor NOT PARTITIONED, Teradata Database does not change the partitioning of the specified table.
  • You cannot partition a secondary or hash index.
  • If a table does not need to be unpopulated for a particular ALTER TABLE request (see “Rules for Modifying Populated and Unpopulated Tables and Join Indexes” on page 89), the modified table and new partitioning must meet the conditions defined in “PRIMARY INDEX Option” on page 87 and “NO PRIMARY INDEX Option” on page 88 except that 8‑byte partitioning remains 8‑byte partitioning even if the new number of combined partitions decreases to be fewer than 65,536 and a 2‑byte partitioning remains 2‑byte partitioning.
  • If the new partitioning defines more than a maximum of 65,535 combined partitions for a 2‑byte partitioned table, the table must be unpopulated, and the rule documented in the previous paragraph does not apply. In this case, the new partitioning for an unpopulated table would be 8‑byte partitioning.

  • If you specify a secondary index in an ALTER TABLE request that is followed by a COMMA character, which is then followed by a PARTITION BY clause, the PARTITION BY clause applies to the table or primary index, not to the secondary index.
  • If you specify a PARTITION BY clause by itself in an index list, not as the last item specified in the index list, you must also add a COMMA character following the PARTITION BY clause.
  • You cannot specify a PARTITION BY clause more than once by itself in an index list.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

  • If you specify a PARTITION BY clause by itself in an index list, you cannot also specify it in an index definition for NO PRIMARY INDEX or PRIMARY INDEX.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

  • Teradata Database implicitly rewrites a partitioning expression as follows.
  • A TIME(n) without time zone literal is modified to a TIME(n) WITH TIME ZONE literal by including the current session time zone displacement, if one exists, or the time zone displacement based on the current session time zone string, the local value of the literal, and the CURRENT_DATE at UTC (that is, at time zone +00:00).
  • A TIMESTAMP(n) without a time zone literal is modified to be a TIMESTAMP(n) WITH TIME ZONE literal by including the current session time zone displacement, if one, or the time zone displacement based on the current session time zone string and the local value of the literal.
  • An AT LOCAL clause is modified to be an AT simple expression clause that specifies a character literal.
  • If the current session time zone is a time zone displacement, the character literal is the current session time zone displacement as a character literal with format '+hh:mm' if positive and '-hh:mm' if negative; if the current session time zone is a time zone string, the character literal is the current session time zone string.

  • For CURRENT_DATE or DATE without an AT clause, the resolved current date defined is at the current session time zone.
  • For CURRENT_DATE or DATE with an AT [TIME ZONE] simple expression clause, the resolved current date is at the specified time zone.

  • If evaluation of the new partitioning expression for a row or column in the table causes evaluation errors (such as divide by zero), changes to the table (and also to the save table, if any) are rolled back and the partitioning expression is not changed.
  • A new partitioning expression for a level becomes the partitioning expression for that level after it is successfully altered.
  • If there is a subsequent attempt to insert or update a row or column of a row‑partitioned table such that the partitioning expression for that row or column does not result in a value between 1 and the number of partitions defined for that level, an error occurs for the insert or update.

  • If a table or join index has both column partitioning and row partitioning, modifying the row partitioning for a populated table or join index has a performance impact. The performance impact is because it can be more costly to move a table row from one row partition to another.
  • You must disable the triggers specified in the following table before you can perform an ALTER TABLE request that modifies or revalidates a table that is defined with triggers.
  •  

    IF you specify this clause in an ALTER TABLE request that modifies or revaluates the row partitioning …

    THEN you must disable these triggers before you perform the request …

    WITH DELETE

    all delete triggers on table_name.

    After the ALTER TABLE request completes, you can re-enable the disabled triggers.

    WITH INSERT

  • all delete triggers on table_name.
  • all insert triggers on save_table.
  • After the ALTER TABLE request completes, you can re-enable the disabled triggers.

    The following general rules apply to the PRIMARY INDEX, NO PRIMARY INDEX, and PARTITIONED BY clauses.

  • If you specify a primary index and a PARTITION BY clause either in the PRIMARY INDEX specification or by itself in the index list, you cannot specify a COLUMN partitioning level in the PARTITION BY clause. Otherwise, Teradata Database aborts the request and returns an error to the requestor.
  • If you specify neither a PRIMARY INDEX nor NO PRIMARY INDEX, but do specify a PARTITION BY clause, the default is NO PRIMARY INDEX even if you specify a PRIMARY KEY or UNIQUE constraint and regardless of the setting of the DBS Control parameter PrimaryIndexDefault (see Utilities: Volume 1 (A-K) for information about DBS Control and the PrimaryIndexDefault parameter).
  • If you do not specify a PRIMARY INDEX, NO PRIMARY INDEX, or a PARTITION BY clause, the default primary index or default NO PRIMARY INDEX for the table or join index is determined by the setting of the DBS Control parameter PrimaryIndexDefault (see Utilities: Volume 1 (A-K) for information about DBS Control and the PrimaryIndexDefault parameter).
  • Teradata Database does not support primary‑indexed column‑partitioned tables, so if you specify PRIMARY INDEX in an ALTER TABLE request, one of the following things must also be true for the specified table.
  • You must also specify NOT PARTITIONED
  • or

  • You must also specify a PARTITIONED BY clause
  • or

  • The table is not currently column‑partitioned.
  • If none of these is true, Teradata Database aborts the request and returns an error to the requestor.

    The following rules apply to the system‑derived columns PARTITION and PARTITION#Ln.

  • You cannot specify the system‑derived columns PARTITION or PARTITION#Ln, where the value of n ranges from 1-15 inclusive for 2‑byte partitioning and from 1 - 62 inclusive for 8‑byte partitioning, in any of its forms in the definition of a new partitioning expression.
  • Any change to how a table or join index is partitioned affects the values for the system‑derived columns PARTITION and PARTITION#Ln for most, if not all, rows in the table or join index.
  • The following rules apply to the NOT PARTITIONED clause.

  • If you specify NOT PARTITIONED and the existing table or join index is partitioned, its partitioning is modified to be an unpartitioned if the table or join index is not populated with rows.
  • If the table or join index is populated, the request aborts and Teradata Database reports an error to the requestor.

  • If you specify NOT PARTITIONED and the existing table or join index has an unpartitioned primary index, Teradata Database does not change the partitioning of its primary index.
  • If you specify neither PARTITIONED BY nor NOT PARTITIONED, the partitioning of the primary index is not changed.