15.10 - PARTITION BY COLUMN - Teradata Database

Column partitioning. 
You can define a large variety of partition levels with a large range in the number of combined partitions. However, complex partitioning can result in greater impact on performance and storage. 
If you do not specify COLUMN or ROW for a column partition, the format is system-determined. For information about COLUMN format and ROW format, see CREATE TABLE in SQL Data Definition Language - Detailed Topics, B035-1184.

AUTO COMPRESS

Indicates autocompression for the column partition. This specification takes precedence over the system default. If you do not explicitly specify AUTO COMPRESS or NO AUTO COMPRESS, Teradata Database uses the system default for autocompression as determined by the value of the AutoCompressDefault cost profile constant.

If the system default is AUTO COMPRESS, an explicit specification of NO AUTO COMPRESS overrides the default. If the system default is NO AUTO COMPRESS, an explicit specification of AUTO COMPRESS overrides the default. For information about AutoCompressDefault, its constant values, and their meanings, see SQL Request and Transaction Processing, B035-1142.

(COLUMN (column_name, column_name))

Indicates COLUMN format for the column partition. Column grouping provides flexibility in specifying which columns are grouped into which partitions while still being able to specify the display order in the table element list. Column grouping in the column list for a table allows for a simpler, but less flexible, specification of column groupings than you can specify in a partitioning level.

ROW (column_name, column_name))

Indicates ROW format for the column partition. ROW specifies that the column partition has ROW format. A ROW format means that only one column-partition value is stored in a physical row as a subrow.

WITH DELETE

Delete any row for which a new partitioning expression evaluates to a value outside the valid range of 1 through the number of partitions defined for that level.


Even though you can specify PARTITION BY partitioning_level WITH DELETE, the table must be empty so it has no effect. This option can be useful under certain circumstances if the table is not empty and DROP RANGE is specified.

WITH INSERT

Insert into save_table any row for which a new partitioning expression evaluates to a value outside the valid range of 1 through the number of partitions defined for that level. After it inserts the non-valid row, Teradata Database deletes it from the table. Even though you can specify with PARTITION BY partitioning level WITH INSERT, the table must be empty so it has no effect. This option can be useful under certain circumstances if the table is not empty and DROP RANGE is specified. You can use this option with row-level security-protected tables as long as the tables referenced in the request are all row-level security-protected and they are all be defined with the same row-level security constraints.
 If you do not specify the constraint values to be inserted into the target table, Teradata Database takes the constraint values for the target table from the source table.

INTO

Optional keyword preceding save_table.

save_table

save_table and the table being altered must be different tables, and save_table must have the same number of columns (with matching data types) as the table being modified.