Guidelines for Partitioning Column‑Partitioned Tables and Join Indexes for Column
While you can define column partitioning at any level of multilevel partitioning,
in most cases you should follow these guidelines when you configure the partitioning
for a column‑partitioned table.
Code the column partitioning level first and follow the column partitioning level
with any row partitioning levels.
If you do not code the column partition at the first level, code it as the second
level after DATE or TIMESTAMP row partitioning.
Some considerations that might lead to putting the column partitioning at a lower
level are the following.
Potential improvements for cylinder migration.
Block compression effectiveness.
If you specify row partitioning as part of a multilevel column partitioning for a
table, consider specifying the ADD option for the any partitioning levels that might
need to increase their number of partitions in the future.
Unless you have a good reason not to, you should use the defaults when you specify
PARTITION BY COLUMN. Do not override the defaults without first giving the reasons
for doing so serious consideration
For columns that are often specified in queries, but where the specific set of columns
specified varies from request to request, you should create single‑column partitions
for the frequently specified columns.
Use ROW format for wide column partitions because it has less overhead than a container
that holds one or a few values.
If Teradata Database does not assign ROW format for a column partition, but you have
determined that ROW format is more appropriate because it decreases space usage, specify
Use COLUMN format for narrow column partitions, especially if autocompression is effective.
If Teradata Database does not assign COLUMN format for a multicolumn partition, but
COLUMN is user-determined to be more appropriate (decreases space usage, etc.), specify
You might need to specify COLUMN format explicitly for a multicolumn partition that
contains a column with a VARCHAR, VARCHAR(n) CHARACTER SET GRAPHIC, or VARBYTE data
type and defined with a large maximum value, but where values are actually very short
in most cases. This is because the system‑determined format might be ROW based on
the large maximum length.