10.3 Partitioning Commands Compatible with Oracle Databases

Table of Contents Previous Next


10 Table Partitioning : 10.3 Partitioning Commands Compatible with Oracle Databases

Use the PARTITION BY clause of the CREATE TABLE command to create a partitioned table with data distributed amongst one or more partitions (and subpartitions). The command syntax comes in the following forms:
CREATE TABLE [ schema. ]table_name
table_definition
(list_partition_definition[, list_partition_definition]...);
Where list_partition_definition is:
PARTITION [partition_name]
VALUES (value[, value]...)
[TABLESPACE tablespace_name]
[(subpartition, ...)]
CREATE TABLE [ schema. ]table_name
table_definition
PARTITION BY RANGE(
column[, column ]...)
(range_partition_definition[, range_partition_definition]...);
Where range_partition_definition is:
PARTITION [partition_name]
VALUES LESS THAN (value[, value]...)
[TABLESPACE tablespace_name]
[(subpartition, ...)]
CREATE TABLE [ schema. ]table_name
table_definition
PARTITION BY HASH(column[, column ]...)
[SUBPARTITION BY {RANGE|LIST|HASH} (
column[, column ]...)]
(
hash_partition_definition[, hash_partition_definition]...);
Where hash_partition_definition is:
[PARTITION partition_name]
[TABLESPACE tablespace_name]
[(
subpartition, ...)]
subpartition may be one of the following:
{list_subpartition | range_subpartition | hash_subpartition}
where list_subpartition is:
SUBPARTITION [subpartition_name]
VALUES (value[, value]...)
[TABLESPACE tablespace_name]
where range_subpartition is:
SUBPARTITION [subpartition_name]
VALUES LESS THAN (value[, value]...)
[TABLESPACE tablespace_name]
where hash_subpartition is:
The CREATE TABLE… PARTITION BY command creates a table with one or more partitions; each partition may have one or more subpartitions. There is no upper limit to the number of defined partitions, but if you include the PARTITION BY clause, you must specify at least one partitioning rule. The resulting table will be owned by the user that creates it.
Use the PARTITION BY LIST clause to divide a table into partitions based on the values entered in a specified column. Each partitioning rule must specify at least one literal value, but there is no upper limit placed on the number of values you may specify. Include a rule that specifies a matching value of DEFAULT to direct any un-qualified rows to the given partition; for more information about using the DEFAULT keyword, see Section 10.4.
Use the PARTITION BY RANGE clause to specify boundary rules by which to create partitions. Each partitioning rule must contain at least one column of a data type that has two operators (i.e., a greater-than or equal to operator, and a less-than operator). Range boundaries are evaluated against a LESS THAN clause and are non-inclusive; a date boundary of January 1, 2013 will include only those date values that fall on or before December 31, 2012.
Range partition rules must be specified in ascending order. INSERT commands that store rows with values that exceed the top boundary of a range-partitioned table will fail unless the partitioning rules include a boundary rule that specifies a value of MAXVALUE. If you do not include a MAXVALUE partitioning rule, any row that exceeds the maximum limit specified by the boundary rules will result in an error.
For more information about using the MAXVALUE keyword, see Section 10.4.
Use the PARTITION BY HASH clause to create a hash-partitioned table. In a HASH partitioned table, data is divided amongst equal-sized partitions based on the hash value of the column specified in the partitioning syntax. When specifying a HASH partition, choose a column (or combination of columns) that is as close to unique as possible to help ensure that data is evenly distributed amongst the partitions. When selecting a partitioning column (or combination of columns), select a column (or columns) that you frequently search for exact matches for best performance.
Use the TABLESPACE keyword to specify the name of a tablespace on which a partition or subpartition will reside; if you do not specify a tablespace, the partition or subpartition will reside in the default tablespace.
If a table definition includes the SUBPARTITION BY clause, each partition within that table will have at least one subpartition. Each subpartition may be explicitly defined or system-defined.
A DEFAULT subpartition if the SUBPARTITION BY clause specifies LIST.
A MAXVALUE subpartition if the SUBPARTITION BY clause specifies RANGE.
Note: For Advanced Server 10, an index cannot be created on a partitioned table. For example on the partitioned table named sales, you will get the following error message:
(value[, value]...)
Use value to specify a quoted literal value (or comma-delimited list of literal values) by which table entries will be grouped into partitions. Each partitioning rule must specify at least one value, but there is no limit placed on the number of values specified within a rule. value may be NULL, DEFAULT (if specifying a LIST partition), or MAXVALUE (if specifying a RANGE partition).
When specifying rules for a list-partitioned table, include the DEFAULT keyword in the last partition rule to direct any un-matched rows to the given partition. If you do not include a rule that includes a value of DEFAULT, any INSERT statement that attempts to add a row that does not match the specified rules of at least one partition will fail, and return an error.
When specifying rules for a list-partitioned table, include the MAXVALUE keyword in the last partition rule to direct any un-categorized rows to the given partition. If you do not include a MAXVALUE partition, any INSERT statement that attempts to add a row where the partitioning key is greater than the highest value specified will fail, and return an error.
The following example creates a partitioned table (sales) using the PARTITION BY LIST clause. The sales table stores information in three partitions (europe, asia, and americas):
Rows with a value of US or CANADA in the country column are stored in the americas partition.
Rows with a value of INDIA or PAKISTAN in the country column are stored in the asia partition.
Rows with a value of FRANCE or ITALY in the country column are stored in the europe partition.
The following example creates a partitioned table (sales) using the PARTITION BY RANGE clause. The sales table stores information in four partitions (q1_2012, q2_2012, q3_2012 and q4_2012) :
Any row with a value in the date column before April 1, 2012 is stored in a partition named q1_2012.
Any row with a value in the date column before July 1, 2012 is stored in a partition named q2_2012.
Any row with a value in the date column before October 1, 2012 is stored in a partition named q3_2012.
Any row with a value in the date column before January 1, 2013 is stored in a partition named q4_2012.
The following example creates a partitioned table (sales) using the PARTITION BY HASH clause. The sales table stores information in three partitions (p1, p2, and p3:
The server will evaluate the hash value of the part_no column, and distribute the rows into approximately equal partitions.
The following example creates a partitioned table (sales) that is first partitioned by the transaction date; the range partitions (q1_2012, q2_2012, q3_2012 and q4_2012) are then list-subpartitioned using the value of the country column.
When a row is added to this table, the value in the date column is compared to the values specified in the range partitioning rules, and the server selects the partition in which the row should reside. The value in the country column is then compared to the values specified in the list subpartitioning rules; when the server locates a match for the value, the row is stored in the corresponding subpartition.

10 Table Partitioning : 10.3 Partitioning Commands Compatible with Oracle Databases

Table of Contents Previous Next