Table Partitions
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.
Partition Management
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.
Ad hoc Auto Partitioning
Auto partitioning is controlled by the x100_partition_scheme dbms parameter in the config.dat file. This parameter determines the default partition scheme to be used if the WITH PARTITION clause is not specified by the user.
The valid values for the partitioning scheme are given below:
• NONE: WITH NOPARTITION
• HASH: WITH PARTITION = (HASH on <first column> ...)
• AUTO: WITH PARTITION = (AUTOMATIC ... )
where AUTOMATIC uses a heuristic to decide if a HASH partition should be used, and the columns to be used for HASH partitioning.
The default is 'AUTO'.
Note: The default number of partitions (default_npartitions) is zero which turns off auto partitioning.
The following situations trigger the heuristic and may result in a HASH partitioned table:
• Modifying a table to reconstruct with PARTITION = (AUTOMATIC...) if run on a table that has primary or unique key.
• Adding a primary or unique key to a table using AUTOMATIC partitioning.
The 'Help table' indicates if a table was changed into a hash table due to automatic partitioning heuristics in a table.
The partition information is shown below:
create table person (age integer, id char(12) unique) with partition = (AUTOMATIC 4 partitions)\g
Table partitioning definition:
partition = (HASH(AUTO) on id
4 partitions
)
Last modified date: 08/14/2024