- partitioning_level
- A partitioning level can be defined using a single COLUMN keyword, a partitioning expression, or a combination of both.
- 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.
- (COLUMN column_name)
- The column partition is stored with COLUMN format.
- (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.
- 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.
- 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.
- 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.
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.
- 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 |