PARTITION BY COLUMN - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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 Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

AUTO COMPRESS
Vantage automatically determines and applies the best available compression method, if it can reduce the size of physical rows. AUTO COMPRESS is the default for column partitions.
(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, Vantage 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, Vantage 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.