partitioning level - 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™
partitioning_level
A partitioning level can be defined using a single COLUMN keyword, a partitioning expression, or a combination of both.
Multiple partitioning levels must be separated by commas and the entire set must be enclosed in parentheses.
The maximum number of partitioning levels that you can specify for:
  • 2-byte partitioning is 15
  • 8-byte partitioning is 62
If a partitioning expression is not a RANGE_N or CASE_N function, it is only allowed if there is only level of partitioning and its result must always be eligible to be implicitly cast to an INTEGER, if it is not already an INTEGER.
COLUMN
Column partition container is stored with COLUMN format.
partitioning_expression
Use a CASE_N function to define a mapping between conditions to INTEGER or BIGINT numbers.
The maximum number of partitions for a CASE_N partitioning level is typically limited to 4,000 or less.
Use a RANGE_N function to define a mapping of ranges of INTEGER, CHARACTER, or DATE values to INTEGER numbers or to define a mapping of ranges of BIGINT or TIMESTAMP values to BIGINT numbers.
The maximum number of ranges, not including the NO RANGE, NO RANGE OR UNKNOWN, and UNKNOWN partitions for a RANGE_N partitioning level, is 9,223,372,036,854,775,805.
You cannot specify an EACH clause if the RANGE_N function specifies a character or graphic test value.
See Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145 for documentation of the CASE_N and RANGE_N functions.
(COLUMN column_name)
The column partition is stored with COLUMN format.
Column grouping in the COLUMN clause enables more flexibility in specifying which columns go into which partitions while still being able to specify the display order (that is, when selecting the columns from the table using an asterisk) in the table element list. Column grouping in the column list for a table enables a simpler, but less flexible, specification of column groupings than you can specify in a partitioning level.
(ROW column_name)
The column partition is stored with ROW format. A ROW format means that only one column-partition value is stored in a physical row as a subrow.
If you specify neither COLUMN nor ROW for a column partition, Vantage determines whether COLUMN or ROW format is used for the column partition. 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 format column partitions.
ADD constant
Specifies that 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.
ALL BUT (column_name_list)
Specifies that a single-column partition with autocompression and a system-determined COLUMN or ROW format is defined for each column, if any, that is not specified in the column group list.
You can only specify this option for column-partitioned tables.
NO AUTO COMPRESS
Disables autocompression for the physical rows of a column partition of a column-partitioned table. Vantage does not automatically determine or apply the best available compression method to physical rows in COLUMN format column partitions.
Vantage does apply any user-specified compression and, for column partitions with COLUMN format, row header compression.

Example: Multilevel Partitioned UPI With no USI Defined on the Primary Index

The following example creates a two-level partitioning using the RANGE_N function as the basis for both partitioning expressions. Because both partitioning columns are components of the primary index, the index can be defined as a UPI.

     CREATE TABLE sales (
       store_id      INTEGER NOT NULL,
       product_id    INTEGER NOT NULL,
       sales_date    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       total_revenue DECIMAL(13,2),
       total_sold    INTEGER,
       note          VARCHAR(256))
     UNIQUE PRIMARY INDEX (store_id, product_id, sales_date)
     PARTITION BY (RANGE_N(store_id   BETWEEN 1 
                                      AND   300 
                                      EACH    1),
                   RANGE_N(sales_date BETWEEN DATE '2006-01-01' 
                                      AND     DATE '2006-05-31' 
                                      EACH INTERVAL '1' DAY));

Example: Multilevel Partitioned NUPI With no USI Defined on the Primary Index

The following example creates a two-level partitioning using the RANGE_N function as the basis for both partitioning expressions. Because neither partitioning column is a component of the primary index, that index must be defined as a NUPI, and because no USI is defined on o_orderkey, it is not unique.

     CREATE TABLE lineitem (
       l_orderkey      INTEGER NOT NULL,
       l_partkey       INTEGER NOT NULL,
       l_suppkey       INTEGER,
       l_linenumber    INTEGER,
       l_quantity      INTEGER NOT NULL,
       l_extendedprice DECIMAL(13,2) NOT NULL,
       l_discount      DECIMAL(13,2),
       l_tax           DECIMAL(13,2),
       l_returnflag    CHARACTER(1),
       l_linestatus    CHARACTER(1),
       l_shipdate      DATE FORMAT 'yyyy-mm-dd',
       l_commitdate    DATE FORMAT 'yyyy-mm-dd',
       l_receiptdate   DATE FORMAT 'yyyy-mm-dd',
       l_shipinstruct  VARCHAR(25),
       l_shipmode      VARCHAR(10),
       l_comment       VARCHAR(44))
     PRIMARY INDEX (l_orderkey)
     PARTITION BY (RANGE_N(l_suppkey  BETWEEN 0 
                                      AND  4999 
                                      EACH   10),
                   RANGE_N(l_shipdate BETWEEN DATE '2000-01-01' 
                                      AND     DATE '2006-12-31' 
                                      EACH INTERVAL '1' MONTH));

Example: Multilevel Partitioned UPI

The following example creates a two-level partitioning using the RANGE_N function as the basis for both partitioning expressions. Because both partitioning columns are components of the primary index, the index can be defined as a UPI.

     CREATE TABLE sales_history (
       store_id      INTEGER NOT NULL,
       product_id    INTEGER NOT NULL,
       sales_date    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       total_revenue DECIMAL(13,2),
       total_sold    INTEGER,
       note          VARCHAR(256))
     UNIQUE PRIMARY INDEX (store_id, product_id, sales_date)
     PARTITION BY (RANGE_N(store_id   BETWEEN 1 
                                      AND   300 
                                      EACH    1),
                   RANGE_N(sales_date BETWEEN DATE '2004-01-01' 
                                      AND     DATE '2006-12-31' 
                                      EACH INTERVAL '1' MONTH));

Example: Multilevel Partitioned NUPI With a Maximum Number of Partitions

The following example specifies the maximum of 65,535 (3*5*17*257) partitions allowed for a 2-byte partition number combined partitioning expression. Because none of the partitioning columns is a component of the primary index, that index cannot be defined as a UPI.

     CREATE TABLE markets (
       product_id       INTEGER NOT NULL,
       region           BYTEINT NOT NULL,
       activity_date    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       revenue_code     BYTEINT NOT NULL,
       business_sector  BYTEINT NOT NULL,
       note             VARCHAR(256))
     PRIMARY INDEX (product_id, region) PARTITION BY (
       RANGE_N(region          BETWEEN 1 
                               AND     9 
                               EACH    3),
       RANGE_N(business_sector BETWEEN 0 
                               AND    49 
                               EACH   10),
       RANGE_N(revenue_code    BETWEEN 1 
                               AND    34 
                               EACH    2),
       RANGE_N(activity_date   BETWEEN DATE '1986-01-01' 
                               AND     DATE '2007-05-31' 
                               EACH INTERVAL '1' MONTH));

Example: Using Different Syntax to Create the Same Column-Partitioned Table Definition or to Change the Partitioning

This example presents three different syntaxes that all define the identical table.

The first example defines a column-partitioned table with a USI on column o_orderkey. Each column except for o_comment is contained in its own partition, is stored using system-determined COLUMN format, and is autocompressed. Column o_comment is in its own partition and is stored using ROW format without autocompression.

Because its maximum combined partition number (including 2 column partitions for internal use and 1 spare) is less than 65,535, the partition number of each row in this table uses a 2-byte partition number field in its row header.

     CREATE TABLE orders (
       o_orderkey     INTEGER NOT NULL,
       o_custkey      INTEGER,
       o_orderstatus  CHARACTER(1) CASESPECIFIC,
       o_totalprice   DECIMAL(13,2) NOT NULL,
       o_orderdate    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_shippriority INTEGER,
       ROW(o_comment  VARCHAR(79)) NO AUTO COMPRESS)
     PARTITION BY COLUMN,
     UNIQUE INDEX (o_orderkey);

The difference for the second example is the absence of the ROW specification for o_comment in the column list, replaced by an ALL BUT (ROW(o_comment)) specification in the PARTITION BY COLUMN clause.

     CREATE TABLE orders (
       o_orderkey     INTEGER NOT NULL,
       o_custkey      INTEGER,
       o_orderstatus  CHARACTER(1) CASESPECIFIC,
       o_totalprice   DECIMAL(13,2) NOT NULL,
       o_orderdate    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_shippriority INTEGER,
       o_comment      VARCHAR(79))
     PARTITION BY COLUMN ALL BUT (ROW(o_comment) NO AUTO COMPRESS),
     UNIQUE INDEX (o_orderkey);

The difference for the third example is that the columns that are not listed in the grouping clause of the COLUMN clause are grouped together into a column partition by default, but because o_shippriority is the only column that is not listed in the COLUMN grouping clause, each column is in its own single-column partition.

     CREATE TABLE orders (
       o_orderkey     INTEGER NOT NULL,
       o_custkey      INTEGER,
       o_orderstatus  CHARACTER(1) CASESPECIFIC,
       o_totalprice   DECIMAL(13,2) NOT NULL,
       o_orderdate    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_shippriority INTEGER,
       o_comment      VARCHAR(79))
     PARTITION BY COLUMN (o_orderkey, o_custkey, o_orderstatus,
                          o_totalprice, o_orderdate, 
                          ROW o_comment NO AUTO COMPRESS),
     UNIQUE INDEX (o_orderkey);

This example again defines a column-partitioned orders table, but the partitioning is different from that used in the previous example. Each column is in its own partition with system-determined COLUMN format and autocompression except for the o_totalprice and o_comment columns, which are grouped together in a partition that has ROW format and autocompression.

Because the maximum number of combined partitions is 65,534 and the maximum column partition number is 65,535, the partition number for each row of this table uses 2-byte partitioning.

You could specify an ADD clause for the partitioning level of this table with a value as large as 65,526 and the syntax would still define the same table.

     CREATE TABLE orders (
       o_orderkey     INTEGER NOT NULL,
       o_custkey      INTEGER,
       o_orderstatus  CHARACTER(1) CASESPECIFIC,
       o_totalprice   DECIMAL(13,2) NOT NULL,
       o_orderdate    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_shippriority INTEGER,
       o_comment      VARCHAR(79))
     PARTITION BY COLUMN ALL BUT (ROW(o_totalprice, o_comment)),
     UNIQUE INDEX (o_orderkey);

This example again defines a column-partitioned orders table, but the partitioning is different from the previous examples. Each column is in its own partition. This request defines 7 partitions plus the 2 internal partitions used by all column-partitioned tables.

Because the maximum number of column partitions for this table is 9,223,372,036,854,775,806 and the maximum column partition number is 9,223,372,036,854,775,807, each row of this table requires 8-byte partitioning, which also distinguishes this example from the preceding examples of column-partitioned tables.

     CREATE TABLE orders (
       o_orderkey     INTEGER NOT NULL,
       o_custkey      INTEGER,
       o_orderstatus  CHARACTER(1) CASESPECIFIC,
       o_totalprice   DECIMAL(13,2) NOT NULL,
       o_orderdate    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_shippriority INTEGER,
       o_comment      VARCHAR(79))
     NO PRIMARY INDEX PARTITION BY COLUMN ADD 68000,
     UNIQUE INDEX (o_orderkey);

This example defines the identical orders table as the previous example, the only difference being the value specified for the ADD option in the partitioning expression. Each column is in its own partition. This request defines 7 partitions plus the 2 internal partitions used by all column-partitioned tables.

Because the maximum number of column partitions for this table is 9,223,372,036,854,775,806 and the maximum column partition number is 9,223,372,036,854,775,807, the partition number for each row of this table requires 8-byte partitioning, which also distinguishes this example from the preceding examples of column-partitioned tables.

     CREATE TABLE orders (
       o_orderkey     INTEGER NOT NULL,
       o_custkey      INTEGER,
       o_orderstatus  CHARACTER(1) CASESPECIFIC,
       o_totalprice   DECIMAL(13,2) NOT NULL,
       o_orderdate    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_shippriority INTEGER,
       o_comment      VARCHAR(79))
     PARTITION BY COLUMN ADD 9223372036854775797,
     UNIQUE INDEX (o_orderkey);

Example: Creating a Column-Partitioned Table With ROW Format

This example defines a column-partitioned table with 3 column partitions, all having ROW format either explicitly specified or system-determined.

     CREATE MULTISET TABLE sensordata (
       id VARCHAR (128),
       m1 VARBYTE(10000),
       m2 VARBYTE(10000),
       m3 VARBYTE(10000),
       t1 TIMESTAMP(6) WITH TIME ZONE,
       t2 TIMESTAMP(6) WITH TIME ZONE,
       t3 TIMESTAMP(6) WITH TIME ZONE,
       t4 TIMESTAMP(6) WITH TIME ZONE )
     PARTITION BY COLUMN (ROW(id), (m1, m2, m3),
                          ROW(ts1, ts2, ts3, ts4));

Example: Using the ADD Option for a Column-Partitioned Table

This example defines a maximum of 9,999 column partitions (3 user-specified, 2 for internal use, and 9,994 for adding more partitions) and a maximum column partition number of 10,000.

The maximum combined partition number is the product of the maximum number of partitions at each level. For table t23, the maximum combined partition number is calculated as follows.

Maximum combined partition number = 10000 x 10000000 x 92233720 = 9223372000000000000

This is a valid maximum because it does not exceed the limit of 9,223,372,036,854,775,807. Even though there are excess combined partitions, there are not enough of them to be able to add a partition to level 2 or to level 3, so the number of partitions at levels 2 and 3 cannot be increased using an ALTER TABLE request.

     CREATE MULTISET TABLE t23 (
       a INTEGER, 
       b INTEGER, 
       c INTEGER)
     PARTITION BY (COLUMN ADD 9994,
                   RANGE_N(a BETWEEN     1 
                             AND 10000000 
                             EACH 1),
                   RANGE_N(b BETWEEN    1 
                             AND 92233720 
                             EACH 1));

Suppose you change the RANGE_N end_expression value for level 3 to 92,233,721, which is only 1 larger than the previous end_expression value for level 3 of 92,233,720.

     CREATE MULTISET TABLE t23a (
       a INTEGER, 
       b INTEGER, 
       c INTEGER)
     PARTITION BY (COLUMN ADD 9994,
                   RANGE_N(a BETWEEN     1 
                             AND 10000000 
                             EACH 1),
                   RANGE_N(b BETWEEN    1 
                             AND 92233721 
                             EACH 1));

The table definition for t23a , like the definition for t23, defines a maximum of 9,999 column partitions (3 user-specified, 2 for internal use, and 9,994 for adding more partitions), and also has a maximum column partition number of 10,000.

But notice what happens now when you calculate the maximum combined partition number for the table.

Maximum combined partition number = 10000 x 10000000 x 92233721 = 9223372100000000000

The maximum combined partition number now exceeds the system maximum value of 9,223,372,036,854,775,807, so Vantage returns an error to the requestor.

Example: Assigning Combined Partitions for a Column-Partitioned Table to Partitioning Levels

If there are remaining unassigned combined partitions after the other partitions have been assigned to their respective levels, Vantage attempts to assign those unassigned combined partitions to the previously defined partition levels, as this example illustrates.

The CREATE TABLE SQL text for this example follows.

CREATE TABLE t38 (
   a INTEGER, 
   b INTEGER, 
   c INTEGER)
PARTITION BY (COLUMN,             -- 3 specified partitions+2 int-
             RANGE_N(c BETWEEN 1     ernal=5 defined partitions.
                       AND    10),-- 1 defined partition. Note
             RANGE_N(b BETWEEN 1     there is no EACH clause.
                       AND  1000 
                       EACH    1) ADD 5);
                                  -- 1000 partitions.
Note the following preliminary properties of the partitioning levels for this table.
  • Partitioning Level 1

    Table t38 is defined so that partitioning level 1 has 3 user-specified partitions: 1 each for column a, column b, and column c; plus the 2 internal partitions for a total of 5 defined partitions. By default, the ADD constant for partitioning level 1 is 10.

    The maximum number of column partitions for partitioning level 1 is 15, which is derived by adding the initial 5 defined partitions to the default ADD constant of 10.

    The maximum column partition number for level 1 is 16. This is derived by adding 1 to the maximum number of column partitions for the level, or 15 + 1 = 16.

Preliminary number of defined partitions 5
Default ADD constant value 10
Final ADD constant value 10
Default maximum number of column partitions 15
Final maximum number of column partitions 15
Preliminary maximum column partition number 15
Final maximum column partition number 16
  • Partitioning Level 2

    Because partitioning level 2 is defined using a RANGE_N function without specifying an EACH clause, its number of defined partitions is only 1. With an initial maximum number of column partitions for level 2 being only 1 because it does not specify an ADD constant. However, the maximum partitioning number for a partitioning level must be at least 2, so the actual maximum number of defined partitions for level 2 is 2, as is the maximum number of column partitions for the level.

    Preliminary number of defined partitions 1
    Default ADD constant value 1
    Final ADD constant value 3
    Default maximum number of defined column partitions 2
    Final maximum number of defined column partitions 4
    Preliminary maximum column partition number 4
    Final maximum column partition number 4
  • Partitioning Level 3

    Level 3 has 1,000 defined partitions and a preliminary maximum partition number of 1005. This is derived by adding the ADD constant value, 5, to the initially defined 1,000 partitions.

    Preliminary number of defined partitions 1,000
    Default ADD constant value 5
    Final ADD constant value 23
    Default maximum number of defined column partitions 1,005
    Final maximum number of defined column partitions 1,023
    Preliminary maximum column partition number 1,005
    Final maximum column partition number 1,023

Because the product of the default maximum column partition numbers for each level, 16*2*1005 = 32,160, this table defines a 2-byte partitioning because 32,160 ≤ 65,335.

DefinedCombinedPartitions = product of defined partitions at each level = 5*1*1,000 =5,000
MaxCombinedPartitions = product of maximum partitions at each level = 15*4*1,023 =61,380
Maximum Combined Partition Number = product of maximum partition number at each level = 65,472

While Maximum Combined Partition Number ≤ 65,535, increasing the maximum partition number for any of the partitioning levels by only 1 would cause the value of Maximum Combined Partition Number > 65,535, which would not be valid.

The following SELECT request reports the values for table t38 in DBC.TableConstraints after Vantage has made its adjustments to the number of combined partitions assigned to each partitioning level of t38.

     SELECT * 
     FROM DBC.TableConstraints
     WHERE TVMId IN (SELECT TVMId 
                     FROM DBC.TVM
                     WHERE TVMNameI = 't38' 
                     AND   DataBaseId IN (SELECT DatabaseId 
                                          FROM DBC.DBASE 
                                          WHERE DatabaseNameI ='test')) 
     ORDER BY TVMId;

This request returns the following result set.

                    TVMId 000017070000
                     Name ?
                  DBaseId 0000F303
               TableCheck CHECK (/*03 02 01*/ PARTITION#L1 /*1 5+10*/ =1 AND RANGE_N(c BETWEEN 1 AND 10 ) /*2 1+3*/ IS NOT NULL AND RANGE_N(b BETWEEN 1 AND 1000 EACH 1 ) /*3 1000+23*/ IS NOT NULL )
                CreateUID 00000100
          CreateTimeStamp 2010-12-12 18:16:38
      LastAccessTimeStamp  ?
              AccessCount  ?
           ConstraintType Q
              IndexNumber  1
      ConstraintCollation U
                 CollName ?
                CharSetID ?
              SessionMode ?
              VTCheckType ?
              TTCheckType ?
     ResolvedCurrent_Date ?
ResolvedCurrent_TimeStamp  ?
DefinedCombinedPartitions  5000
    MaxCombinedPartitions  61380
       PartitioningLevels  3
  ColumnPartitioningLevel  1