4. SQL Statements : CREATE TABLE : Partitioned Tables : Guidelines for Partitioned Tables
 
Share this page                  
Guidelines for Partitioned Tables
Follow these guidelines when creating a partitioned table:
The locations for partitions should be different to exploit the I/O performance of different devices.
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 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.
When you expect to join tables A and B often, the locations specified for tables A and B should be the same.
A query on a partitioned 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 tables with a large number of partitions are run concurrently.
As a consequence of the previous limitation, the number of partitions for a given table should not be larger than the number of cores available. Choose the number of partitions for any table as a divisor of the number of cores available. More specifically, if you expect most often to have X concurrent queries on table A, then the number of partitions for 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 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 table you define.
Creating an index on the columns that are used to define a foreign relationship is not allowed when the 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 TABLE X (a i4 NOT NULL,
                b i4 NOT NULL,
                c i4 NOT NULL)
                WITH LOCATION=(db_loc1),
                PARTITION=(HASH ON a,c 2 PARTITIONS);
ALTER TABLE X ADD CONSTRAINT pk_x PRIMARY KEY (a,c);
CREATE TABLE Y (c i4 NOT NULL,
                d i4 NOT NULL,
                e i4)
                WITH PARTITION=(HASH ON d,e 2 PARTITIONS WITH LOCATION=(db_loc1));
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 TABLE X (a i4 not null,
                b i4 not null)
                WITH
                PARTITION=(HASH on a 2 PARTITIONS,
                                     1 PARTITION WITH LOCATION=(db_loc2),
                                     1 PARTITION);
ALTER TABLE X ADD CONSTRAINT pk_x PRIMARY KEY (a);
CREATE TABLE Y (c i4 NOT NULL,
                d i4 NOT NULL)
                WITH PARTITION=(HASH ON d 2 PARTITIONS WITH LOCATION=(db_loc1));
ALTER TABLE Y ADD CONSTRAINT fk_y FOREIGN KEY(d) REFERENCES X(a);
CREATE INDEX idx_y ON Y (d);