The following rules and restrictions apply to modifying partitioning.
- 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, Vantage 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 statement, the modified table and new partitioning must meet the conditions defined in MODIFY PRIMARY Option and MODIFY NO PRIMARY Option 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. See Rules for Modifying Populated and Unpopulated Tables and Join Indexes.
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, 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.
- 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, PRIMARY, or PRIMARY AMP.
- You cannot specify a COLUMN partitioning level more than once in a PARTITION BY clause.
- Vantage 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.
ALTER TABLE Request Modifies or Revalidates the Row Partitioning and Includes this Option Disable These Triggers 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 MODIFY PRIMARY, MODIFY NO PRIMARY, and PARTITIONED BY clauses.
- If you do not specify MODIFY PRIMARY or MODIFY NO PRIMARY, but do specify a PARTITION BY clause, the default is NO PRIMARY INDEX even if you specify a PRIMARY KEY or UNIQUE constraint, regardless of the setting of the DBS Control field PrimaryIndexDefault. For information about DBS Control and the PrimaryIndexDefault field, see Teradata Vantage™ - Database Utilities, B035-1102.
- If you do not specify MODIFY PRIMARY, MODIFY NO PRIMARY, or a PARTITION BY clause, the default primary index, primary AMP index, or default MODIFY NO PRIMARY for the table or join index is determined by the setting of the DBS Control field PrimaryIndexDefault. For information about the DBS Control PrimaryIndexDefault field, see Teradata Vantage™ - Database Utilities, B035-1102.
The following rules apply to the system-derived columns PARTITION and PARTITION#L n.
- You cannot specify the system-derived columns PARTITION or PARTITION#L n, 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#L n 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 a nonpartitioned if the table or join index is not populated with rows.
If the table or join index is populated, the system returns an error to the requestor.
- If you specify NOT PARTITIONED and the existing table or join index has a nonpartitioned primary index, Vantage 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.