Guidelines for Specifying Table and Column Attributes for Column Partitioning
Neither nonpartitioned NoPI tables nor column‑partitioned tables can be created as a SET table. As a result, Teradata Database does not perform duplicate row checks for either.
These options specify the amount of space on each cylinder that is to be left unused during load operations, and the reserved free space allows table data to expand on current table cylinders, preventing or delaying the need for additional table cylinders to be allocated, preventing or delaying data migration operations associated with new cylinder allocations.
If you do a large INSERT … SELECT operation and internal partitions are either large or empty, little or no free space is needed. Keeping new table data physically close to existing table data and avoiding data migrations, can improve overall system performance.
Because nullable columns can significantly decrease the effectiveness of autocompression, you should avoid them unless you have a sound reason for specifying them.
CHECK constraints are valid for both NoPI tables and column‑partitioned tables.
UNIQUE and PRIMARY KEY constraints are valid for both NoPI tables and column‑partitioned tables.
Foreign key constraints are valid for both NoPI tables and column‑partitioned tables.
The following features are valid for both NoPI tables and for column‑partitioned tables:
For a primary‑indexed table, Teradata Database generates the hash value of a row from the values of the columns that constitute the primary index. This hash value determines to which AMP a row is sent and stored. Although neither NoPI tables nor column‑partitioned tables have a primary index, each row still must be assigned to a hash bucket, and the bucket number to which the row is assigned is generated internally.
This approach allows fallback and index maintenance to work as they would if the table had a primary index.
A nonpartitioned NoPI table can be created as a global temporary or volatile table.
The default behavior for CREATE TABLE for a column‑partitioned table is NO PRIMARY INDEX if you do not specify a primary index. The setting of the PrimaryIndexDefault DBS Control parameter does not affect this behavior.
Note: There is a limit of 256M rows per rowkey per AMP with XML, BLOB, and CLOB data types. NoPI tables normally have only one hash value on each AMP, so the effective limit on the number of rows per AMP is approximately 256M.
The exact number is 268,435,455 rows per rowkey per AMP.
For column‑partitioned tables, these limits are per column partition:hash bucket combination rather than rows per hash value.
See the following topics for detailed performance guidelines for specifying compression, I/O, CPU usage, and storage operations, and collecting statistics for column‑partitioned tables and join indexes.