本文介绍了创建分区表的语法、各种类型分区表的示例等内容。
语法
CREATE TABLE [ schema. ]<table_name>
<table_definition>
PARTITION BY LIST(<column>)
[SUBPARTITION BY {RANGE|LIST|HASH} (<column>[, <column> ]...)]
(<list_partition_definition>[, <list_partition_definition>]...)
[ENABLE ROW MOVEMENT];
Where list_partition_definition is:
PARTITION [<partition_name>]
VALUES (<value>[, <value>]...)
[TABLESPACE <tablespace_name>]
[(<subpartition>, ...)]
1. Range Partitioning Syntax
CREATE TABLE [ schema. ]<table_name>
<table_definition>
PARTITION BY RANGE(<column>[, <column> ]...)
[SUBPARTITION BY {RANGE|LIST|HASH} (<column>[, <column> ]...)]
(<range_partition_definition>[, <range_partition_definition>]...)
[ENABLE ROW MOVEMENT];
Where range_partition_definition is:
PARTITION [<partition_name>]
VALUES LESS THAN (<value>[, <value>]...)
[TABLESPACE <tablespace_name>]
[(<subpartition>, ...)]
2. Hash Partitioning Syntax
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>]...)
[ENABLE ROW MOVEMENT];
Where hash_partition_definition is:
PARTITION [<partition_name>]
[TABLESPACE <tablespace_name>]
[(<subpartition>, ...)]
3. Subpartitioning Syntax
{<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:
SUBPARTITION [<subpartition_name>]
[TABLESPACE <tablespace_name>]
示例
创建范围分区表
CREATE TABLE sales(dept_no number, date date)
PARTITION BY RANGE(date)
(
PARTITION q1_2012 VALUES LESS THAN('2012-Apr-01'),
PARTITION q2_2012 VALUES LESS THAN('2012-Jul-01'),
PARTITION q3_2012 VALUES LESS THAN('2012-Oct-01'),
PARTITION q4_2012 VALUES LESS THAN('2013-Jan-01')
);
创建列表分区表
CREATE TABLE sales (dept_no number, country varchar2(20))
PARTITION BY LIST(country)
(
PARTITION europe VALUES('FRANCE', 'ITALY'),
PARTITION asia VALUES('INDIA', 'PAKISTAN'),
PARTITION americas VALUES('US', 'CANADA')
);
创建哈希分区表
CREATE TABLE sales (dept_no number, part_no varchar2)
PARTITION BY HASH (part_no)
(
PARTITION p1,
PARTITION p2,
PARTITION p3
);
创建二级分区表
说明
PolarDB PostgreSQL版(兼容Oracle)在创建分区表时需要指定分区,同样在创建分区时也可指定二级分区,Oracle语法中最大的分区级别为二级分区(也称为子分区)。
CREATE TABLE sales
(
dept_no number,
part_no varchar2,
country varchar2(20),
date date,
amount number
)
PARTITION BY RANGE(date)
SUBPARTITION BY LIST(country)
(
PARTITION q1_2012
VALUES LESS THAN('2012-Apr-01')
(
SUBPARTITION q1_europe VALUES ('FRANCE', 'ITALY'),
SUBPARTITION q1_asia VALUES ('INDIA', 'PAKISTAN'),
SUBPARTITION q1_americas VALUES ('US', 'CANADA')
),
PARTITION q2_2012
VALUES LESS THAN('2012-Jul-01')
(
SUBPARTITION q2_europe VALUES ('FRANCE', 'ITALY'),
SUBPARTITION q2_asia VALUES ('INDIA', 'PAKISTAN'),
SUBPARTITION q2_americas VALUES ('US', 'CANADA')
),
PARTITION q3_2012
VALUES LESS THAN('2012-Oct-01')
(
SUBPARTITION q3_europe VALUES ('FRANCE', 'ITALY'),
SUBPARTITION q3_asia VALUES ('INDIA', 'PAKISTAN'),
SUBPARTITION q3_americas VALUES ('US', 'CANADA')
),
PARTITION q4_2012
VALUES LESS THAN('2013-Jan-01')
(
SUBPARTITION q4_europe VALUES ('FRANCE', 'ITALY'),
SUBPARTITION q4_asia VALUES ('INDIA', 'PAKISTAN'),
SUBPARTITION q4_americas VALUES ('US', 'CANADA')
)
);
文档内容是否对您有帮助?