SAND CDBMS Administration Guide
Massively Parallel Processing (MPP)

Modifying Partitions and Partitioned Tables

After it is created, a partitioned table's structure and remote contents can be modified. The following SQL commands can be executed against a partitioned table:

• INSERT...SELECT : insert the records returned from the queried partitioned and/or dimension table(s) into the remote tables associated with the target partitioned table
• DELETE : removes the records that satisfy the DELETE statement's conditions from all of the remote tables associated with the partitioned table
• UPDATE : changes the values of specified columns in the remote tables, using expressions, values from other tables, or update files
• ALTER TABLE : modify the structure of the partitioned table (for instance, add columns, drop columns, rename columns, and so on)

Note:
The INSERT command with a value list cannot be executed against a partitioned table at this time.

The SAND Data Loader can add new records to a partitioned table by loading the data from files or ODBC data sources, using standard IMPORT jobs. While the load operation is executed against the partitioned table on the head node, the data is actually pushed to the remote tables associated with the partitioned table. In the course of the load operation, ndlm will:

1. retrieve the partitioning method (HASH, RANGE, or ROUND ROBIN) from the system metadata for the partitioned table
2. partition the data according to the partitioning method
3. load the partitioned data into the appropriate remote tables.

In order to maintain the consistency of the partitioned data, new records should never be inserted manually into the remote table partitions, and SAND Data Loader operations should never be performed directly on the remote table partitions. New records should always be imported into the partitioned table on the head node via the SAND Data Loader.