A table can be partitioned to increase performance and availability. Partitioning distributes the rows of a table among several 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 table with partitions, use the WITH PARTITION= clause in the CREATE TABLE statement.

You can manage traditional Ingres table partitions by splitting, merging, or dropping partitions.

You may need to modify the way partitions are divided if conditions change or the initial partitioning is incorrect. You can split one partition into two or more partitions, or merge two or more partitions into fewer partitions. Use the MODIFY PARTITION TO SPLIT and MODIFY PARTITION TO MERGE statements.

You can accomplish the same by modifying the table using a full partition definition, but that would require all partitions to be read and reloaded. Using the SPLIT modify-action, for example, will read and rewrite only the data in the split partition, and then rewrite the new partition definition to the catalogs. These MODIFY operations drop all secondary indexes and rebuild persistent indexes.

The MODIFY TO DROP statement drops the specified partitions. All data in the partitions is deleted.

For more information on creating and managing partitions, see the SQL Reference Guide.