CREATE TABLE PARTITION | SUBPARTITION AS
功能描述
根据查询结果创建分区表。
CREATE TABLE PARTITION | SUBPARTITION AS用于创建一个分区表并且用来自SELECT命令的结果填充该分区表，数据将会按照指定的分区策略进行分区。该表的字段和SELECT输出字段的名称及数据类型相关。不过用户可以通过明确地给出一个字段名称列表来覆盖SELECT输出字段的名称。
语法格式
- 使用CREATE TABLE PARTITION AS创建一级分区表，并填充子查询数据：
CREATE TABLE [ IF NOT EXISTS ] partition_table_name [ (column_name [, ...] ) ] [ { ENGINE [ = ] { InnoDB | 'InnoDB' | "InnoDB" } } [ [ , ] ... ] ] [ 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 partition_clause partition_definition_list AS query [ WITH [ NO ] DATA ];
- 使用CREATE TABLE SUBPARTITION AS创建二级分区表，并填充子查询数据：
CREATE TABLE [ IF NOT EXISTS ] partition_table_name [ (column_name [, ...] ) ] [ { ENGINE [ = ] { InnoDB | 'InnoDB' | "InnoDB" } } [ [ , ] ... ] ] [ 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 partition_clause SUBPARTITION BY subpartition_clause partition_subpartition_definition_list AS query [ WITH [ NO ] DATA ];
各个字段详细信息请参见语法格式。
参数说明
- IF NOT EXISTS
如果指定IF NOT EXISTS关键字，创建表前会在当前SCHEMA中查找是否已有名字相同的relation。若已有同名relation存在，则不会新建，返回NOTICE提示。未指定IF NOT EXISTS关键字时，若SCHEMA中存在同名relation，返回ERROR告警。
- partition_table_name
要创建的分区表名。
取值范围：字符串，要符合标识符命名规范。
- column_name
可选。新表中要创建的字段名。如果没有指定字段名，那么新表的字段名和SELECT语句输入的字段名一致。
取值范围：字符串，要符合标识符命名规范。
- ENGINE
B模式下支持，仅语法适配，且只支持设置InnoDB，无实际效果。
- WITH ( storage_parameter [= value] [, ... ] )
这个子句为表或索引指定一个可选的存储参数。参数的详细说明如下所示。
- FILLFACTOR
一个表的填充因子（fillfactor）是一个介于10和100之间的百分数。在Ustore存储引擎下，该值得默认值为92，在Astore存储引擎下默认值为100（完全填充）。如果指定了较小的填充因子，INSERT操作仅按照填充因子指定的百分率填充表页。每个页上的剩余空间将用于在该页上更新行，这就使得UPDATE有机会在同一页上放置同一条记录的新版本，这比把新版本放置在其他页上更有效。对于一个从不更新的表将填充因子设为100是最佳选择，但是对于频繁更新的表，选择较小的填充因子则更加合适。该参数只对行存表有效。
取值范围：10~100
- ORIENTATION
ROW（缺省值）：表的数据将以行式存储。
- COMPRESSION
指定表数据的压缩级别，它决定了表数据的压缩比以及压缩时间。一般来讲，压缩级别越高，压缩比也越大，压缩时间也越长；反之亦然。实际压缩比取决于加载的表数据的分布特征。
取值范围：
该参数仅支持列存压缩。
- autovacuum_enabled
取值范围：on/off
默认值：on
- autovacuum_vacuum_threshold
自动清理功能中，指定在该表中触发VACUUM所需的更新或删除的最小元组数（仅对Astore表生效）。
取值范围：0-2147483647
默认值：-1，缺省时与GUC参数autovacuum_vacuum_threshold一致。
- autovacuum_analyze_threshold
自动清理功能中，指定在该表中触发ANALYZE所需的插入、更新或删除的最小元组数。
取值范围：0-2147483647
默认值：-1，缺省时与GUC参数autovacuum_analyze_threshold一致。
- autovacuum_vacuum_scale_factor
自动清理功能中，指定在该表中触发VACUUM所需的插入、更新或删除元组的比例（仅对Astore表生效）。
取值范围：0.0-100.0
默认值：-1，缺省时与GUC参数autovacuum_vacuum_scale_factor一致。
- autovacuum_analyze_scale_factor
自动清理功能中，指定在该表中触发ANALYZE所需的插入、更新或删除元组的比例。
取值范围：0.0-100.0
默认值：-1，缺省时与GUC参数autovacuum_analyze_scale_factor一致。
- autovacuum_freeze_min_age
自动清理功能中，指定在该表参数指定了一个行版本的最小年龄，超过这个年龄的行才会被冻结。
取值范围：0-1000000000
默认值：-1，缺省时与GUC参数vacuum_freeze_min_age一致。
- autovacuum_freeze_max_age
自动清理功能中，该表pg_class.relfrozenxid字段在超过多少个事务后，就会强制执行VACUUM操作。即使自动清理被禁用，系统也会启动AUTOVACUUM进程。清理操作还允许从pg_clog/子目录中删除旧文件（仅对Astore表生效）。
取值范围：100000-2000000000
默认值：-1，缺省时与GUC参数autovacuum_freeze_max_age一致。
- autovacuum_freeze_table_age
自动清理功能中，该表被标记为不需要自动清理时，它将保持不变的时间。（仅对Astore表生效）。
取值范围：0-2000000000
默认值：-1，缺省时与GUC参数vacuum_freeze_table_age一致。
- FILLFACTOR
- [ 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给行存添加高级压缩策略。
- AFTER n { day | month | year } OF NO MODIFICATION ：表示n天/月/年没有修改的行。
- ON ( EXPR )：行级表达式，用于判断行的冷热。
- TABLESPACE tablespace_name
指定新表将要在tablespace_name表空间内创建。如果没有声明，将使用默认表空间。
- PARTITION BY
partition_clause与partition_definition_list的各字段详细信息请参见参数说明。
- SUBPARTITION BY
subpartition_clause与partition_subpartition_definition_list的各字段详细信息请参见参数说明。
- AS query
一个SELECT VALUES命令或者一个运行预备好的SELECT或VALUES查询的EXECUTE命令。
- [ WITH [ NO ] DATA ]
创建分区表时，是否也插入查询到的数据。默认是要数据，选择“NO”参数时，则不要数据。
示例
- 创建一级分区表，并使用子查询数据填充分区表。
-- 创建一个源表t1，并插入3条数据。 gaussdb=# CREATE TABLE t1(a int, b int); gaussdb=# INSERT INTO t1 VALUES(8, 2); gaussdb=# INSERT INTO t1 VALUES(18, 3); gaussdb=# INSERT INTO t1 VALUES(28, 4); -- 使用CREATE TABLE PARTITION AS创建一级分区表，并使用子查询数据填充分区表。 gaussdb=# CREATE TABLE t1_part_dup PARTITION BY RANGE(a) ( PARTITION p1 VALUES LESS THAN(10), PARTITION p2 VALUES LESS THAN(20), PARTITION p3 VALUES LESS THAN(MAXVALUE) ) AS SELECT * FROM t1; gaussdb=# \d+ t1_part_dup; Table "create_table_as_partition_by.t1_part_dup" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+---------+--------------+------------- a | integer | | plain | | b | integer | | plain | | Partition By RANGE(a) Number of partitions: 3 (View pg_partition to check each partition range.) Has OIDs: no Options: orientation=row, compression=no gaussdb=# SELECT a, b FROM t1_part_dup PARTITION(p1) ORDER BY a; a | b ---+--- 8 | 2 (1 row) gaussdb=# SELECT a, b FROM t1_part_dup PARTITION(p2) ORDER BY a; a | b ----+--- 18 | 3 (1 row) gaussdb=# SELECT a, b FROM t1_part_dup PARTITION(p3) ORDER BY a; a | b ----+--- 28 | 4 (1 row) gaussdb=# DROP TABLE t1,t1_part_dup;
- 创建二级分区表，并使用子查询数据填充分区表。
-- 创建一个源表t1，并插入6条数据 gaussdb=# CREATE TABLE t1(a int, b int, c int, d varchar, e varchar) PARTITION BY RANGE(a) SUBPARTITION BY RANGE(b) ( PARTITION p1 VALUES LESS THAN(200) (SUBPARTITION s1 VALUES LESS THAN (500), SUBPARTITION s2 VALUES LESS THAN (MAXVALUE) ), PARTITION p2 VALUES LESS THAN(500) (SUBPARTITION s3 VALUES LESS THAN (500), SUBPARTITION s4 VALUES LESS THAN (MAXVALUE) ), PARTITION p3 VALUES LESS THAN(MAXVALUE) (SUBPARTITION s5 VALUES LESS THAN (500), SUBPARTITION s6 VALUES LESS THAN (MAXVALUE) ) ); gaussdb=# INSERT INTO t1 VALUES (100, 300, 1, 'aA'); gaussdb=# INSERT INTO t1 VALUES (150, 550, 1, 'bB'); gaussdb=# INSERT INTO t1 VALUES (250, 150, 1, 'cC'); gaussdb=# INSERT INTO t1 VALUES (350, 560, 1, 'dD'); gaussdb=# INSERT INTO t1 VALUES (550, 100, 1, 'eE'); gaussdb=# INSERT INTO t1 VALUES (650, 600, 1, 'fF'); -- 使用CREATE TABLE SUBPARTITION AS创建二级分区表，并使用子查询数据填充分区表。 gaussdb=# CREATE TABLE t1_part_dup PARTITION BY RANGE(a) SUBPARTITION BY RANGE(b) ( PARTITION p1 VALUES LESS THAN(200) (SUBPARTITION s1 VALUES LESS THAN (500), SUBPARTITION s2 VALUES LESS THAN (MAXVALUE) ), PARTITION p2 VALUES LESS THAN(500) (SUBPARTITION s3 VALUES LESS THAN (500), SUBPARTITION s4 VALUES LESS THAN (MAXVALUE) ), PARTITION p3 VALUES LESS THAN(MAXVALUE) (SUBPARTITION s5 VALUES LESS THAN (500), SUBPARTITION s6 VALUES LESS THAN (MAXVALUE) ) ) AS SELECT * FROM t1; gaussdb=# \d+ t1_part_dup; Table "create_table_as_partition_by.t1_part_dup" Column | Type | Modifiers | Storage | Stats target | Description --------+-------------------+-----------+----------+--------------+------------- a | integer | | plain | | b | integer | | plain | | c | integer | | plain | | d | character varying | | extended | | e | character varying | | extended | | Partition By RANGE(a) Subpartition By RANGE(b) Number of partitions: 3 (View pg_partition to check each partition range.) Number of subpartitions: 6 (View pg_partition to check each subpartition range.) Has OIDs: no Options: orientation=row, compression=no gaussdb=# SELECT a, b, c, d, e FROM t1_part_dup ORDER BY a; a | b | c | d | e -----+-----+---+----+--- 100 | 300 | 1 | aA | 150 | 550 | 1 | bB | 250 | 150 | 1 | cC | 350 | 560 | 1 | dD | 550 | 100 | 1 | eE | 650 | 600 | 1 | fF | (6 rows) gaussdb=# SELECT a, b, c, d, e FROM t1_part_dup PARTITION(p1) ORDER BY a; a | b | c | d | e -----+-----+---+----+--- 100 | 300 | 1 | aA | 150 | 550 | 1 | bB | (2 rows) gaussdb=# SELECT a, b, c, d, e FROM t1_part_dup PARTITION(s1) ORDER BY a; a | b | c | d | e -----+-----+---+----+--- 100 | 300 | 1 | aA | (1 row) gaussdb=# DROP TABLE t1, t1_part_dup;
