2022-10-11 达梦7 分区表操作

创建分区表

1.建表时创建表分区

CREATE TABLE "NBFG"."QUARTER_HOURPOWERPREDICTION_PARTITION"
(
"ID" INT NOT NULL,
"REPORTING_TIME" TIMESTAMP(0) NOT NULL,
"PREDICTION_TIME" TIMESTAMP(0) NOT NULL,
"PREDICTION_VALUE" DECIMAL(16,6),
"PREDICTION_TYPE" INT NOT NULL,
"DATA_TYPE" INT NOT NULL,
"STORAGE_TIME" TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP(),
PRIMARY KEY("DATA_TYPE", "PREDICTION_TYPE", "PREDICTION_TIME", "ID", "REPORTING_TIME")) 

partition by range(reporting_time)
(
	PARTITION foo_001 VALUES LESS THAN ('2022-08-01'),
	PARTITION foo_002 VALUES LESS THAN ('2022-09-01'),
	PARTITION foo_003 VALUES LESS THAN ('2022-10-01'),
	PARTITION foo_004 VALUES LESS THAN ('2022-11-01'),
	PARTITION foo_005 VALUES LESS THAN ('2022-12-01'),
	PARTITION foo_006 VALUES LESS THAN ('2023-01-01'),
	PARTITION foo_007 VALUES LESS THAN ('2023-02-01'),
	PARTITION foo_008 VALUES LESS THAN ('2023-03-01'),
	PARTITION foo_009 VALUES LESS THAN ('2023-04-01'),
	PARTITION foo_010 VALUES LESS THAN ('2023-05-01'),
	PARTITION p_other VALUES LESS THAN (MAXVALUE)
)
;

2.已创建的表，创建表分区

...

添加表分区

如果在表分区后增加连续范围分区：

alter table "NBFG"."QUARTER_HOURPOWERPREDICTION_PARTITION" 
add partition at(p_other) foo_011 VALUES LESS THAN ('2023-06-01');

若最后一个使用VALUES LESS THAN(MAXVALUE)表示，则使用：

alter table "NBFG"."QUARTER_HOURPOWERPREDICTION_PARTITION" 
split partition p_other at('2023-06-01') into (partition foo_011, partition foo_012)
;

查看分区

select partition_name from dba_tab_partitions where table_name='PART';