15.10 - PARTITION BY - Teradata Database

The join index is partitioned by one or more partitioning levels. You can define either a single-level or multilevel partitioning for a join index, but only if the index is not row-compressed.

You cannot specify a PARTITION BY clause in the same join index definition as an ORDER BY CLAUSE.

You cannot collect statistics on the system-derived PARTITION#Ln columns for a join index.

partitioning expression

Defines partitions for the level. You can also specify the ADD keyword for a partitioning level. For more information about partitioning tables and join indexes, see Database Design, B035-1094. If the preceding item in the index list is a partitioning clause that is not part of an index clause, you must specify a COMMA character following the PARTITION BY clause. Otherwise, the comma is optional. You can define multilevel partitioning for an uncompressed join index, with as many as 62 partitioning levels. Each level must be defined by a RANGE_N or CASE_N function or by the COLUMN keyword. You cannot specify character partitioning expressions for columns or constants that use the Kanji1 or KanjiSJIS server character sets. The result of a partitioning expression that is not a RANGE_N or CASE_N function is cast to an INTEGER, if it is not already an INTEGER. The value must be from 1 through 65,535. A RANGE_N or CASE_N function is only allowed for single-level partitioning. The maximum number of partitioning levels that you can specify for a join index with 2-byte partitioning is 15. The maximum number of partitioning levels that you can specify for a join index with 8-byte partitioning is 62. A partitioning expression for a join index must be a deterministic expression and cannot specify external or SQL UDFs or columns having any of the following data types:

• ARRAY
• VARRAY
• BLOB
• CLOB
• Geospatial

While the partitioning expression for a join index cannot be defined on a Geospatial or ARRAY/VARRAY column, the partitioning expression can be defined on a Period column. This is particularly useful for defining updatable current dates and updatable current timestamps in a PPI.

In deciding whether to use a CASE_N or RANGE_N function in your partitioning expression, refer to the following:

• Use a CASE_N function to define a mapping between conditions to INTEGER numbers.

  If a partitioning expression is based only on a CASE_N function, the maximum number of partitions you can define is a maximum of approximately 4,000 conditions.

  This maximum can be further limited by other limits such as the size of the request text.

• Use a RANGE_N function to define a mapping of ranges of INTEGER, CHARACTER, or DATE values to INTEGER numbers. You should use a RANGE_N function to map BIGINT and TIMESTAMP values to BIGINT numbers.

If a single-level partitioning expression is based only on a RANGE_N function with INTEGER data type, the maximum number of partitions you can define is 65,535 for 2-byte partitioning and 2,147,483,647 for 8-byte partitioning.

If a partitioning expression is based only on a RANGE_N function with BIGINT data type, the maximum number of partitions you can define is 9,223,372,036,854,775,805.

You can define up to 9,223,372,036,854,775,807 ranges if you specify both the NO RANGE and UNKNOWN partitions.

See SQL Functions, Operators, Expressions, and Predicates, B035-1145 for documentation of functions that are particularly useful for use as partitioning levels, particularly RANGE_N and CASE_N.

You can define a wide variety of partitions with a large range in the number of combined partitions. However, you must consider the usefulness of defining a particular partitioning and its impact, both positive and negative, on performance and storage.

You must specify only a single COLUMN partitioning level for a column-partitioned join index.

You can group multiple columns together into a single partition by specifying a list of columns to be grouped together.

If you specify column grouping in a partitioning level of a join index definition, you cannot also specify column grouping in its select list.

If you specify neither COLUMN nor ROW for a column partition, the system determines whether COLUMN or ROW format is used for the column partition (for information about COLUMN and ROW formats, see Database Design, B035-1094.

COLUMN

Keyword to indicate partitioning level.

AUTO COMPRESS

NO AUTO COMPRESS

To enable or disable autocompression:

• If you specify NO AUTO COMPRESS for a column partitioning level in a PARTITION BY clause, the system does not apply autocompression for a column partition unless you explicitly specify AUTO COMPRESS.
• If you specify AUTO COMPRESS for a column partitioning level in a PARTITION BY clause, the system applies autocompression for a column partition unless the you explicitly specify NO AUTO COMPRESS.
• If you specify neither AUTO COMPRESS nor NO AUTO COMPRESS for a column partitioning level in a PARTITION BY clause, the system applies the default autocompression determined by the AutoCompressDefault cost profile constant. For more information about AutoCompressDefault, see SQL Request and Transaction Processing, B035-1142.

The system applies row header autocompression for column partitions with COLUMN format.

ALL BUT (column_name_list)

ALL BUT (column_group_list)

A multicolumn partition with autocompression and system-determined COLUMN or ROW format that includes all of the columns in the join index definition that are not specified in column_name_list or column_group_list.

You can only specify this option for column-partitioned join index.

COLUMN

Specifies that the join index has a column-partitioned level.

If you precede a column grouping with the COLUMN keyword, the system stores the grouped columns using COLUMN format.

ROW

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

If you precede a column grouping with the ROW keyword, the system stores the grouped columns using ROW format.

ADD constant

The maximum number of partitions for a partitioning level is the number of partitions it defines plus the value of the BIGINT constant value specified by constant.

The value for constant must be an unsigned BIGINT constant and cannot exceed 9,223,372,036,854,775,807.

You can only specify this option for column-partitioned join indexes.