GaussDB分区表常规操作

举报
HuaweiCloudDeveloper 发表于 2024/12/09 19:43:37 2024/12/09
【摘要】 本文描述分区表分区策略以及优点。创建、修改与删除分区表的常规操作并列出相关的操作示例，方便对分区表的熟练掌握。

1 问题现象

GaussDB分区表是一种可以将大型的数据库表分割成更小，更易于管理的部分的技术，这样可以提高查询和维护的效率，并且还可以提升系统的可用性。

2 分区表介绍

分区表实际上就是一种数据组织方式，是把逻辑上的一张表根据某种方案分成几张物理块进行存储。这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。分区的小表独立管理，可以具有独立的索引和存储空间。分区表增强了数据库应用程序的性能、可管理性和可用性，并有助于降低存储大量数据的总体拥有成本。分区允许将表、索引和索引组织的表细分为更小的部分，使这些数据库对象能够在更精细的粒度级别上进行管理和访问。

GaussDB提供了丰富的分区策略和扩展，以满足不同业务场景的需求。由于分区策略的实现完全由数据库内部实现，对用户是完全透明的，因此它几乎可以在实施分区表优化策略以后做平滑迁移，无需潜在耗费人力物力的应用程序更改。

3 GaussDB支持的分区类型

GaussDB支持的分区表为一级分区表和二级分区表，其中一级分区表包括范围分区表、间隔分区表、列表分区表、哈希分区表四种，二级分区表包括范围分区、列表分区、哈希分区两两组合的九种。

一般情况下，我们只会使用到一级分区。

常见的分区方案有范围分区（Range Partitioning）、间隔分区（Interval Partitioning）、哈希分区（Hash Partitioning）、列表分区（List Partitioning）、数值分区（Value Partition）等。目前行存表支持范围分区、间隔分区、哈希分区、列表分区。

    • 范围分区表：

范围分区是根据表的一列或者多列，将要插入表的记录分为若干个范围，这些范围在不同的分区里没有重叠。为每个范围创建一个分区，用来存储相应的数据，将数据基于范围映射到每一个分区。这个范围是由创建分区表时指定的分区键决定的。分区键经常采用日期，例如将销售数据按照月份进行分区。

范围分区的分区策略是指记录插入分区的方式。目前范围分区仅支持范围分区策略。

范围分区策略：根据分区键值将记录映射到已创建的某个分区上，如果可以映射到已创建的某一分区上，则把记录插入到对应的分区上，否则给出报错和提示信息。这是最常用的分区策略。

    • 间隔分区表：

是一种特殊的范围分区表，相比范围分区表，新增间隔值定义，当插入记录找不到匹配的分区时，可以根据间隔值自动创建分区。

间隔分区只支持基于表的一列分区，并且该列只支持TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE数据类型。

间隔分区策略：根据分区键值将记录映射到已创建的某个分区上，如果可以映射到已创建的某一分区上，则把记录插入到对应的分区上，否则根据分区键值和表定义信息自动创建一个分区，然后将记录插入新分区中，新创建的分区数据范围等于间隔值。

    • 哈希分区表：

哈希分区是根据表的一列，为每个分区指定模数和余数，将要插入表的记录划分到对应的分区中，每个分区所持有的行都需要满足条件：分区键的值除以为其指定的模数将产生为其指定的余数。包含的分区个数由创建分区表时指定。

哈希分区策略：根据分区键值将记录映射到已创建的某个分区上，如果可以映射到已创建的某一分区上，则把记录插入到对应的分区上，否则返回报错和提示信息。

    • 列表分区表：

列表分区是根据表的一列，将要插入表的记录通过每一个分区中出现的键值划分到对应的分区中，这些键值在不同的分区里没有重叠。为每组键值创建一个分区，用来存储相应的数据。分区中包含的键值由创建分区表时指定。

列表分区策略：根据分区键值将记录映射到已创建的某个分区上，如果可以映射到已创建的某一分区上，则把记录插入到对应的分区上，否则给出报错和提示信息。

    • 二级分区表：

由范围分区、列表分区、哈希分区任意组合得到的分区表，其一级分区和二级分区均可以使用前面三种定义方式。

4 分区表优点

分区表和普通表相比具有以下优点：

    • 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。

    • 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。

    • 方便维护：对于大型表，数据维护变得非常困难。而分区表可以将数据分散到多个小表中，使得数据维护更加便捷。例如，对某个分区进行删除操作时，只需要删除该分区对应的小表即可。

    • 扩展性好 :随着业务的发展，数据量会不断增加。分区表可以根据业务需求动态调整分区数量，以满足更高的查询性能和存储需求。

    • 均衡I/O：可以把不同的分区映射到不同的磁盘以平衡I/O，改善整个系统性能。

说明：

普通表若要转成分区表，需要新建分区表，然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时，请根据业务提前规划是否使用分区表。

5 分区表具体使用

5.1 创建分区表

由于SQL语言功能强大和灵活多样性，SQL语法树通常比复杂，分区表同样如此，分区表的创建可以理解成在原有非分区表的基础上新增表分区属性，因此分区表的语法接口可以看成是对原有非分区表CREATE TABLE语句进行扩展PARTITION BY语句部分，同时指定分区相关的三个核元素：

A. 分区类型（partType）：描述分区表的分区策略，分别有RANGE/INTERVAL/LIST/HASH。

B. 分区键（partKey）：描述分区表的分区列，目前RANGE/LIST分区支持多列（不超过16列）分区键，INTERVAL/HASH分区只支持单列分区。

C. 分区表达式（partExpr）：描述分区表的具体分区表方式，即键值与分区的对应映射关系。

这三部分重要元素在建表语句的Partition By Clause字句中体现，PARTITION BY partType (partKey) ( partExpr[,partExpr]…)。

示例如下：

CREATE TABLE [ IF NOT EXISTS ] partition_table_name
(
[ /* 该部份继承于普通表的Create Table */
{ column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option [...] ] }[, ... ]
]
)
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ COMPRESS | NOCOMPRESS ]
[ TABLESPACE tablespace_name ]
/* 范围分区场景，若申明INTERVAL子句则为间隔分区场景 */
PARTITION BY RANGE (partKey) [ INTERVAL ('interval_expr') [ STORE IN (tablespace_name [, ... ] ) ] ] (
partition_start_end_item [, ... ]
partition_less_then_item [, ... ]
)
/* 列表分区场景，若申明AUTOMATIC则支持list自动扩展 */
PARTITION BY LIST (partKey) [ AUTOMATIC ]
(
PARTITION partition_name VALUES (list_values_clause) [ TABLESPACE tablespace_name [, ... ] ]
...
)
/* 哈希分区场景 */
PARTITION BY HASH (partKey) (
PARTITION partition_name [ TABLESPACE tablespace_name [, ... ] ]
...
)
/* 开启/关闭分区表行迁移 */
[ { ENABLE | DISABLE } ROW MOVEMENT ];

规格约束：

RANGE/LIST分区最大支持16个分区键，INTERVAL/HASH分区均只支持1个分区键，二级分区只支持1个分区键。

INTERVAL分区仅支持数值类型和日期/时间类型，INTERVAL分区不支持在二级分区表中创建。

INTERVAL分区表定义不能有MAXVALUE分区，LIST自动扩展分区表不能定义有DEFAULT分区。

除哈希分区外，分区键不能插入空值，否则DML语句会进行报错处理。唯一例外：RANGE分区表定义有MAXVALUE分区/LIST分区表定义有DEFAULT分区。

分区数最大值为1048575个，可以满足大部分业务场景的诉求。但分区数增加会导致系统中文件数增加，影响系统的性能，一般对于单个表而言不建议分区数超过200。

5.1.1 语法格式

CREATE TABLE [ IF NOT EXISTS ] partition_table_name
( [
{ column_name data_type [ CHARACTER SET | CHARSET charset ] [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option [...] ] }[, ... ]
] )
[ table_option [ [ , ] ... ] ]
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )]]
[ TABLESPACE tablespace_name ]
PARTITION BY {
{RANGE [COLUMNS] (partition_key) [ INTERVAL (interval_expr) [ STORE IN (tablespace_name [, ... ] ) ] ] [ PARTITIONS integer ] ( partition_less_than_item [, ... ] )} |
{RANGE [COLUMNS] (partition_key) [ INTERVAL (interval_expr) [ STORE IN (tablespace_name [, ... ] ) ] ] [ PARTITIONS integer ] ( partition_start_end_item [, ... ] )} |
{LIST [COLUMNS] (partition_key) [ AUTOMATIC ] [ PARTITIONS integer ] ( PARTITION partition_name VALUES [IN] (list_values) [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )]] [TABLESPACE [=] tablespace_name][, ... ]）} |
{{ HASH | KEY } (partition_key) [ PARTITIONS integer ] ( PARTITION partition_name [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR ) ]] [TABLESPACE [=] tablespace_name][, ... ]）}
} [ { ENABLE | DISABLE } ROW MOVEMENT ];

    • 其中table_option为：

{ COMMENT [ = ] 'string' |
AUTO_INCREMENT [ = ] value |
[ DEFAULT ] CHARACTER SET | CHARSET [ = ] default_charset |
[ DEFAULT ] COLLATE [ = ] default_collation }

    • 列约束column_constraint：

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL |
CHECK ( expression ) |
DEFAULT default_e xpr |
ON UPDATE update_expr |
GENERATED ALWAYS AS ( generation_expr ) [STORED] |
GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ] |
AUTO_INCREMENT |
COMMENT 'string' |
UNIQUE [KEY] index_parameters |
PRIMARY KEY index_parameters |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

    • 表约束table_constraint：

[ CONSTRAINT [ constraint_name ] ]
{ CHECK ( expression ) |
UNIQUE [ index_name ][ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters |
PRIMARY KEY [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters |
FOREIGN KEY [ index_name ] ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
{ [ COMMENT 'string' ] [ ... ] }

    • like选项like_option：

{ INCLUDING | EXCLUDING } { DEFAULTS | GENERATED | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | RELOPTIONS| UPDATE | IDENTITY | ALL }

    • 索引存储参数index_parameters：

[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

    • partition_less_than_item：

PARTITION partition_name VALUES LESS THAN {( { partition_value | MAXVALUE } [,...] ) | MAXVALUE } [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR ) ]] [TABLESPACE [=] tablespace_name]

    • partition_start_end_item：

PARTITION partition_name {
{START(partition_value) END (partition_value) EVERY (interval_value)} |
{START(partition_value) END ({partition_value | MAXVALUE})} |
{START(partition_value)} |
{END({partition_value | MAXVALUE})}
} [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR ) ]] [TABLESPACE [=] tablespace_name]

    • 其中update_expr为：

{ CURRENT_TIMESTAMP | LOCALTIMESTAMP | NOW() }

5.1.2 参数说明

    • IF NOT EXISTS

如果已经存在相同名称的表，不抛出错误，而是发出一个notice，告知表已存在。

    • partition_table_name

分区表的名称。

取值范围：字符串，要符合标识符命名规范 。

    • column_name

新表中要创建的字段名。

取值范围：字符串，要符合标识符命名规范 。

    • data_type

字段的数据类型。

    • COLLATE collation

COLLATE子句指定列的排序规则（该列必须是可排列的数据类型）。如果没有指定，则使用默认的排序规则。排序规则可以使用“SELECT * FROM pg_collation;”命令从pg_collation系统表中查询，默认的排序规则为查询结果中以default开始的行。

    • CONSTRAINT constraint_name

列约束或表约束的名称。可选的约束子句用于声明约束，新行或者更新的行必须满足这些约束才能成功插入或更新。

定义约束有两种方法：

D. 列约束：作为一个列定义的一部分，仅影响该列。

E. 表约束：不和某个列绑在一起，可以作用于多个列。在B模式数据库下（即sql_compatibility = 'B'）constraint_name为可选项，在其他模式数据库下，必须加上constraint_name。

    • index_name索引名

说明：

A. index_name仅在B模式数据库下（即sql_compatibility = 'B'）支持，其他模式数据库下不支持。

B. 对于外键约束，constraint_name和index_name同时指定时，索引名为constraint_name。

C. 对于唯一键约束，constraint_name和index_name同时指定时，索引名以index_name。

    • USING method

指定创建索引的方法。

取值范围参考参数说明 中的USING method。

说明：

A. USING method仅在B模式数据库下（即sql_compatibility = 'B'）支持，其他模式数据库下不支持。

B. 在B模式下，未指定USING method时，对于ASTORE的存储方式，默认索引方法为btree；对于USTORE的存储方式，默认索引方法为ubtree。

    • ASC | DESC

ASC表示指定按升序排序（默认）。DESC指定按降序排序。

说明：

ASC|DESC只在B模式数据库下（即sql_compatibility = 'B'）支持，其他模式数据库不支持。

    • LIKE source_table [ like_option ... ]

LIKE子句声明一个表，新表自动从这个表里面继承所有字段名及其数据类型和非空约束。

新表与原表之间在创建动作完毕之后是完全无关的。在原表做的任何修改都不会传播到新表中，并且也不可能在扫描原表的时候包含新表的数据。

A. 字段缺省表达式只有在声明了INCLUDING DEFAULTS之后才会包含进来。缺省是不包含缺省表达式的，即新表中所有字段的缺省值都是NULL。

B. 如果指定了INCLUDING UPDATE，则原表列的ON UPDATE CURRENT_TIMESTAMP属性会复制到新表列中。默认不复制该属性。

C. 如果指定了INCLUDING GENERATED，则原表列的生成表达式会复制到新表中。默认不复制生成表达式。

D. 非空约束将总是复制到新表中，CHECK约束则仅在指定了INCLUDING CONSTRAINTS的时候才复制，而其他类型的约束则永远也不会被复制。此规则同时适用于表约束和列约束。

被复制的列和约束并不使用相同的名称进行融合。如果明确的指定了相同的名称或者在另外一个LIKE子句中，将会报错。

A. 如果指定了INCLUDING INDEXES，则原表上的索引也将在新表上创建，默认不建立索引。

B. 如果指定了INCLUDING STORAGE，则原表列的STORAGE设置也将被复制，默认情况下不包含STORAGE设置。

C. 如果指定了INCLUDING COMMENTS，则原表列、约束和索引的注释也会被复制过来。默认情况下，不复制源表的注释。

D. 如果指定了INCLUDING RELOPTIONS，则原表的存储参数（即源表的WITH子句）也将复制至新表。默认情况下，不复制源原的存储参数。

E. 如果指定了INCLUDING IDENTITY，则原表的identity功能会复制到新表中，并创建一个与原表SEQUENCE参数相同的SEQUENCE。默认情况下，不复制原表的identity功能。

F. INCLUDING ALL包含了INCLUDING DEFAULTS、INCLUDING UPDATE、INCLUDING CONSTRAINTS、INCLUDING INDEXES、INCLUDING STORAGE、INCLUDING COMMENTS、INCLUDING PARTITION、INCLUDING RELOPTIONS和INCLUDING IDENTITY的内容。

说明：

A. “CREATE TABLE table_name LIKE source_table;”语法仅在B模式数据库（即sql_compatibility = 'B'）下，且参数b_format_version值为5.7、b_format_dev_version值为s2时支持。

B. 在B模式数据库下，且参数b_format_version值为5.7、b_format_dev_version值为s2时，不支持指定INCLUDING和EXCLUDING选项，缺省等同于指定INCLUDING ALL。

    • AUTO_INCREMENT [ = ] value

这个子句为自动增长列指定一个初始值，value必须为正整数，不得超过2127-1。

说明：

该子句仅在参数sql_compatibility='B'时有效。

    • COMMENT [ = ] 'string'

A. COMMENT [ = ] 'string'子句表示给表添加注释。

B. 在column_constraint中的COMMENT 'string'表示给列添加注释。

C. 在table_constraint中的COMMENT 'string'表示给主键和唯一键对应的索引添加注释。

    • CHARACTER SET | CHARSET charset

指定表字段的字符集。单独指定时会将字段的字符序设置为指定的字符集的默认字符序。

仅在B模式数据库下（即sql_compatibility = 'B'）支持该语法，其他模式数据库不支持。

    • COLLATE collation

COLLATE子句指定列的排序规则（该列必须是可排列的数据类型）。如果没有指定，则使用默认的排序规则。排序规则可以使用“SELECT * FROM pg_collation”命令从pg_collation系统表中查询，默认的排序规则为查询结果中以default开始的行。对于B模式数据库下（即sql_compatibility = 'B'）还支持utf8mb4_bin、utf8mb4_general_ci、utf8mb4_unicode_ci、binary、gbk_chinese_ci、gbk_bin、gb18030_chinese_ci、gb18030_bin字符序。

    • WITH ( storage_parameter [= value] [, ... ] )

这个子句为表或索引指定一个可选的存储参数。参数的详细描述如下所示：

A. FILLFACTOR

一个表的填充因子（fillfactor）是一个介于10~100的百分数。在Ustore存储引擎下，该值得默认值为92，在Astore存储引擎下默认值为100（完全填充）。如果指定了较小的填充因子，INSERT操作仅按照填充因子指定的百分率填充表页。每个页上的剩余空间将用于在该页上更新行，这就使得UPDATE有机会在同一页上放置同一条记录的新版本，这比把新版本放置在其他页上更有效。对于一个从不更新的表将填充因子设为100是最佳选择，但是对于频繁更新的表，选择较小的填充因子则更加合适。

取值范围：10~100

B. ORIENTATION

决定了表的数据的存储方式。

取值范围：

A. ROW（缺省值）：表的数据将以行式存储。

说明：

orientation不支持修改。

A. STORAGE_TYPE

指定存储引擎类型，该参数设置成功后就不再支持修改。

取值范围：

    • USTORE，表示表支持Inplace-Update存储引擎。特别需要注意，使用USTORE表，必须要开启track_counts和track_activities参数，否则会引起空间膨胀。

    • ASTORE，表示表支持Append-Only存储引擎。

默认值：

不指定时，由参数enable_default_ustore_table决定存储引擎方式，默认是Inplace-Update存储。

B. COMPRESSION

    • 行存表不支持压缩。

C. segment

使用段页式的方式存储。本参数仅支持行存表。不支持临时表、unlog表。

取值范围：on/off

默认值：off

D. statistic_granularity

记录该表在分析统计信息时的默认partition_mode，partition_mode说明详见ANALYZE|ANALYSE参数说明 ，此参数对非分区表设置无效。

取值范围：见partition_mode取值范围。

默认值：AUTO

E. enable_tde

指定该表为加密表。数据库会自动将加密表中的数据先加密再存储。使用该参数前，请确保已通过GUC参数enable_tde开启透明加密功能，并通过GUC参数tde_key_info设置访问密钥服务的信息，在《特性指南》中“透明数据加密”章节可获取该参数的详细使用方法。本参数仅支持行存表、段页式表、hashbucket表、临时表和unlogged表。

取值范围：on/off。设置enable_tde=on时，key_type、tde_cmk_id、dek_cipher参数由数据库自动生成，用户无法手动指定或更改。

默认值：off

F. encrypt_algo

指定加密表的加密算法，需与enable_tde结合使用。

取值范围：字符串，有效值为：AES_128_CTR，SM4_CTR。

默认值：不设置enable_tde选项时默认为空；设置enable_tde选项设置时，默认为AES_128_CTR。

G. dek_cipher

数据密钥的密文。用户为表设置enable_tde参数后，数据库自动生成数据密钥。

取值范围：字符串

默认值：空

H. key_type

主密钥的类型。用户为表设置enable_tde参数后，数据库自动从GUC参数tde_key_info中获取主密钥的类型。

取值范围：字符串

默认值：空

I. cmk_id

主密钥的ID。用户为表设置enable_tde参数后，数据库自动从GUC参数tde_key_info中获取主密钥的ID。

取值范围：字符串

默认值：空

[ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )]]

创建新表时，可以调用ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW给行存添加高级压缩策略，分区继承表的策略。

F. AFTER n { day | month | year } OF NO MODIFICATION ：表示n天/月/年没有修改的行。

G. ON ( EXPR )：行级表达式，用于判断行的冷热。

    • TABLESPACE tablespace_name

指定新表将要在tablespace_name表空间内创建。如果没有声明，将使用默认表空间。

    • PARTITION BY RANGE [COLUMNS] (partition_key)

创建范围分区。partition_key为分区键的名称。

COLUMNS关键字只能在sql_compatibility='B'时使用，“PARTITION BY RANGE COLUMNS” 语义同 “PARTITION BY RANGE”。

A. 对于从句是VALUES LESS THAN的语法格式：

说明：

对于从句是VALUES LESS THAN的语法格式，范围分区策略的分区键最多支持16列。

该情形下，分区键支持的数据类型为：TINYINT、SMALLINT、INTEGER、BIGINT、DECIMAL、NUMERIC、REAL、DOUBLE PRECISION、CHARACTER VARYING(n)、VARCHAR(n)、CHARACTER(n)、CHAR(n)、CHARACTER、CHAR、TEXT、NVARCHAR、NVARCHAR2、NAME、TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE。

B. 对于从句是START END的语法格式：

说明：

对于从句是START END的语法格式，范围分区策略的分区键仅支持1列。

该情形下，分区键支持的数据类型为：TINYINT、SMALLINT、INTEGER、BIGINT、DECIMAL、NUMERIC、REAL、DOUBLE PRECISION、TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE。

C. 对于指定了INTERVAL子句的语法格式：

说明：

对于指定了INTERVAL子句的语法格式，范围分区策略的分区键仅支持1列。

该情形下，分区键支持的数据类型为：TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE。

   • PARTITION partition_name VALUES LESS THAN {( { partition_value | MAXVALUE } [,...] ) | MAXVALUE }

指定各分区的信息。partition_name为范围分区的名称。partition_value为范围分区的上边界，取值依赖于partition_key的类型。MAXVALUE表示分区的上边界，它通常用于设置最后一个范围分区的上边界。

说明：

A. 每个分区都需要指定一个上边界。

B. 分区上边界的类型应当和分区键的类型一致。

C. 分区列表是按照分区上边界升序排列的，值较小的分区位于值较大的分区之前。

D. 不在括号内的MAVALUE只能在sql_compatibility='B'时使用，并且只能有一个分区键。

• PARTITION partition_name {START (partition_value) END (partition_value) EVERY (interval_value)} | {START (partition_value) END (partition_value
|MAXVALUE)} | {START(partition_value)} | {END (partition_value | MAXVALUE)}

指定各分区的信息，各参数意义如下：

A. partition_name：范围分区的名称或名称前缀，除以下情形外（假定其中的partition_name是p1），均为分区的名称。

    • 若该定义是START+END+EVERY从句，则语义上定义的分区的名称依次为p1_1, p1_2, ...。例如对于定义“PARTITION p1 START(1) END(4) EVERY(1)”，则生成的分区是：[1, 2), [2, 3) 和 [3, 4)，名称依次为p1_1, p1_2和p1_3，即此处的p1是名称前缀。

    • 若该定义是第一个分区定义，且该定义有START值，则范围（MINVALUE, START）将自动作为第一个实际分区，其名称为p1_0，然后该定义语义描述的分区名称依次为p1_1, p1_2, ...。例如对于完整定义“PARTITION p1 START(1), PARTITION p2 START(2)”，则生成的分区是：(MINVALUE, 1), [1, 2) 和 [2, MAXVALUE)，其名称依次为p1_0, p1_1和p2，即此处p1是名称前缀，p2是分区名称。这里MINVALUE表示最小值。

B. partition_value：范围分区的端点值（起始或终点），取值依赖于partition_key的类型，不可是MAXVALUE。

C. interval_value：对[START，END) 表示的范围进行切分，interval_value是指定切分后每个分区的宽度，不可是MAXVALUE；如果（END-START）值不能整除以EVERY值，则仅最后一个分区的宽度小于EVERY值。

D. MAXVALUE：表示最大值，它通常用于设置最后一个范围分区的上边界。

说明：

A. 在创建分区表若第一个分区定义含START值，则范围（MINVALUE，START）将自动作为实际的第一个分区。

B. START END语法需要遵循以下限制：

    • 每个partition_start_end_item中的START值（如果有的话，下同）必须小于其END值；

    • 相邻的两个partition_start_end_item，第一个的END值必须等于第二个的START值；

    • 每个partition_start_end_item中的EVERY值必须是正向递增的，且必须小于（END-START）值；

    • 每个分区包含起始值，不包含终点值，即形如：[起始值，终点值)，起始值是MINVALUE时则不包含；

    • 一个partition_start_end_item创建的每个分区所属的TABLESPACE一样；

    • partition_name作为分区名称前缀时，其长度不要超过57字节，超过时自动截断；

    • 在创建、修改分区表时请注意分区表的分区总数不可超过最大限制（1048575）；

C. 在创建分区表时START END与LESS THAN语法不可混合使用。

D. 即使创建分区表时使用START END语法，备份（gs_dump）出的SQL语句也是VALUES LESS THAN语法格式。

    • INTERVAL (interval_expr) [ STORE IN (tablespace_name [, ... ] ) ]

间隔分区定义信息。

A. interval_expr：自动创建分区的间隔，需要符合partition_key的字段类型，目前只支持数值类型和日期/时间类型，例如：1 day、1 month。

B. STORE IN (tablespace_name [, ... ] )：指定存放自动创建分区的表空间列表，如果有指定，则自动创建的分区从表空间列表中循环选择使用，否则使用分区表默认的表空间。

    • PARTITION BY LIST [COLUMNS] (partition_key)

创建列表分区。partition_key为分区键的名称。

COLUMNS关键字只能在sql_compatibility='B'时使用，“PARTITION BY LIST COLUMNS” 语义同 “PARTITION BY LIST”。

A. 对于partition_key，列表分区策略的分区键最多支持16列。

B. 对于从句是VALUES [IN] (list_values)的语法格式，list_values中包含了对应分区存在的键值，每个分区的键值数量不超过64个。

C. 从句"VALUES IN"只能在sql_compatibility='B'时使用，语义同"VALUES"。

分区键支持的数据类型为：TINYINT、SMALLINT、INTEGER、BIGINT、NUMERIC、VARCHAR(n)、CHAR、BPCHAR、NVARCHAR、NVARCHAR2、TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE。分区个数不能超过1048575个。

    • PARTITION BY HASH(partition_key)

创建哈希分区。partition_key为分区键的名称。

对于partition_key，哈希分区策略的分区键仅支持1列。

分区键支持的数据类型为：TINYINT、SMALLINT、INTEGER、BIGINT、NUMERIC、VARCHAR(n)、CHAR、BPCHAR、TEXT、NVARCHAR、NVARCHAR2、TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE。分区个数不能超过1048575个。

    • PARTITION BY KEY(partition_key)

只能在sql_compatibility='B'时使用，语义同“PARTITION BY HASH(partition_key)”。

    • AUTOMATIC

创建新表时，若指定关键字AUTOMATIC，则开启列表分区的自动扩展功能，缺省时表示不开启自动扩展功能。只有列表分区可以使用自动扩展功能。

开启自动扩展功能后，当插入数据无法匹配到已有分区时，会自动创建一个单独的分区。

    • PARTITIONS integer

指定分区个数。

integer为分区数，必须为大于0的整数，且不得大于1048575。

A. 当在RANGE和LIST分区后指定此子句时，必须显式定义每个分区，且定义分区的数量必须与integer值相等。只能在sql_compatibility='B'时在RANGE和LIST分区后指定此子句。

B. 当在HASH和KEY分区后指定此子句时，若不列出各个分区定义，将自动生成integer个分区，自动生成的分区名为“p+数字”，数字依次为0到integer-1，分区的表空间默认为此表的表空间；也可以显式列出每个分区定义，此时定义分区的数量必须与integer值相等。若既不列出分区定义，也不指定分区数量，将创建唯一一个分区。

    • { ENABLE | DISABLE } ROW MOVEMENT

行迁移开关。

如果进行UPDATE操作时，更新了元组在分区键上的值，造成了该元组所在分区发生变化，就会根据该开关给出报错信息，或者进行元组在分区间的转移。

取值范围：

A. ENABLE（缺省值）：行迁移开关打开。

B. DISABLE：行迁移开关关闭。

在打开行迁移开关情况下，并发UPDATE、DELETE操作可能会报错，原因如下：

UPDATE和DELETE操作对于旧数据都是标记为已删除。在打开行迁移开关情况下，如果更新分区键时，导致了跨分区更新，内核会把旧分区中旧数据标记为已删除，在新分区中新增加一条数据，无法通过旧数据找到新数据。

在UPDATE和UPDATE并发、DELETE和DELETE并发、UPDATE和DELETE并发三个并发场景下，如果并发操作同一行数据时，数据跨分区和非跨分区结果有不同的行为。

A. 对于数据非跨分区结果，第一个操作执行完后，第二个操作不会报错。

    • 如果第一个操作是UPDATE，第二个操作能成功找到最新的数据，之后对新数据操作。

    • 如果第一个操作是DELETE，第二个操作看到当前数据已经被删除而且找不到最新数据，就终止操作。

B. 对于数据跨分区结果，第一个操作执行完后，第二个操作会报错。

    • 如果第一个操作是UPDATE，由于新数据在新分区中，第二个操作不能成功找到最新的数据，就无法操作，之后会报错。

    • 如果第一个操作是DELETE，第二个操作看到当前数据已经被删除而且找不到最新数据，但无法判断删除旧数据的操作是UPDATE还是DELETE。如果是UPDATE，报错处理。如果是DELETE，终止操作。为了保持数据的正确性，只能报错处理。

如果是UPDATE和UPDATE并发，UPDATE和DELETE并发场景，需要串行执行才能解决问题，如果是DELETE和DELETE并发，关闭行迁移开关可以解决问题。

    • NOT NULL

字段值不允许为NULL。ENABLE用于语法兼容，可省略。

    • NULL

字段值允许NULL ，这是缺省。

这个子句只是为和非标准SQL数据库兼容。不建议使用。

    • CHECK (condition) [ NO INHERIT ]

CHECK约束声明一个布尔表达式，每次要插入的新行或者要更新的行的新值必须使表达式结果为真或未知才能成功，否则会抛出一个异常并且不会修改数据库。

声明为字段约束的检查约束应该只引用该字段的数值，而在表约束里出现的表达式可以引用多个字段。

用NO INHERIT标记的约束将不会传递到子表中去。

ENABLE用于语法兼容，可省略。

    • DEFAULT default_expr

DEFAULT子句给字段指定缺省值。该数值可以是任何不含变量的表达式(不允许使用子查询和对本表中的其他字段的交叉引用)。缺省表达式的数据类型必须和字段类型匹配。

缺省表达式将被用于任何未声明该字段数值的插入操作。如果没有指定缺省值则缺省值为NULL 。

    • ON UPDATE update_expr

ON UPDATE子句为字段的一种属性约束。

当对表中某元组执行UPDATE操作时，若更新字段的新值和表中旧值不相同，则表中该元组上具有该属性且不在更新字段内的字段值自动更新为当前时间戳；若更新字段的新值和表中旧值相同，则表中该元组上具有该属性且不在更新字段内的字段值不变，保持原有值；若具有该属性的字段在更新字段内，则对应这些字段值直接按指定更新的值更新。

说明：

A. 该属性仅支持在B模式库中的5.7版本下指定（即sql_compatibility = 'B'、b_format_version='5.7'、b_format_dev_version='s1'）。

B. 语法上update_expr支持CURRENT_TIMESTAMP 、LOCALTIMESTAMP 、NOW()三种关键字，也支持关键字带括号指定或不指定精度。例如：ON UPDATE CURRENT_TIMESTAMP( )、ON UPDATE CURRENT_TIMESTAMP(5)、ON UPDATE LOCALTIMESTAMP()、ON UPDATE LOCALTIMESTAMP(6)等。不带括号或空括号时精度为0，其中NOW关键字不支持不带括号。三种关键字互为同义词，属性效果相同。

C. 该属性仅支持在如下类型的列上指定: timestamp、datetime、date、time without time zone、smalldatetime、abstime。

D. CREATE TABLE AS语法不会继承该列属性。

E. CREATE TABLE LIKE语法可通过INCLUDING UPDATE或EXCLUDING UPDATE来选择继承或排除该约束。LIKE语法继承自PostgreSQL的LIKE语法，目前不支持复制旧表的ilm策略信息。

F. 该属性指定的精度和对应列上类型指定的精度可以不一致，通过该属性更新字段值后显示结果按最小精度显示。例如：CREATE TABLE t1 (col1 timestamp(6) ON UPDATE CURRENT_TIMESTAMP(3)); 若UPDATE语法触发该属性生效，则本次更新后col1字段值小数位显示3位。

G. 该属性和生成列约束不能同时指定同一列。

H. 分区表中的分区键不支持指定该属性。

    • GENERATED ALWAYS AS ( generation_expr ) [STORED]

该子句将字段创建为生成列，生成列的值在写入（插入或更新）数据时由generation_expr计算得到，STORED表示像普通列一样存储生成列的值。

说明：

A. STORED关键字可省略，与不省略STORED语义相同。

B. 生成表达式不能以任何方式引用当前行以外的其他数据。生成表达式不能引用其他生成列，不能引用系统列。生成表达式不能返回结果集，不能使用子查询，不能使用聚集函数，不能使用窗口函数。生成表达式调用的函数只能是不可变（IMMUTABLE）函数。

C. 不能为生成列指定默认值。

D. 生成列不能作为分区键的一部分。

E. 生成列不能和ON UPDATE约束子句的CASCADE、SET NULL、SET DEFAULT动作同时指定。生成列不能和ON DELETE约束子句的SET NULL、SET DEFAULT动作同时指定。

F. 修改和删除生成列的方法和普通列相同。删除生成列依赖的普通列，生成列被自动删除。不能改变生成列所依赖的列的类型。

G. 生成列不能被直接写入。在INSERT或UPDATE命令中, 不能为生成列指定值, 但是可以指定关键字DEFAULT。

H. 生成列的权限控制和普通列一样。

    • GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ]

该子句将列创建为IDENTITY列。会根据identity_options自动创建一个隐式序列附加到指定列，在插入数据时将序列中获取的值自动分配给该列。

A. GENERATED ALWAYS AS IDENTITY：该列仅接受插入由序列生成器提供的IDENTITY值，不能接受用户指定值。

B. GENERATED BY DEFAULT AS IDENTITY：该列优先插入用户提供值，若用户不指定值，将插入由序列生成器提供的IDENTITY值。

C. GENERATED BY DEFAULT ON NULL AS IDENTITY：该列优先插入用户提供值，若用户指定NULL值或用户不指定值，将插入由序列生成器提供的IDENTITY值。

可选的identity_options子句可用于覆盖序列选项。

A. increment：指定隐式序列步长。为正数时将生成一个递增的序列，为负数时将生成一个递减的序列，缺省值为1。

B. MINVALUE minvalue | NO MINVALUE | NOMINVALUE：执行序列的最小值，如果没有声明minvalue或者声明了NO MINVALUE，则递增序序列缺省值为1，递减序列的缺省值为-10^27+1。NOMINVALUE等价于NO MINVALUE。

C. MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE：执行序列的最大值，如果没有声明maxvalue或者声明了NO MAXVALUE，则递增序序列缺省值为10^28-1，递减序列的缺省值为-1。NOMAXVALUE等价于NO MAXVALUE。

D. start：指定隐式序列的起始值。缺省值：对于递增序列为minvalue，递减序列为maxvalue。

E. cache：为了快速访问，而在内存中预先存储序列号的个数。缺省值为1，表示一次只能生成一个值，即没有缓存。

F. NOCACHE：未预先存储序列的值。

G. CYCLE：用于使序列达到maxvalue或者minvalue后可循环并继续下去。如果声明了NO CYCLE，则在序列达到其最大或最小值之后任何对nextval的调用都会返回一个错误。NOCYCLE等价于NO CYCLE，缺省值为NO CYCLE。

H. SCALE：用于启用序列的可伸缩性。如果指定，则会在序列的开头附加一个数字偏移量，防止生成值中有重复项。如果声明了NOSCALE，则禁止序列的可伸缩性。缺省值为NOSCALE。

I. EXTEND：扩展数字偏移量长度（默认值为6），将序列生成值对齐到x（默认为6）+y（最大位数）位，指定EXTEND时必须指定SCALE。如果声明了NOEXTEND，则不扩展数字偏移量长度。缺省值为NOEXTEND。

说明：

A. IDENTITY列只能为smallint、integer、bigint、decimal、numeric、float、double precision或real数字类型。

B. 在A兼容模式下，当创建IDENTITY列为整数数字类型时，将默认创建为numeric数字类型。

C. 修改IDENTITY列的字段类型和普通列相同，但仅限于修改为smallint、integer、bigint、decimal、numeric、float、double precision和real数字类型。

D. IDENTITY列默认有NOT NULL约束。

E. 一张表里只允许有一个IDENTITY列。

F. 删除IDENTITY列的方法和删除普通列相同，删除列时，IDENTITY的隐式序列将会被自动删除。

G. IDENTITY列不能和SET DEFAULT动作同时指定。

H. 自动创建的隐式序列的类型为LARGE SEQUENCE。

I. 用户不能执行DROP LARGE SEQUENCE或ALTER LARGE SEQUENCE对IDENTITY的隐式序列进行修改。

J. 当对该表进行赋权后，插入能正常执行，若要更改IDENTITY列、删除IDENTITY属性或删除IDENTITY列，需要对相应的隐式序列额外赋权。

K. [ SCALE [ EXTEND | NOEXTED ] | NOSCALE ]子句仅用于A兼容模式的集中式下创建IDENTITY列时可用。

L. 在全密态数据库下，不支持创建表时指定加密IDENTITY列。

    • AUTO_INCREMENT

指定列为自动增长列。

详见：AUTO_INCREMENT 

    • UNIQUE [KEY] index_parameters

UNIQUE ( column_name [, ... ] ) index_parameters

UNIQUE约束表示表里的一个字段或多个字段的组合必须在全表范围内唯一。

对于唯一约束，NULL被认为是互不相等的。

UNIQUE KEY只能在sql_compatibility='B'时使用，与UNIQUE语义相同。

    • PRIMARY KEY index_parameters

PRIMARY KEY ( column_name [, ... ] ) index_parameters

主键约束声明表中的一个或者多个字段只能包含唯一的非NULL值。

一个表只能声明一个主键。

   • DEFERRABLE | NOT DEFERRABLE

这两个关键字设置该约束是否可推迟。一个不可推迟的约束将在每条命令之后马上检查。可推迟约束可以推迟到事务结尾使用SET CONSTRAINTS命令检查。缺省是NOT DEFERRABLE。目前，UNIQUE约束、主键约束、外键约束可以接受这个子句。所有其他约束类型都是不可推迟的。

    • INITIALLY IMMEDIATE | INITIALLY DEFERRED

如果约束是可推迟的，则这个子句声明检查约束的缺省时间。

A. 如果约束是INITIALLY IMMEDIATE（缺省），则在每条语句执行之后就立即检查它。

B. 如果约束是INITIALLY DEFERRED ，则只有在事务结尾才检查它。

约束检查的时间可以用SET CONSTRAINTS命令修改。

    • USING INDEX TABLESPACE tablespace_name

为UNIQUE或PRIMARY KEY约束相关的索引声明一个表空间。如果没有提供这个子句，这个索引将在default_tablespace中创建，如果default_tablespace为空，将使用数据库的缺省表空间。

5.1.3 操作示例

4.1.3.1 范围分区示例

--创建表空间。
CREATE TABLESPACE tbs_test_range1_p1 RELATIVE LOCATION 'tbs_test_range1/tablespace_1';
CREATE TABLESPACE tbs_test_range1_p2 RELATIVE LOCATION 'tbs_test_range1/tablespace_2';
CREATE TABLESPACE tbs_test_range1_p3 RELATIVE LOCATION 'tbs_test_range1/tablespace_3';
CREATE TABLESPACE tbs_test_range1_p4 RELATIVE LOCATION 'tbs_test_range1/tablespace_4';
--创建范围分区表test_range1。
CREATE TABLE test_range1(
id INT,
info VARCHAR(20)
) PARTITION BY RANGE (id) (
PARTITION p1 VALUES LESS THAN (200) TABLESPACE tbs_test_range1_p1,
PARTITION p2 VALUES LESS THAN (400) TABLESPACE tbs_test_range1_p2,
PARTITION p3 VALUES LESS THAN (600) TABLESPACE tbs_test_range1_p3,
PARTITION pmax VALUES LESS THAN (MAXVALUE) TABLESPACE tbs_test_range1_p4
);

--插入1000条数据
INSERT INTO test_range1 VALUES(GENERATE_SERIES(1,1000),'abcd');
--查看p1分区的行数199条，[1,200)。
SELECT COUNT(*) FROM test_range1 PARTITION (p1);
count
-------
199
(1 row)
--查看p2分区的行数200条，[200,400)。
SELECT COUNT(*) FROM test_range1 PARTITION (p2);
count
-------
200
(1 row)
--查看分区信息。
SELECT a.relname, a.boundaries, b.spcname
FROM pg_partition a, pg_tablespace b
WHERE a.reltablespace = b.oid AND a.parentid = 'test_range1'::regclass;
relname | boundaries | spcname
---------+------------+--------------------
p1 | {200} | tbs_test_range1_p1
p2 | {400} | tbs_test_range1_p2
p3 | {600} | tbs_test_range1_p3
pmax | {NULL} | tbs_test_range1_p4
(4 rows)
--删除
DROP TABLE test_range1;
DROP TABLESPACE tbs_test_range1_p1;
DROP TABLESPACE tbs_test_range1_p2;
DROP TABLESPACE tbs_test_range1_p3;
DROP TABLESPACE tbs_test_range1_p4;
•START END--创建分区表。
CREATE TABLE test_range2(
id INT,
info VARCHAR(20)
) PARTITION BY RANGE (id) (
PARTITION p1 START(1) END(600) EVERY(200),
PARTITION p2 START(600) END(800),
PARTITION pmax START(800) END(MAXVALUE)
);
--查看分区信息。
SELECT relname, boundaries FROM pg_partition WHERE parentid = 'test_range2'::regclass AND parttype = 'p' ORDER BY 1;
relname | boundaries
---------+------------
p1_0 | {1}
p1_1 | {201}
p1_2 | {401}
p1_3 | {600}
p2 | {800}
pmax | {NULL}
(6 rows)
--删除。
DROP TABLE test_range2;

4.1.3.2 列表分区示例

--创建列表分区表。
CREATE TABLE test_list ( NAME VARCHAR ( 50 ), area VARCHAR ( 50 ) )
PARTITION BY LIST (area) (
PARTITION p1 VALUES ('Beijing'),
PARTITION p2 VALUES ('Shanghai'),
PARTITION p3 VALUES ('Guangzhou'),
PARTITION p4 VALUES ('Shenzhen'),
PARTITION pdefault VALUES (DEFAULT)
);
--插入数据。
INSERT INTO test_list VALUES ('bob', 'Shanghai'),('scott', 'Sichuan');
--查询分区数据。
SELECT * FROM test_list PARTITION (p2);
name | area
------+----------
bob | Shanghai
(1 row)
SELECT * FROM test_list PARTITION (pdefault);
name | area
-------+---------
scott | Sichuan
(1 row)
--删除。
DROP TABLE test_list;


4.1.3.3 哈希分区示例

--创建哈希分区表，指定分区数。
CREATE TABLE test_hash1(c1 int) PARTITION BY HASH(c1) PARTITIONS 3;
--创建哈希分区表，并指定分区名。
CREATE TABLE test_hash2(c1 int) PARTITION BY HASH(C1)(
PARTITION pa,
PARTITION pb,
PARTITION pc
);
--查看分区信息。
SELECT b.relname AS table_name,
a.relname AS partition_name
FROM pg_partition a,
pg_class b
WHERE b.relname LIKE 'test_hash%'
AND a.parttype = 'p'
AND a.parentid = b.oid;
table_name | partition_name
------------+----------------
test_hash1 | p2
test_hash1 | p1
test_hash1 | p0
test_hash2 | pc
test_hash2 | pb
test_hash2 | pa
(6 rows)
--删除。
DROP TABLE test_hash1,test_hash2;

4.1.3.4 间隔分区示例

CREATE TABLE interval_sales
(
prod_id NUMBER(6),
cust_id NUMBER,
time_id DATE,
channel_id CHAR(1),
promo_id NUMBER(6),
quantity_sold NUMBER(3),
amount_sold NUMBER(10, 2)
)
PARTITION BY RANGE (time_id) INTERVAL ('1 month')
(
PARTITION date_2015 VALUES LESS THAN ('2016-01-01'),
PARTITION date_2016 VALUES LESS THAN ('2017-01-01'),
PARTITION date_2017 VALUES LESS THAN ('2018-01-01'),
PARTITION date_2018 VALUES LESS THAN ('2019-01-01'),
PARTITION date_2019 VALUES LESS THAN ('2020-01-01')
);

4.1.3.5 使用注意事项

A. 唯一约束和主键约束的约束键包含所有分区键将为约束创建LOCAL索引，否则创建GLOBAL索引。

B. 目前哈希分区仅支持单列构建分区键，暂不支持多列构建分区键。

C. 只需要有间隔分区表的INSERT权限，往该表INSERT数据时就可以自动创建分区。

D. 对于分区表PARTITION FOR (values)语法，values只能是常量。

E. 对于分区表PARTITION FOR (values)语法，values在需要数据类型转换时，建议使用强制类型转换，以防隐式类型转换结果与预期不符。

F. 分区数最大值为1048575个，一般情况下业务不可能创建这么多分区，这样会导致内存不足。应参照参数local_syscache_threshold的值合理创建分区，分区表使用内存大致为（分区数 * 3 / 1024）MB。理论上分区占用内存不允许大于local_syscache_threshold的值，同时还需要预留部分空间以供其他功能使用。

G. 考虑性能影响，一般建议单表最大分区数不超过2000，子分区数 *（LOCAL索引个数 + 1） 不超过10000。

H. 当分区数太多导致内存不足时，会间接导致性能急剧下降。

I. 指定分区语句目前不能走全局索引扫描。

J. 不支持XML类型数据作为分区键、二级分区键。

K. 在为数据对象增加或者变更ILM策略的时候，如果追加了行级表达式，需要注意行表达式目前只支持白名单中列出的函数。具体白名单函数列表参考行表达式函数白名单。

5.2 修改分区表

这里的语法格式和参数说明可以直接参考对应版本的产品说明文档，下面附件为参考示例。

5.2.1 语法格式

修改分区表分区包括修改表分区主语法、修改表分区名称的语法、重置分区ID和开启/关闭分区自动扩展功能的语法。

    • 修改表分区主语法

ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )}
action [, ... ];

A. 其中action统指如下分区维护子语法。当存在多个分区维护子句时，保证了分区的连续性，无论这些子句的排序如何，GaussDB总会先执行DROP PARTITION再执行ADD PARTITION操作，最后顺序执行其它分区维护操作。

move_clause |
exchange_clause |
row_clause |
merge_clause |
modify_clause |
split_clause |
add_clause |
drop_clause |
truncate_clause |
ilm_clause |
set_partitioning_clause

B. move_clause子语法用于移动分区到新的表空间。

MOVE PARTITION { partion_name | FOR ( partition_value [, ...] ) } TABLESPACE tablespacename

C. exchange_clause子语法用于把普通表的数据迁移到指定的分区。

EXCHANGE PARTITION { ( partition_name ) | partition_name | FOR ( partition_value [, ... ] ) }
WITH TABLE {[ ONLY ] ordinary_table_name | ordinary_table_name * | ONLY ( ordinary_table_name )}
[ { WITH | WITHOUT } VALIDATION ] [ VERBOSE ] [ UPDATE GLOBAL INDEX ] [ UPDATE DISTRIBUTED GLOBAL INDEX | NO UPDATE DISTRIBUTED GLOBAL INDEX ]

进行交换的普通表和分区必须满足如下条件：

A. 普通表和分区的列数相同，对应列的信息严格一致，包括：列名、列的数据类型、列约束、列的Collation信息、列的存储参数、列的压缩信息等。

B. 普通表和分区的表压缩信息严格一致。

C. 普通表索引和分区Local索引个数相同，且对应索引的信息严格一致。

D. 普通表和分区的表约束个数相同，且对应表约束的信息严格一致。

E. 普通表不可以是临时表，分区表只能是范围分区表，列表分区表，哈希分区表或间隔分区表。

F. 在内置安全策略开关开启的情况下，普通表和分区表上不可以有动态数据脱敏，行访问控制约束。

说明：

A. 完成交换后，普通表和分区的数据被置换，同时普通表和分区的表空间信息被置换。此时，普通表和分区的统计信息变得不可靠，需要对普通表和分区重新执行analyze。

B. 由于非分区键不能建立本地唯一索引，只能建立全局唯一索引，所以如果普通表含有唯一索引时，可能会导致无法交换数据。

C. 如果需要进行数据交换操作，可以通过创建中间表的方式。先将分区数据插入到中间表，truncate分区，普通表数据插入分区表，drop普通表，重命名中间表的方式完成数据交换操作。

D. 对于普通表和分区表都是Ustore的场景，如果普通表的Ubtree索引类型（RCR或者PCR，默认为RCR）和分区表本地的Ubtree索引类型（RCR或PCR，默认为RCR）不一致，会导致无法完成数据交换的操作。

E. 如果在普通表/分区表上进行了DROP COLUMN操作，被删除的列依然物理存在，则需要保证普通表和分区的被删除列严格对齐才能交换成功。

F. EXCHANGE PARTITION partition_name操作仅在B模式数据库（即sql_compatibility = 'B'）下有效。

G. 在B模式数据库（即sql_compatibility = 'B'）下，当partition_name为一级分区名时，进行交换的是一级分区和普通表；当partition_name为二级分区时， 进行交换的是二级分区和普通表。

H. 不支持在二级分区表中交换一级分区和普通表。

    • row_clause子语法用于设置分区表的行迁移开关。

{ ENABLE | DISABLE } ROW MOVEMENT

    • merge_clause子语法用于把多个分区合并成一个分区。一个命令中合并的源分区上限为300。

MERGE PARTITIONS { partition_name } [, ...] INTO PARTITION partition_name
[ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] 
[ TABLESPACE tablespacename ] [ UPDATE GLOBAL INDEX ] [ UPDATE DISTRIBUTED GLOBAL INDEX | NO UPDATE DISTRIBUTED GLOBAL INDEX ]

说明：

A. 对于范围分区/间隔分区，MERGE分区要求源分区的范围连续递增，且MERGE后的分区名可以与最后一个源分区名相同；对于列表分区，则源分区无顺序要求，且MERGE后的分区名可以与任一源分区名相同。如果MERGE后的分区名与源分区名相同，视为同一个分区。

B. 未打开guc参数enable_ilm的情况下，如果使用merge_clause子语法把多个带有ilm policy的分区合并成一个分区，新分区不继承ilm policy。

说明：

USTORE存储引擎表不支持在事务块/存储过程中执行ALTER TABLE MERGE PARTITIONS的操作。

    • modify_clause子语法用于设置分区索引是否可用。

MODIFY PARTITION partition_name { UNUSABLE LOCAL INDEXES | REBUILD UNUSABLE LOCAL INDEXES }

    • split_clause子语法用于把一个分区切割成多个分区。

SPLIT PARTITION { partition_name | FOR ( partition_value [, ...] ) } { split_point_clause | no_split_point_clause } [ UPDATE GLOBAL INDEX ] 
[ UPDATE DISTRIBUTED GLOBAL INDEX | NO UPDATE DISTRIBUTED GLOBAL INDEX ]

说明：

A. SPLIT后的分区名可以与源分区名相同，将视为不同的分区。

B. 未打开guc参数enable_ilm的情况下，如果使用split_clause子语法把一个带有ilm policy的分区分割成多个分区，新分区不继承ilm policy。

C. 范围分区表和间隔分区表指定切割点split_point_clause的语法为：

AT ( partition_value ) INTO ( PARTITION partition_name [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } 
AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ] , PARTITION 
partition_name [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF 
{ NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ] )

说明：

切割点的大小要位于正在被切割的分区的分区键范围内，指定切割点的方式只能把一个分区切割成两个新分区。

    • 范围分区表和间隔分区表不指定切割点no_split_point_clause的语法为：

INTO { ( partition_less_than_item [, ...] ) | ( partition_start_end_item [, ...] ) }

说明：

A. 不指定切割点的方式，partition_less_than_item指定的第一个新分区的分区键要大于正在被切割的分区的前一个分区（如果存在）的分区键，partition_less_than_item指定的最后一个分区的分区键要等于正在被切割的分区的分区键。

B. 不指定切割点的方式，partition_start_end_item指定的第一个新分区的起始点（如果存在）必须等于正在被切割的分区的前一个分区（如果存在）的分区键，partition_start_end_item指定的最后一个分区的终止点（如果存在）必须等于正在被切割的分区的分区键。

C. partition_less_than_item支持的分区键个数最多为16，而partition_start_end_item仅支持1个分区键，其支持的数据类型请参见•PARTITION BY RANGE [COLUMNS] (partition_key) 。

D. 在同一语句中partition_less_than_item和partition_start_end_item两者不可同时使用；不同split语句之间没有限制。

E. 分区项partition_less_than_item的语法如下，其中最后一个分区可以不写分区范围定义，即VALUES LESS THAN (partition_value)部分，默认继承源分区范围定义的上界值。

PARTITION partition_name VALUES LESS THAN {( { partition_value | MAXVALUE } [, ...] ) | MAXVALUE }
[ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ]

说明：

RANGE分区时支持MAXVALUE关键字不带括号，只能支持B模式使用，不支持在二级分区的子分区中使用，不支持在分区字段为多列的场景使用。

A. 分区项partition_start_end_item的语法如下，其约束请参见START END语法描述 。

PARTITION partition_name {
{START(partition_value) END (partition_value) EVERY (interval_value)} |
{START(partition_value) END ({partition_value | MAXVALUE})} |
{START(partition_value)} |
{END({partition_value | MAXVALUE})}
} [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [TABLESPACE tablespace_name]

B. 列表分区表指定切割点split_point_clause的语法如下。

VALUES ( partition_value_list ) INTO ( PARTITION partition_name [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } 
{ ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )]] [ TABLESPACE tablespacename ] , PARTITION 
partition_name [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { 
NO MODIFICATION } [ ON ( EXPR )]] [ TABLESPACE tablespacename ] )

说明：

切割点必须是源分区的一个非空真子集，指定切割点的方式只能把一个分区切割成两个新分区。

    • 列表分区表不指定切割点no_split_point_clause的语法如下，其中最后一个分区不能写分区范围定义，即VALUES (partition_value_list)部分，其范围等于源分区去掉其他子分区后的剩余集合。

INTO ( PARTITION partition_name VALUES (partition_value_list) [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW }
 AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )]] [ TABLESPACE tablespacename ][, ...] )

说明：

A. 最后一个新分区不能写分区范围定义，其范围等于源分区去掉其他子分区后的剩余集合。

B. 不指定切割点的方式，每一个新分区都必须是源分区的一个非空真子集，且互不交叉。

    • add_clause子语法用于为指定的分区表添加一个或多个分区。

ADD {{partition_less_than_item | partition_start_end_item| partition_list_item } |
PARTITION({partition_less_than_item | partition_start_end_item | partition_list_item})}

    • 分区项partition_list_item的语法为：

PARTITION partition_name VALUES [IN] (list_values_clause)
[ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ]

说明：

A. partition_list_item支持最多16个分区键，其支持的数据类型请参见•PARTITION BY LIST [COLUMNS] (partition_key) 。

B. 间隔/哈希分区表不支持添加分区。

C. IN需要在B模式下使用，不支持在二级分区的子分区中使用。

D. drop_clause子语法用于删除分区表中的指定分区。

DROP PARTITION { partition_name | FOR ( partition_value [, ...] ) } [ UPDATE GLOBAL INDEX ] [ UPDATE DISTRIBUTED GLOBAL INDEX 
| NO UPDATE DISTRIBUTED GLOBAL INDEX ]

说明：

A. 哈希分区表不支持删除分区。

B. 当分区表只有一个分区时，不能删除该分区。

C. truncate_clause子语法用于清空分区表中的指定分区。

TRUNCATE PARTITION { partition_name | FOR ( partition_value [, ...] ) } [ UPDATE GLOBAL INDEX ] [ UPDATE DISTRIBUTED GLOBAL INDEX 
| NO UPDATE DISTRIBUTED GLOBAL INDEX ]

D. ilm_clause子语法用于为分区添加ILM策略，为数据生命周期管理-OLTP表压缩特性支持语法。

MODIFY PARTITION partition_name ILM ADD POLICY ROW STORE { COMPRESS ADVANCED }
 { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR ) ]

E. set_partitioning_clause子语法用于开启或关闭列表/范围分区自动扩展功能。

SET PARTITIONING { AUTOMATIC | MANUAL } |
SET INTERVAL ( [ interval_expr ] )

F. 修改表分区名称的语法。

ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )}
RENAME PARTITION { partion_name | FOR ( partition_value [, ...] ) } TO partition_new_name;

G. 重置分区ID的语法。

ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )} RESET PARTITION;

5.2.2 参数说明

    • table_name

分区表名。

取值范围：已存在的分区表名。

    • partition_name

分区名。

取值范围：已存在的分区名。

    • tablespacename

指定分区要移动到哪一个表空间。

取值范围：已存在的表空间名。

    • partition_value

分区键值。

通过PARTITION FOR ( partition_value [, ...] )子句指定的这一组值，可以唯一确定一个分区。

取值范围：需要进行操作的分区的分区键的取值范围。

    • UNUSABLE LOCAL INDEXES

设置该分区上的所有索引不可用。

    • REBUILD UNUSABLE LOCAL INDEXES

重建该分区上的所有索引。

    • { ENABLE | DISABLE } ROW MOVEMET

行迁移开关。

如果进行UPDATE操作时，更新了元组在分区键上的值，造成了该元组所在分区发生变化，就会根据该开关给出报错信息，或者进行元组在分区间的转移。

取值范围：

    • ENABLE：打开行迁移开关。

    • DISABLE：关闭行迁移开关。

默认是打开状态。

    • ordinary_table_name

进行迁移的普通表的名称。

取值范围：已存在的普通表名。

    • { WITH | WITHOUT } VALIDATION

在进行数据迁移时，是否检查普通表中的数据满足指定分区的分区键范围。

取值范围：

    • WITH：对于普通表中的数据要检查是否满足分区的分区键范围，如果有数据不满足，则报错。

    • WITHOUT：对于普通表中的数据不检查是否满足分区的分区键范围。

默认是WITH状态。

由于检查比较耗时，特别是当数据量很大的情况下。所以在保证当前普通表中的数据满足分区的分区键范围时，可以加上WITHOUT来指明不进行检查。

    • VERBOSE

在VALIDATION是WITH状态时，如果检查出普通表有不满足要交换分区的分区键范围的数据，那么把这些数据插入到正确的分区，如果路由不到任何分区，再报错。

说明：

只有在VALIDATION是WITH状态时，才可以指定VERBOSE。

    • partition_new_name

分区的新名称。

取值范围：字符串，要符合标识符命名规范 。

    • UPDATE GLOBAL INDEX

如果使用该参数，则会更新分区表上的所有全局索引，以确保使用全局索引可以查询出正确的数据。如果不使用该参数，则分区表上的所有全局索引将会失效。

    • UPDATE DISTRIBUTED GLOBAL INDEX | NO UPDATE DISTRIBUTED GLOBAL INDEX

可选重建或者失效基表的全局二级索引。

删除、切割、合并、清空、交换分区的操作中的此选项集中式不支持。

    • SET PARTITIONING { AUTOMATIC | MANUAL }

开启或关闭列表分区的自动扩展功能。使用AUTOMATIC关键字时为开启自动扩展功能，使用MANUAL关键字时为关闭自动扩展功能。

    • SET INTERVAL ( [ interval_expr ] )

用于实现间隔分区与范围分区之间的互转，间隔分区相当于开启了自动扩展功能后的范围分区。interval_expr缺省时，将间隔分区转为范围分区；否则将范围分区转为间隔分区。其中，interval_expr为自动创建分区的间隔，需要符合partition_key的字段类型，目前只支持数值类型和日期/时间类型，例如：1、'1 day'、'1 month'。

5.2.3 操作示例

4.2.3.1 修改表分区名称

--创建前置分区表。
gaussdb=# CREATE TABLE test_p1 (col1 INT, col2 INT) PARTITION BY RANGE (col1)
(
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
--修改分区名称。
gaussdb=# ALTER TABLE test_p1 RENAME PARTITION p3 TO pmax;
--查询分区信息。
gaussdb=# SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_p1'::regclass AND parttype <> 'r';
relname | boundaries | oid
---------+------------+-------
p1 | {10} | 17066
p2 | {20} | 17067
pmax | {NULL} | 17068
(3 rows)

4.2.3.2 移动分区表空间

--创建分区
gaussdb=# CREATE TABLESPACE tbs_data1 RELATIVE LOCATION 'tablespace1/tbs_data1';
--移动分区表空间
gaussdb=# ALTER TABLE test_p1 MOVE PARTITION P1 TABLESPACE tbs_data1;
--查看分区表空间
gaussdb=# SELECT relname, spcname FROM pg_partition t1, pg_tablespace t2 WHERE T1.reltablespace=t2.oid and t1.parentid='test_p1'::regclass;
relname | spcname
---------+-----------
p1 | tbs_data1
(1 row)

4.2.3.3 分区交换

--创建普通表，插入数据
gaussdb=# CREATE TABLE test_ep1(col1 INT,col2 INT);
gaussdb=# INSERT INTO test_ep1 VALUES (GENERATE_SERIES(1,30), 1000);
--迁移普通表数据到指定分区
gaussdb=# ALTER TABLE test_p1 EXCHANGE PARTITION (p1) WITH TABLE test_ep1 VERBOSE;
--查询
gaussdb=# SELECT COUNT(*) FROM test_p1 PARTITION (p1);
count
-------
9
(1 row)
--删除表test_ep1
gaussdb=# DROP TABLE test_ep1;

4.2.3.4 分区合并

--将test_p1表中p2,pmax分区合并到pmax中
gaussdb=# ALTER TABLE test_p1 MERGE PARTITIONS p2,pmax INTO PARTITION pmax;
--查看分区
gaussdb=# SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_p1'::regclass AND parttype <> 'r' order by 1;
relname | boundaries | oid
---------+------------+-------
p1 | {10} | 17066
pmax | {NULL} | 17070
(2 rows)
--删除表和表空间
gaussdb=# DROP TABLE test_p1;
gaussdb=# DROP TABLESPACE tbs_data1;

4.2.3.5 切割分区

--建表
gaussdb=# CREATE TABLE test_r1 (col1 INT,col2 INT) PARTITION BY RANGE (col1)(
PARTITION p1 VALUES LESS THAN (10),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
--切割分区
gaussdb=# ALTER TABLE test_r1 SPLIT PARTITION pmax AT (20) INTO (PARTITION p2, PARTITION pmax);
gaussdb=# ALTER TABLE test_r1 SPLIT PARTITION pmax INTO (
PARTITION p3 VALUES LESS THAN (30),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
--查询
gaussdb=# SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_r1'::regclass AND parttype <> 'r' order by 1;
relname | boundaries | oid
---------+------------+-------
p1 | {10} | 17088
p2 | {20} | 17090
p3 | {30} | 17092
pmax | {NULL} | 17093
(4 rows)
--删除表test_r1。
gaussdb=# DROP TABLE test_r1;
--建表
gaussdb=# CREATE TABLE test_r2(col1 INT, col2 INT) PARTITION BY RANGE (col1)(
PARTITION p1 START(1) END(10),
PARTITION p2 START(10) END(20),
PARTITION pmax START(20) END(MAXVALUE)
);
--切割分区
gaussdb=# ALTER TABLE test_r2 SPLIT PARTITION pmax INTO (
PARTITION p3 START(20) END(30),
PARTITION pmax START(30) END (MAXVALUE)
);
--查看
gaussdb=# SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_r2'::regclass AND parttype <> 'r' order by 1;
relname | boundaries | oid
---------+------------+-------
p1_0 | {1} | 17112
p1_1 | {10} | 17113
p2 | {20} | 17114
p3 | {30} | 17116
pmax | {NULL} | 17117
(5 rows)
--删除表test_r2
gaussdb=# DROP TABLE test_r2;
--建表
gaussdb=# CREATE TABLE test_l1(col1 INT, col2 INT) PARTITION BY LIST(col1)(
PARTITION p1 VALUES (10,20),
PARTITION p2 VALUES (30,40)
);
--切割分区
gaussdb=# ALTER TABLE test_l1 SPLIT PARTITION p1 VALUES (10) INTO (PARTITION p1_1, PARTITION p1_2);
gaussdb=# ALTER TABLE test_l1 SPLIT PARTITION p2 INTO (PARTITION p3_1 VALUES(30), PARTITION p3_2);
--查看
gaussdb=# SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_l1'::regclass AND parttype <> 'r' order by 1;
relname | boundaries | oid
---------+------------+-------
p1_1 | {10} | 17132
p1_2 | {20} | 17133
p3_1 | {30} | 17134
p3_2 | {40} | 17135
(4 rows)
--删除表test_l1
gaussdb=# DROP TABLE test_l1;

4.2.3.6 添加分区

--建表
gaussdb=# CREATE TABLE test_p2 (col1 INT, col2 INT) PARTITION BY RANGE (col1)(
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20)
);
--添加分区
gaussdb=# ALTER TABLE test_p2 ADD PARTITION p3 VALUES LESS THAN (30);
--删除表test_p2。
gaussdb=# DROP TABLE test_p2;
--建表
gaussdb=# CREATE TABLE test_p3 (col1 INT, col2 INT) PARTITION BY LIST(col1)(
PARTITION p1 VALUES (1),
PARTITION p2 VALUES (2)
);
--添加分区
gaussdb=# ALTER TABLE test_p3 ADD PARTITION p3 VALUES (3);
--删除表test_p3
gaussdb=# DROP TABLE test_p3;

4.2.3.7 删除分区

--建表
gaussdb=# CREATE TABLE test_p4 (col1 INT, col2 INT) PARTITION BY LIST(col1)(PARTITION p1 VALUES (1),PARTITION p2 VALUES (2));
--删除test_p3表的p2分区
gaussdb=# ALTER TABLE test_p4 DROP PARTITION p2;
--查看
gaussdb=# SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_p4'::regclass;
relname | boundaries | oid
---------+------------+-------
test_p4 | | 17187
p1 | {1} | 17188
(2 rows)
--删除表test_p4。
gaussdb=# DROP TABLE test_p4;
--指定partition value删除分区
--建表
gaussdb=# CREATE TABLE test_p4 (col1 INT, col2 INT) PARTITION BY RANGE(col1)(PARTITION p1 VALUES LESS THAN(1),PARTITION p2 VALUES LESS THAN (2));
--删除test_p3表的分区键为1时所处的分区
gaussdb=# ALTER TABLE test_p4 DROP PARTITION FOR (1);
--查看
gaussdb=# SELECT relname, boundaries FROM pg_partition WHERE parentid='test_p4'::regclass order by 1 desc;
relname | boundaries
---------+------------
test_p4 |
p1 | {1}
(2 rows)
--删除表test_p4。
gaussdb=# DROP TABLE test_p4;

4.2.3.8 清空分区

--建表
gaussdb=# CREATE TABLE test_p5 (col1 INT, col2 INT) PARTITION BY RANGE (col1)(
PARTITION p1 VALUES LESS THAN (5),
PARTITION p2 VALUES LESS THAN (10)
);
--插入数据。
gaussdb=# INSERT INTO test_p5 VALUES (GENERATE_SERIES(1,9), 100);
--查看p2分区数据
gaussdb=# SELECT * FROM test_p5 PARTITION (p2);
col1 | col2
------+------
5 | 100
6 | 100
7 | 100
8 | 100
9 | 100
(5 rows)
--清空p2分区的数据。
gaussdb=# ALTER TABLE test_p5 TRUNCATE PARTITION p2;
--查看p2分区数据。
gaussdb=# SELECT * FROM test_p5 PARTITION (p2);
col1 | col2
------+------
(0 rows)
--删除表test_p5
gaussdb=# DROP TABLE test_p5;

4.2.3.9 开启和关闭列表分区的自动扩展功能

1.开启和关闭列表分区的自动扩展功能
--创建一个列表分区
gaussdb=# CREATE TABLE list_int (c1 int, c2 int)
PARTITION BY LIST (c1)
(
PARTITION p1 VALUES (1, 2, 3),
PARTITION p2 VALUES (4, 5, 6)
);
--开启列表分区的自动扩展功能
gaussdb=# ALTER TABLE list_int SET PARTITIONING AUTOMATIC;
--关闭列表分区的自动扩展功能。
gaussdb=# ALTER TABLE list_int SET PARTITIONING MANUAL;
--删除列表分区。
gaussdb=# DROP TABLE list_int;
2.开启和关闭范围分区的自动扩展功能。
--创建一个范围分区。
gaussdb=# CREATE TABLE range_int (c1 int, c2 int)
PARTITION BY RANGE (c1)
(
PARTITION p1 VALUES LESS THAN (5),
PARTITION p2 VALUES LESS THAN (10),
PARTITION p3 VALUES LESS THAN (15)
);
--开启范围分区的自动扩展功能
gaussdb=# ALTER TABLE range_int SET INTERVAL (5);
--关闭范围分区的自动扩展功能
gaussdb=# ALTER TABLE range_int SET INTERVAL ();
--删除范围分区
gaussdb=# DROP TABLE range_int;

4.2.3.10 使用注意事项

    • 只有分区表的所有者或者被授予了分区表ALTER权限的用户有权限执行ALTER TABLE PARTITION命令，当三权分立开关关闭时，系统管理员默认拥有此权限。

    • 添加分区的表空间不能是PG_GLOBAL。

    • 添加分区的名称不能与该分区表已有分区的名称相同。

    • 添加分区的分区键值要和分区表的分区键类型一致。

    • 若添加RANGE分区，添加分区键值要大于分区表中最后一个范围分区的上边界。

    • 若添加LIST分区，添加分区键值不能与现有分区键值重复。

    • 不支持添加哈希分区。

    • 如果目标分区表中已有分区数达到了最大值1048575，则不能继续添加分区。

    • 当分区表只有一个分区时，不能删除该分区。

    • 选择分区使用PARTITION FOR()，括号里指定值个数应该与定义分区时使用的列个数相同，并且一一对应。ALTER TABLE操作会处理partition_value落入的分区。

    • Value分区表不支持Alter Partition操作。

    • 间隔分区表不支持添加分区。

    • 哈希分区表不支持切割分区，不支持合并分区，不支持添加/删除分区。

    • 删除、切割、合并、清空、交换分区的操作会使Global索引失效，可以申明UPDATE GLOBAL INDEX子句同步更新索引。

    • 如果删除、切割、合并、清空、交换分区操作不申明UPDATE GLOBAL INDEX子句，并发的DML业务有可能因为索引不可用而报错。

    • 若设置参数enable_gpi_auto_update为on，即使不申明UPDATE GLOBAL INDEX子句，也会自动更新Global索引。

    • 在为数据对象增加或者变更ILM策略的时候，如果追加了行级表达式，需要注意行表达式目前只支持白名单中列出的函数。具体白名单函数列表参考行表达式函数白 名单 。

    • 开启范围分区自动扩展要求分区表中不能存在分区键值为MAXVALUE的分区。

    • 开启范围分区自动扩展只支持单分区键。

    • 开启列表分区自动扩展要求分区表中不能存在分区键值为DEFAULT的分区。

5.3 删除分区表

分区表删除方法与普通表一样，语法如下。

DROP TABLE [ IF EXISTS ]
{ [schema.]table_name } [, ...] [ CASCADE | RESTRICT ] [ PURGE ];

5.3.1 注意事项

    • DROP TABLE删除表后，依赖该表的索引会被删除，而使用到该表的函数和存储过程将无法执行。删除分区表，会同时删除分区表中的所有分区。

    • 表的所有者、表所在模式的所有者、被授予了表的DROP权限的用户或被授予DROP ANY TABLE权限的用户，有权删除指定表，三权分立关闭时，系统管理员默认拥有该权限。

    • DROP TABLE时，如果被指删除的表作为外键表引用了另一张表，会级联删除被引用表上的触发器，此时需要对被引用表加八级锁，可能造成业务的阻塞。

6 总结

本文描述分区表分区策略以及优点。创建、修改与删除分区表的常规操作并列出相关的操作示例，方便对分区表的熟练掌握。

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
GaussDB数据库 云数据库 GaussDB 开发者空间
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入