Download (direct link):
create a table with partitioning columns and define the PI as UNIQUE choose one of the following: • Include all the partitioning columns in the PI definition • Omit some or all of the partioning columns from the PI, plus: - Do not define the PI as unique (it must be a NUPPI) - Define a USI on the partitioning columns especially: - "CREATE TABLE (Index Definition Clause)" - "General Rules for the Primary Index Option Clause" - "CREATE INDEX"
create a table with partitioning and a PI that does not include all of the partitioning columns • The PI cannot be unique • To force uniqueness, you can: • "Using an Identity Column" on page 2-66
- Create a USI on the same set of columns as the PI - Use an identity column
create a table with partitioning columns and also define a USI one of the following must be true: • The PI is not partitioned • The set of PI columns excludes some or all of the partitioning columns
• Create a table with a multi-column PPI • Define a PRIMARY KEY or UNIQUE constraint on the same set of columns as the PPI both of the following conditions must be true: • The PPI column list does not include all of the partitioning columns • You do not define a USI on exactly the same set of columns as the PPI Note: A PRIMARY KEY or UNIQUE constraint on a PI implicitly defines a USI on the same set of columns.
create a partitioned table in which all rows can be inserted or else updated use CASE expressions, options on the CASE_N and RANGE_N functions, and asterisks in the RANGE_N function to construct a partitioning_expression that assigns each row to a partition number within the range 1 to 65533. (The limit allows for the UNKNOWN and NO RANGE [OR UNKNOWN] options). tNote: If an error (such as divide by zero) occurs during evaluation of the partitioning_expression itself, the entire transaction will be rolled back. • Teradata RDBMS SQL Reference, Volume 4, especially: - "General Rules for the Primary Index Option Clause" - "CREATE INDEX"
copy a table with partition expression and/or rename the partitioning columns, using CREATE TABLE . AS . WITH [NO] DATA • If the source table has a PPI, the PPI in the newly created table is partitioned in the same way. • The partitioning columns are renamed in the partitioning expression for the newly created table. "CREATE TABLE (AS Clause)" in Teradata RDBMS SQL Reference, Volume 4
2 - 22
Teradata RDBMS Database AdministrationChapter 2: Building the Teradata RDBMS
Secondary Indexes (SI)
IF you want to
For more information, see .
alter a table to modify a PPI expression
The new partitioning expression must be either already an INTEGER data type or be able to be CAST to integer
alter a table to change a PI to a UPPI
allowed if at least one of the following is true:
• The existing PI is already unique
• A USI exists on the same set of columns as the PPI (the USI is dropped automatically after the change)
• The table is empty
alter a table to change a NUPPI to a UPPI
include all the partitioning columns in the PPI definition. (If a USI already exists on the same set of columns as the final UPPI, the USI is dropped).
If you alter PI partitioning, uniqueness, or column members of an existing table, the table version number changes; this means you cannot include the modified table in these ARC functions:
• Cluster restore.
• Single AMP restore.
• PJ rollforward or rollback. (The roll operation stops and places an error message in the output listing.)
alter a table to change to UNIQUE a NUSI that is defined on the same columns as the PI
the PI for the table must already be defined as both:
- Not partitioned
- Does not include all of the partitioning columns
alter a table to exclude from a UPPI some or all of the partitioning columns
only allowed if the PPI is redefined as NOT UNIQUE (it must become a NUPPI).
alter a table with a PPI to drop or add ranges or partitions
consider the following performance issues:
IF . THEN there is .
dropped ranges and/or partitions are not empty a small amount of overhead.
a referential integrity constraint exists some overhead.
non-empty, NO RANGE [OR UNKNOWN) or UNKNOWN partitions or rows in the dropped ranges need to be moved to the added ranges overhead, depending on: • How many rows must be processed • Whether secondary, join, or hash indexes must be updated
"ALTER TABLE" in Teradata RDBMS SQL Reference, Volume 4, especially:
• "General Rules for the Modify Primary Index Option Clause"
• "Rules for Altering the Partioning Expression of a Primary Index"
• "Rules for Adding and Dropping Ranges in a Partitioning Expression"
2 - 22 Teradata RDBMS Database Administration
Chapter 2: Building the Teradata RDBMS
Secondary Indexes (SI)
IF you want to . THEN . For more information, see .
deleted rows are to be overhead, depending on inserted into save_table the number of rows.