达梦数据库-分区篇

分区基础环境名词
1、INTERVAL(NUMTOYMINTERVAL(1, ‘year’)) 为自动分区设置，支持年、月
2、PARTITION BY RANGE(create_time) 其中RANGE为模式(具体参考官网考虑)，其create_time为分区表的依据字段 3、PRIMARY KEY(“id”) 主键索引设置
4、EXPLAIN 执行计划关键字

以下为YXYTEST模式下的c_business_formats表的调整DDL语句（仅供参考！！！）

-- 建立表分区
create tablespace "C_BUSINESS_FORMATS" datafile 'D:\Program Files\dmdbms\data\YXYTEST\C_BUSINESS_FORMATS.DBF' size 32 autoextend on maxsize 16777215 CACHE = NORMAL;

-- 建表
CREATE TABLE "YXYTEST"."c_business_formats"
(
"id" BIGINT NOT NULL,
"business_formats_code" VARCHAR(50),
"business_formats_name" VARCHAR(50),
"parent_id" BIGINT DEFAULT 0,
"parent_path" VARCHAR(255),
"status" INT DEFAULT 1,
"remark" VARCHAR(255),
"delete_flag" BIT DEFAULT BIN_TO_NUM(0),
"create_by" BIGINT DEFAULT 0 NOT NULL,
"create_time" TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP() NOT NULL,
"update_by" BIGINT DEFAULT 0 NOT NULL,
"update_time" TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP() NOT NULL,
NOT CLUSTER PRIMARY KEY("id")
) 
-- 设置分区规则
PARTITION BY RANGE(create_time)
        INTERVAL(NUMTOYMINTERVAL(1, 'year'))
(
        PARTITION LESSTHAN2024 VALUES LESS THAN (to_date('2024-01-01'))
) STORAGE(ON "c_business_formats", CLUSTERBTR);

附加操作指令说明

-- 添加分区
ALTER TABLE "YXYTEST"."c_business_formats"
ADD PARTITION HYZ2026 VALUES LESS THAN ('2027-01-01');

-- 删除分区
ALTER TABLE "YXYTEST"."c_business_formats"
DROP PARTITION HYZ2025;

-- 合并分区
ALTER TABLE "YXYTEST"."c_business_formats"
MERGE PARTITIONS HYZ2025,HYZ2026 INTO PARTITION HYZ2025_2026;

-- 拆分分区
ALTER TABLE "YXYTEST"."c_business_formats"
SPLIT PARTITION HYZ2025_2026 INTO (
    PARTITION HYZ2025 VALUES LESS THAN ('2026-01-01'),
    PARTITION HYZ2026 VALUES LESS THAN ('2027-01-01')
);

-- 查看执行计划
EXPLAIN SELECT * FROM "YXYTEST"."c_business_formats" WHERE create_time < '2023-06-01';

-- copy表数据到新的表
INSERT INTO "YXYTEST"."c_business_formats" SELECT * FROM "YXYCOST"."c_business_formats";
commit;

-- 查看某张表的分区表 示例：c_business_formats
SELECT table_name,partition_name, high_value FROM dba_tab_partitions
WHERE table_name='c_business_formats' ORDER BY high_value;