10.4 Handling Stray Values in a LIST or RANGE Partitioned Table

Table of Contents Previous Next


10 Table Partitioning : 10.4 Handling Stray Values in a LIST or RANGE Partitioned Table

A DEFAULT or MAXVALUE partition or subpartition will capture any rows that do not meet the other partitioning rules defined for a table.
A DEFAULT partition will capture any rows that do not fit into any other partition in a LIST partitioned (or subpartitioned) table. If you do not include a DEFAULT rule, any row that does not match one of the values in the partitioning constraints will result in an error. Each LIST partition or subpartition may have its own DEFAULT rule.
The syntax of a DEFAULT rule is:
PARTITION [partition_name] VALUES (DEFAULT)
Where partition_name specifies the name of the partition or subpartition that will store any rows that do not match the rules specified for other partitions.
The last example created a list partitioned table in which the server decided which partition to store the data based upon the value of the country column. If you attempt to add a row in which the value of the country column contains a value not listed in the rules, Advanced Server reports an error:
The following example creates the same table, but adds a DEFAULT partition. The server will store any rows that do not match a value specified in the partitioning rules for europe, asia, or americas partitions in the others partition:
To test the DEFAULT partition, add row with a value in the country column that does not match one of the countries specified in the partitioning constraints:
Querying the contents of the sales table confirms that the previously rejected row is now stored in the sales_others partition:
You cannot use the ALTER TABLEADD PARTITION command to add a partition to a table with a DEFAULT rule, but you can use the ALTER TABLESPLIT PARTITION command to split an existing partition.
You cannot use the ALTER TABLEADD SUBPARTITION command to add a subpartition to a table with a DEFAULT rule, but you can use the ALTER TABLESPLIT SUBPARTITION command to split an existing subpartition.
A MAXVALUE partition (or subpartition) will capture any rows that do not fit into any other partition in a range-partitioned (or subpartitioned) table. If you do not include a MAXVALUE rule, any row that exceeds the maximum limit specified by the partitioning rules will result in an error. Each partition or subpartition may have its own MAXVALUE partition.
The syntax of a MAXVALUE rule is:
PARTITION [partition_name] VALUES LESS THAN (MAXVALUE)
Where partition_name specifies the name of the partition that will store any rows that do not match the rules specified for other partitions.
The last example created a range-partitioned table in which the data was partitioned based upon the value of the date column. If you attempt to add a row with a date that exceeds a date listed in the partitioning constraints, Advanced Server reports an error:
The following CREATE TABLE command creates the same table, but with a MAXVALUE partition. Instead of throwing an error, the server will store any rows that do not match the previous partitioning constraints in the others partition:
To test the MAXVALUE partition, add a row with a value in the date column that exceeds the last date value listed in a partitioning rule. The server will store the row in the others partition:
Querying the contents of the sales table confirms that the previously rejected row is now stored in the sales_others partition :
You cannot use the ALTER TABLEADD PARTITION statement to add a partition to a table with a MAXVALUE rule, but you can use the ALTER TABLESPLIT PARTITION statement to split an existing partition.
You cannot use the ALTER TABLEADD SUBPARTITION statement to add a subpartition to a table with a MAXVALUE rule , but you can split an existing subpartition with the ALTER TABLESPLIT SUBPARTITION statement.

10 Table Partitioning : 10.4 Handling Stray Values in a LIST or RANGE Partitioned Table

Table of Contents Previous Next