17.10 - Rules and Restrictions for Modifying Column-Partitioned Tables - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

Following are the rules and restrictions for modifying column-partitioned tables:

MODIFY NO PRIMARY

If you specify MODIFY NO PRIMARY, you must specify the NOT PARTITIONED option or a PARTITION BY clause.

You can also specify MODIFY NO PRIMARY for nonpartitioned NoPI tables, in addition to column-partitioned tables.

Specifying COLUMN partitioning in a PARTITION BY clause

You cannot specify MODIFY NO PRIMARY and NOT PARTITIONED on a table without a primary index or primary AMP index which is not partitioned.

If you alter a SET table to be a table with MODIFY NO PRIMARY, the system alters the table kind to be MULTISET by default. See Rules for Modifying Populated and Unpopulated Tables and Join Indexes.

The table being modified must not be populated with rows in the following cases:
  • Table with a primary index or primary AMP index being modified to have no primary index
  • NoPI or column-partitioned table being modified to have a primary index or primary AMP index.
  • Partitioned table being modified to have no partitioning.
  • Nonpartitioned table being modified to have partitioning.
  • Partitioned table being modified to have a new partitioning using a PARTITION BY clause.

New Partitioning Using DROP and ADD RANGE Clauses

To modify a partitioned table to have a new partitioning, you must define the new partitioning expressions using DROP RANGE#L n and ADD RANGE#L n options that do not drop ranges other than from the beginning or end of the range sequence with at least one remaining range, and do not add ranges between the resulting beginning and end of the ranges.

If the table has multilevel partitioning and the modification of a partitioning expression includes dropping the NO RANGE [OR UNKNOWN] or UNKNOWN partitions, that partitioning expression must not have previously been modified.

The resulting number of partitions for a level must be between 1 and the maximum defined for that level.