15.00 - Rules for Using CASE_N and RANGE_N Functions in the Partitioning Expression of a Column-Partitioned Table - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Rules for Using CASE_N and RANGE_N Functions in the Partitioning Expression of a Column‑Partitioned Table

  • If you define a partitioning expression using only a single CASE_N function, the number of partitions defined must be less than or equal to 2,147,483,647 because the result of a CASE_N function invocation has INTEGER data type.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

  • If you define the partitioning expression for single‑level partitioning using only a single RANGE_N function with INTEGER data type, the total number of partitions defined must be less than or equal to 2,147,483,647.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

    For 2‑byte single‑level partitioning, the RANGE_N function cannot define more than 65,533 range partitions because by definition, more range partitions would require 8‑byte partitioning.

    The total number of partitions you can define can be as many as 65,535 if you define both a NO RANGE and an UNKNOWN partition.

  • If you define a partitioning expression using only a single RANGE_N function with BIGINT data type, the number of ranges defined must be less than or equal to 9,223,372,036,854,775,805.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

    For single‑level partitioning, the total number of partitions you can define can be as many as 9,223,372,036,854,775,807 if you define both a NO RANGE and an UNKNOWN partition.

  • The number of partitions defined for a row partitioning level of a column‑partitioned table is the number of row partitions specified by the RANGE_N or CASE_N function, or 65,535 if you specify neither a RANGE_N or a CASE_N function.