CREATE TABLE…PARTITION BY_与 Oracle 数据库兼容的分区命令_表分区_兼容Oracle数据库开发指南_云数据库PolarDB
CREATE TABLE…PARTITION BY
使用 CREATE TABLE 命令的 PARTITION BY 子句来使用分布在一个或多个分区(和二级分区)中的数据创建已分区表。该命令语法有以下几种形式:
- 按列表分区的语法
使用第一种形式来创建按列表分区的表:
CREATE TABLE [ schema. ]table_name table_definition PARTITION BY LIST(column) [SUBPARTITION BY {RANGE|LIST|HASH} (column[, column ]...)] (list_partition_definition[, list_partition_definition]...);
其中 list_partition_definition 为:
PARTITION [partition_name] VALUES (value[, value]...) [TABLESPACE tablespace_name] [(subpartition, ...)]
- 按范围分区的语法
使用第二种形式来创建按范围分区的表:
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]...);
其中 range_partition_definition 为:
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]...);
其中 hash_partition_definition 为:
[PARTITION partition_name] [TABLESPACE tablespace_name] [(subpartition, ...)]
- 二级分区语法
subpartition 可能是以下几种之一:
{list_subpartition | range_subpartition | hash_subpartition}
其中 list_subpartition 为:
SUBPARTITION [subpartition_name] VALUES (value[, value]...) [TABLESPACE tablespace_name]
其中 range_subpartition 为:
SUBPARTITION [subpartition_name] VALUES LESS THAN (value[, value]...) [TABLESPACE tablespace_name]
其中 hash_subpartition 为:
[SUBPARTITION subpartition_name] [TABLESPACE tablespace_name]
说明
CREATE TABLE… PARTITION BY 命令创建具有一个或多个分区的表;每个分区可能具有一个或多个二级分区。定义的分区数没有上限,但如果包括 PARTITION BY 子句,则您必须至少指定一个分区规则。生成的表将由创建它的用户所有。
使用 PARTITION BY LIST 子句来根据指定列中所输入的值将表划分为分区。每个分区规则都必须至少指定一个文本值,但您可以指定的值的数量没有上限。包括指定 DEFAULT 的匹配值的规则来将任何不合格行定向到给定分区。
使用 PARTITION BY RANGE 子句指定创建分区所依据的边界规则。每个分区规则都必须至少包含一个具有两个运算符(即,大于或等于运算符和小于运算符)的数据类型的列。将根据 LESS THAN 子句对范围边界进行计算,并且范围边界是非包含式;2013 年 1 月 1 日的日期边界将仅包括 2012 年 12 月 31 日或之前的那些日期值。
必须按升序指定范围分区规则。存储其值超过按范围分区的表的上边界的行的 INSERT 命令将失败,除非分区规则包括指定 MAXVALUE 的值的边界规则。如果未包括 MAXVALUE 分区规则,则任何超过边界规则所指定的上限的行都将导致错误。
使用 PARTITION BY HASH 子句创建按哈希分区的表。在按 HASH 分区的表中,根据分区语法中指定的列的哈希值,在大小相等的分区中划分数据。在指定 HASH 分区时,选择尽可能唯一的列(或列组合)来帮助确保数据均匀分布在各分区中。在选择分区依据列(或列组合)时,选择频繁在其中搜索完全匹配项的一列(或多列)以实现最佳性能。 注意:如果您升级 POLARDB for Oracle,则必须在升级版的服务器上重新生成按哈希分区的表。
使用 TABLESPACE 关键字来指定分区或二级分区将位于的表空间的名称;如果您没有指定表空间,则分区或二级分区将位于默认表空间中。
如果表定义包括 SUBPARTITION BY 子句,则该表中的每个分区将至少具有一个二级分区。每个二级分区可能是显式定义的或系统定义的。
如果二级分区是系统定义的,则服务器生成的二级分区将位于默认表空间中,并且服务器将分配二级分区的名称。服务器将创建:
- DEFAULT 二级分区(如果 SUBPARTITION BY 子句指定 LIST)。
- MAXVALUE 二级分区(如果 SUBPARTITION BY 子句指定 RANGE)。
服务器将生成由分区表名称和唯一标识符组合而成的二级分区名称。您可以查询 ALL_TAB_SUBPARTITIONS 表来查看二级分区名称的完整列表。
参数
参数 | 说明 |
---|---|
table_name | 要创建的表的名称(可能是 schema 限定的)。 |
table_definition | PostgreSQL 核心文档中为 CREATE TABLE 语句介绍的列名、数据类型和约束信息,网址为:
https://www.postgresql.org/docs/11/static/sql-createtable.html。 |
partition_name | 要创建的分区的名称。分区名称必须在所有分区和二级分区中是唯一的,并且必须遵循对象标识符的命名约定。 |
subpartition_name | 要创建的二级分区的名称。二级分区名称必须在所有分区和二级分区中是唯一的,并且必须遵循对象标识符的命名约定。 |
column | 分区规则所基于的列的名称。每行将存储在与指定列的 value 对应的分区中。 |
(value[, value]...) | 使用 value 指定将表条目分组到分区中所依据的用引号括起来的文本值(或文本值的逗号分隔列表)。每个分区规则都必须至少指定一个值,但对规则中指定的值的数量没有限制。value 可能是 NULL、DEFAULT(如果指定 LIST 分区)或 MAXVALUE(如果指定 RANGE 分区)。 |
tablespace_name | 分区或二级分区所在的表空间的名称。 |
在为按列表分区的表指定规则时,请在最后一个分区规则中包括 DEFAULT 关键字以将任何不匹配的行定向到给定分区。如果您没有规则包括值 DEFAULT,则尝试添加与至少一个分区的指定规则不匹配的行的任何 INSERT 语句将失败并返回错误。
在为按列表分区的表指定规则时,请在最后一个分区规则中包括 MAXVALUE 关键字以将任何未分类的行定向到给定分区。如果您没有包括 MAXVALUE 分区,则尝试添加分区键大于所指定最高值的行的任何 INSERT 语句将失败并返回错误。
示例 - PARTITION BY LIST
以下示例使用 PARTITION BY LIST 子句创建已分区表 (sales)。sales 表将信息存储在三个分区(europe、asia 和 americas)中:
CREATE TABLE sales
(
dept_no number,
part_no varchar2,
country varchar2(20),
date date,
amount number
)
PARTITION BY LIST(country)
(
PARTITION europe VALUES('FRANCE', 'ITALY'),
PARTITION asia VALUES('INDIA', 'PAKISTAN'),
PARTITION americas VALUES('US', 'CANADA')
);
按 country 列中指定的值对生成的表进行分区:
acctg=# SELECT partition_name, high_value from ALL_TAB_PARTITIONS;
partition_name | high_value
----------------+-------------------------------------
EUROPE | FOR VALUES IN ('FRANCE', 'ITALY')
ASIA | FOR VALUES IN ('INDIA', 'PAKISTAN')
AMERICAS | FOR VALUES IN ('US', 'CANADA')
(3 rows)
- country 列中的值为 US 或 CANADA 的行存储在 americas 分区中。
- country 列中的值为 INDIA 或 PAKISTAN 的行存储在 asia 分区中。
- country 列中的值为 FRANCE 或 ITALY 的行存储在 europe 分区中。
服务器将根据分区规则计算以下语句,然后将行存储在 europe 分区中:
INSERT INTO sales VALUES (10, '9519a', 'FRANCE', '18-Aug-2012', '650000');
示例 - PARTITION BY RANGE
以下示例使用 PARTITION BY RANGE 子句创建已分区表 (sales)。sales 表将信息存储在四个分区(q1_2012、q2_2012、q3_2012 和 q4_2012)中:
CREATE TABLE sales
(
dept_no number,
part_no varchar2,
country varchar2(20),
date date,
amount number
)
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'));
按 date 列中指定的值对生成的表进行分区:
acctg=# SELECT partition_name, high_value from ALL_TAB_PARTITIONS;
partition_name | high_value
----------------+------------------------------------------------------------
Q1_2012 | FOR VALUES FROM (MINVALUE) TO ('01-APR-12 00:00:00')
Q2_2012 | FOR VALUES FROM ('01-APR-12 00:00:00') TO ('01-JUL-12 00:00:00')
Q3_2012 | FOR VALUES FROM ('01-JUL-12 00:00:00') TO ('01-OCT-12 00:00:00')
Q4_2012 | FOR VALUES FROM ('01-OCT-12 00:00:00') TO ('01-JAN-13 00:00:00')
(4 rows)
- date 列中的值在 2012 年 4 月 1 日之前的所有行存储在名为 q1_2012 的分区中。
- date 列中的值在 2012 年 7 月 1 日之前的所有行存储在名为 q2_2012 的分区中。
- date 列中的值在 2012 年 10 月 1 日之前的所有行存储在名为 q3_2012 的分区中。
- date 列中的值在 2013 年 1 月 1 日之前的所有行存储在名为 q4_2012 的分区中。
服务器将根据分区规则计算以下语句,然后将行存储在 q3_2012 分区中:
INSERT INTO sales VALUES (10, '9519a', 'FRANCE', '18-Aug-2012', '650000');
示例 - PARTITION BY HASH
以下示例使用 PARTITION BY HASH 子句创建已分区表 (sales)。sales 表将信息存储在三个分区(p1、p2 和 p3)中:
CREATE TABLE sales
(
dept_no number,
part_no varchar2,
country varchar2(20),
date date,
amount number
)
PARTITION BY HASH (part_no)
(
PARTITION p1,
PARTITION p2,
PARTITION p3
);
按 part_no 列中指定的值的哈希值对表进行分区:
acctg=# SELECT partition_name, high_value from ALL_TAB_PARTITIONS;
partition_name | high_value
----------------+------------------------------------------
P1 | FOR VALUES WITH (modulus 3, remainder 0)
P2 | FOR VALUES WITH (modulus 3, remainder 1)
P3 | FOR VALUES WITH (modulus 3, remainder 2)
(3 rows)
服务器将计算 part_no 列的哈希值,然后将行分配到大致相等的分区中。
示例 - PARTITION BY RANGE、SUBPARTITION BY LIST
以下示例创建首先按交易日期进行分区的已分区表 (sales);然后使用 country 列的值对范围分区(q1_2012、q2_2012、q3_2012 和 q4_2012)进行按列表进行的二级分区。
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')
)
);
此语句创建具有四个分区的表;每个分区具有三个二级分区:
acctg=# SELECT subpartition_name, high_value, partition_name FROM ALL_TAB_SUBPARTITIONS;
subpartition_name | high_value | partition_name
-------------------+-------------------------------------+----------------
Q1_AMERICAS | FOR VALUES IN ('US', 'CANADA') | Q1_2012
Q1_ASIA | FOR VALUES IN ('INDIA', 'PAKISTAN') | Q1_2012
Q1_EUROPE | FOR VALUES IN ('FRANCE', 'ITALY') | Q1_2012
Q2_AMERICAS | FOR VALUES IN ('US', 'CANADA') | Q2_2012
Q2_ASIA | FOR VALUES IN ('INDIA', 'PAKISTAN') | Q2_2012
Q2_EUROPE | FOR VALUES IN ('FRANCE', 'ITALY') | Q2_2012
Q3_AMERICAS | FOR VALUES IN ('US', 'CANADA') | Q3_2012
Q3_ASIA | FOR VALUES IN ('INDIA', 'PAKISTAN') | Q3_2012
Q3_EUROPE | FOR VALUES IN ('FRANCE', 'ITALY') | Q3_2012
Q4_AMERICAS | FOR VALUES IN ('US', 'CANADA') | Q4_2012
Q4_ASIA | FOR VALUES IN ('INDIA', 'PAKISTAN') | Q4_2012
Q4_EUROPE | FOR VALUES IN ('FRANCE', 'ITALY') | Q4_2012
(12 rows)
在向此表中添加行时,会将 date 列中的值与按范围分区规则中指定的值进行比较,然后服务器选择该行应位于的分区。然后将 country 列中的值与按列表进行二级分区规则中指定的值进行比较;当服务器找到值的匹配项后,会将该行存储在相应二级分区中。
添加到表中的任何行都将存储在二级分区中,因此分区将不包含任何数据。
服务器将根据分区和二级分区规则计算以下语句,然后将行存储在 q3_europe 分区中:
INSERT INTO sales VALUES (10, '9519a', 'FRANCE', '18-Aug-2012', '650000');
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论