SAND CDBMS Administration Guide
Massively Parallel Processing (MPP)

 

Previous Topic:
Setting Up the MPP Environment
Chapter Index
Next Topic:
Modifying Partitions and Partitioned Tables

 

Creating Partitioned Tables

There are alternative ways to create new partitioned tables, but they can be performed only in MPP mode and require existing partitioned tables:

The first method involves a CREATE TABLE...SELECT statement that queries partitioned and dimension tables. On each remote node associated with the default partitioned table, the result of the SELECT statement on that node will define and populate a new table, which will have the same name and belong to the same schema as the partitioned table being created on the head node. On the head node, a new linked table will be generated automatically (named "<partitioned table>_<connection object>") for each new remote table. The new partitioned table on the head node will use (via the associated linked tables) the newly created remote tables as its partitions. The partitioning strategy and key(s), if any, of the partitioned table in the SELECT statement is inherited by the new partitioned table. The SELECT statement in this case is limited to partitioned and dimension tables only; attempting to join a local standard table with a partitioned table, for instance, will produce an error.

Note that, in MPP mode, if the subquery of the CREATE TABLE...SELECT does not reference a partitioned table, a new dimension table will be created instead.

A second method involves defining the partitioned table structure with the CREATE PARTITION TABLE command first, and then executing an INSERT...SELECT statement to populate and add the remote table partitions. The CREATE PARTITION TABLE command in this case differs from what was described above, insofar as standard column definitions are provided in place of a list of linked tables (partitions). The partitioned table structure is automatically copied with the same name to all of the nodes associated with the default partitioned table, and a linked table is generated (named "<partitioned table>_<connection object>") on the head node for each of these new remote tables. When a subsequent INSERT...SELECT command is issued on the head node with the new partitioned table as the target, the command will be sent to each of the remote nodes for execution. The data returned by the subquery will then be inserted into that node's table partition.