Partitioned Transient Tables
A transient table can be partitioned to increase performance and availability. Partitioning distributes the rows of a transient table among a number of sub-tables (partitions). A partitioning scheme determines which rows are sent to which partitions.
After the partitioning scheme is defined, partitioning is managed automatically.
To define a transient table with partitions, use the PARTITION= option in the CREATE TRANSIENT TABLE WITH clause.
When creating transient tables, NOPARTITION is the default.
Partitioned transient tables have the following restrictions:
• Only one dimension can be specified.
• The distribution type must be HASH or AUTOMATIC.
• Transient tables cannot be created on specific locations.
Partitioning Schemes for Transient Tables
Each dimension of a partitioning scheme defines a rule (distribution scheme) for assigning rows to partitions. Conceptually, a dimension defines a set of logical partitions. Only one dimension is allowed.
Two distribution types are available:
• HASH: It distributes row evenly among the partitions by use of a hash value (instead of randomly). Given a value for the partitioning columns, a query can predict which partition contains the rows that have the matching value. Thus a query can restrict its search to a subset of partitions. HASH is data dependent and requires the ON clause.
The optional logical partition names must be unique for each transient table. The same partition name is allowed to occur in other partitioned transient tables. If a partition name is omitted, the system generates a name (of the form iipartnn).
The distribution scheme can be a default value set at the system level. For more information, see
Default Distribution Scheme for Transient Tables.
Partitioning Syntax
A transient table partition definition has the following format:
PARTITION = (dimension)
The syntax for each partition dimension is:
dimension = rule partitionspec {, partitionspec}
rule
Defines the type of distribution scheme for assigning rows to partitions. Valid values are:
HASH ON column {, column}
Distributes rows evenly among the partitions according to a hash value.
ON column {, column} specifies the columns to partition the transient table on.
AUTOMATIC
Distributes rows randomly among the partitions.
partitionspec
Defines the number of partitions and optionally their names:
partitionspec = [nn] PARTITION[S] [ ( name {, name} ) ]
where:
nn
Is the number of partitions, which defaults to 1 if omitted.
name
Identifies the partition. When the number of partitions is two or more, a comma-separated list of names can be provided to override the default value.
Default: iipartNN
Guidelines for Partitioned Transient Tables
Follow these guidelines when creating a partitioned transient table:
• The partition key should be chosen from columns that have uniform values, for example, primary/foreign keys.
• When you expect to have a large number of queries that join transient tables A and B on the condition A.fk_col = B.col, good partitioning keys for A and B are fk_col and col, respectively.
• A query on a partitioned transient table with X partitions will use at least X threads (even if the max_parallelism_level in the configuration file or query statement is lower). This may influence throughput performance when many queries on partitioned transient tables with a large number of partitions are run concurrently.
As a consequence of the previous limitation, the number of partitions for a given transient table should not be larger than the number of cores available. Choose the number of partitions for any transient table as a divisor of the number of cores available. More specifically, if you expect most often to have X concurrent queries on transient table A, then the number of partitions for transient table A should be calculated as:
#partitions = (no_cores_available / X)
Examples:
– On a 12-core system you expect to have on average 2 concurrent queries on transient table A: a good number of partitions for A is 6.
– On a 12-core system you will run only 1 query at a time: you can use 12 partitions for each transient table you define.
• Creating an index on the columns that are used to define a foreign relationship is not allowed when the transient tables joined by the foreign relationship do not have the same number of partitions or are not partitioned on the columns (or a matching subset) used for the foreign key relationship. For example:
The following is allowed:
CREATE TRANSIENT TABLE X (a i4 NOT NULL,
b i4 NOT NULL,
c i4 NOT NULL)
WITH PARTITION=(HASH ON a,c 2 PARTITIONS);
ALTER TABLE X ADD CONSTRAINT pk_x PRIMARY KEY (a,c);
CREATE TRANSIENT TABLE Y (c i4 NOT NULL,
d i4 NOT NULL,
e i4)
WITH PARTITION=(HASH ON d,e 2 PARTITIONS);
ALTER TABLE Y ADD CONSTRAINT fk_y FOREIGN KEY(d,e) REFERENCES X(a,c);
CREATE INDEX idx_y ON Y(d,e);
Partitioning keys on c for X and e for Y is also valid.
The following is not allowed:
CREATE TRANSIENT TABLE X (a i4 not null,
b i4 not null)
WITH
PARTITION=(HASH on a 2 PARTITIONS,
1 PARTITION,
1 PARTITION);
ALTER TABLE X ADD CONSTRAINT pk_x PRIMARY KEY (a);
CREATE TRANSIENT TABLE Y (c i4 NOT NULL,
d i4 NOT NULL)
WITH PARTITION=(HASH ON d 2 PARTITIONS);
ALTER TABLE Y ADD CONSTRAINT fk_y FOREIGN KEY(d) REFERENCES X(a);
CREATE INDEX idx_y ON Y (d);
Automatic Partitioning for Transient Tables
The AUTOMATIC partitioning scheme randomly distributes rows evenly among the partitions. Unlike hash-distributed transient tables, rows with equal values are not guaranteed to be assigned to the same partition. As a result, the system typically must reorganize the data before resolving the query. For example, joining two automatically partitioned transient tables usually requires reshuffling the rows. This extra step can degrade query performance.
AUTOMATIC partitioning should be used in the following cases:
• When insight is lacking for creating a good hash key. That is, when:
– There is no obvious joining key
– There is no good candidate for hash distributing the transient table (arbitrary data)
– The transient table does not share a common join key with other transient tables
– The transient table is a temporary staging transient table
• When defining an automatic partitioned transient table as a preparatory step before creating a good hash key, you can use GENERATE STATISTICS, and then SELECT to get the minimum and maximum column values and COUNTs to better choose which columns to use as the HASH distribution key.
Automatic Partitioning Limitations
Automatic partitioning has the following limitations:
• AS UNIQUE and FOREIGN KEY constraint checks cannot be done locally for concurrent transactions with update operations on the same transient table. The first committed transaction wins, while the second triggers a constraint violation (when both contain update operations). The second transaction must be reissued.
• A partial MODIFY TO COMBINE operation may be slower due to extra network traffic.
• A full MODIFY TO COMBINE operation can redistribute tuples in the cluster.
• A MODIFY TO RECONSTRUCT can only be performed once per transient table per transaction.
If the x100_partition_scheme is set to AUTO, certain operations might cause the transient table to be repartitioned using MODIFY TO RECONSTRUCT. An example operation is adding a primary key. You should commit any prior MODIFY statements before running these operations.