达梦创建复杂分区表

最新推荐文章于 2025-09-27 14:28:10 发布

原创最新推荐文章于 2025-09-27 14:28:10 发布 · 838 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #运维 #sql

达梦数据库专栏收录该内容

12 篇文章

订阅专栏

1. Creating Interval-Partitioned Tables

drop table PARTTAB1;
create table PARTTAB1
(
  ID INT PRIMARY KEY,
  CONTENT1 VARCHAR(20),
  CONTENT2 VARCHAR(20),
  INSERT_TIME DATE
)
PARTITION BY RANGE (INSERT_TIME) 
  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
    ( PARTITION p0 VALUES LESS THAN ('2024-01-01') tablespace MAIN,
      PARTITION p1 VALUES LESS THAN ('2024-02-01') tablespace MAIN,
      PARTITION p2 VALUES LESS THAN ('2024-03-01') tablespace MAIN,
      PARTITION p3 VALUES LESS THAN ('2024-04-01') tablespace MAIN )
storage(NOBRANCH);

insert into PARTTAB1 values(1,'test','test',TO_DATE('2024-01-15','YYYY-MM-DD'));
insert into PARTTAB1 values(2,'test','test',TO_DATE('2024-02-15','YYYY-MM-DD'));
insert into PARTTAB1 values(3,'test','test',TO_DATE('2024-03-15','YYYY-MM-DD'));
insert into PARTTAB1 values(4,'test','test',TO_DATE('2024-04-15','YYYY-MM-DD'));
insert into PARTTAB1 values(5,'test','test',TO_DATE('2024-05-15','YYYY-MM-DD'));
commit;

插入数据时如果分区字段为null，数据会存放在最小的分区中

insert into PARTTAB1 values(6,'test','test',NULL);
SELECT * FROM SYS.DBA_TAB_PARTITIONS WHERE TABLE_NAME='PARTTAB1';
SELECT * FROM PARTTAB1 PARTITION (P0);

2. Creating Interval-Reference Partitioned Tables

drop table PARTTAB2;
create table PARTTAB2
(
  id            number not null ,
  log_id        VARCHAR2(64) not null,
  column_code   VARCHAR2(128) not null,
  old_value     VARCHAR2(4000),
  new_value     VARCHAR2(4000),
  is_rollback   CHAR(1) default 0,
  rollback_date DATE,
  rollback_by   VARCHAR2(256),
  need_syn      CHAR(1),
  is_log        CHAR(1) default 1,
  CONSTRAINT fk_sys_record_log_detail
      FOREIGN KEY(log_id) REFERENCES SYS_RECORD_LOG(id)
)
PARTITION BY REFERENCE(fk_sys_record_log_detail);

Oracle11g新特性-引用分区(reference partitioning)：如果父表是分区表，子表可以按照父表的方式进行分区，父表中被引用的列不一定要是分区键。
达梦中暂不支持，但语法没有报错

3. Creating Composite List-Range Partitioned Tables

drop table XXL_JOB_QRTZ_TRIGGER_LOG;
create table XXL_JOB_QRTZ_TRIGGER_LOG
(
  id                        NUMBER(11) not null,
  job_group                 NUMBER(11) not null,
  job_id                    NUMBER(11) not null,
  executor_address          VARCHAR2(255),
  executor_handler          VARCHAR2(255),
  executor_param            VARCHAR2(512),
  executor_sharding_param   VARCHAR2(64),
  executor_fail_retry_count NUMBER(11) default 0 not null,
  trigger_time              DATE,
  trigger_code              NUMBER(11) not null,
  trigger_msg               NCLOB,
  handle_time               DATE,
  handle_code               NUMBER(11) not null,
  handle_msg                NCLOB,
  log_type                  CHAR(1) default 'L' not null
)
partition by list(handle_code) subpartition by range (handle_time)  
( 
 partition xxl_jqtl_part_t values (200) tablespace MAIN
 (
   subpartition xxl_jqtl_part_t_2024_07_01 values less than (TO_DATE('2024-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN,
   subpartition xxl_jqtl_part_t_2024_07_02 values less than (TO_DATE('2024-07-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN,
   subpartition xxl_jqtl_part_t_2024_07_03 values less than (TO_DATE('2024-07-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN,
   subpartition xxl_jqtl_part_t_2024_07_04 values less than (TO_DATE('2024-07-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN,
   subpartition xxl_jqtl_part_t_2024_07_05 values less than (TO_DATE('2024-07-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN, 
   subpartition xxl_jqtl_part_t_2024_07_06 values less than (TO_DATE('2024-07-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN, 
   subpartition xxl_jqtl_part_t_2024_07_07 values less than (TO_DATE('2024-07-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN, 
   subpartition xxl_jqtl_part_t_2024_07_08 values less than (TO_DATE('2024-07-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN,
   subpartition xxl_jqtl_part_t_2024_07_09 values less than (TO_DATE('2024-07-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN, 
   subpartition xxl_jqtl_part_t_2024_07_10 values less than (TO_DATE('2024-07-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN, 
   subpartition xxl_jqtl_part_t_2024_07_11 values less than (TO_DATE('2024-07-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN, 
   subpartition xxl_jqtl_part_t_2024_07_12 values less than (TO_DATE('2024-07-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN 
 ),                 
 partition xxl_jqtl_part_f values (0,500,900) tablespace MAIN
 (
   subpartition xxl_jqtl_part_f_2024_01 values less than (TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN,
   subpartition xxl_jqtl_part_f_2024_02 values less than (TO_DATE('2024-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN,
   subpartition xxl_jqtl_part_f_2024_03 values less than (TO_DATE('2024-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN,
   subpartition xxl_jqtl_part_f_2024_04 values less than (TO_DATE('2024-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN,
   subpartition xxl_jqtl_part_f_2024_05 values less than (TO_DATE('2024-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN, 
   subpartition xxl_jqtl_part_f_2024_06 values less than (TO_DATE('2024-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN, 
   subpartition xxl_jqtl_part_f_2024_07 values less than (TO_DATE('2024-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN, 
   subpartition xxl_jqtl_part_f_2024_08 values less than (TO_DATE('2024-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN,
   subpartition xxl_jqtl_part_f_2024_09 values less than (TO_DATE('2024-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN, 
   subpartition xxl_jqtl_part_f_2024_10 values less than (TO_DATE('2024-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN, 
   subpartition xxl_jqtl_part_f_2024_11 values less than (TO_DATE('2024-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN, 
   subpartition xxl_jqtl_part_f_2024_12 values less than (TO_DATE('2024-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace MAIN 
 )
) storage(NOBRANCH);

这里用Oracle的语法不会报错，但部分字段类型会被转换

SELECT * FROM SYS.DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='XXL_JOB_QRTZ_TRIGGER_LOG' ORDER BY PARTITION_NAME,SUBPARTITION_NAME;


-- ADD/DROP主分区
ALTER TABLE XXL_JOB_QRTZ_TRIGGER_LOG 
   ADD PARTITION xxl_jqtl_part_x values (1)
    (subpartition xxl_jqtl_part_x_2022_02_01 values less than (DATE'2024-07-01') tablespace MAIN);

ALTER TABLE XXL_JOB_QRTZ_TRIGGER_LOG 
RENAME PARTITION xxl_jqtl_part_x 
TO xxl_jqtl_part_x0;

ALTER TABLE XXL_JOB_QRTZ_TRIGGER_LOG DROP PARTITION xxl_jqtl_part_x0;


-- ADD/DROP子分区
ALTER TABLE XXL_JOB_QRTZ_TRIGGER_LOG
   MODIFY PARTITION xxl_jqtl_part_x 
      ADD SUBPARTITION xxl_jqtl_part_x_2022_02_02 values less than (DATE'2024-07-02') tablespace MAIN;
	  
ALTER TABLE XXL_JOB_QRTZ_TRIGGER_LOG 
RENAME SUBPARTITION xxl_jqtl_part_x_2022_02_02 
 TO xxl_jqtl_part_x_2022_02_020;

ALTER TABLE XXL_JOB_QRTZ_TRIGGER_LOG DROP SUBPARTITION xxl_jqtl_part_x_2022_02_020;

4. create index

alter table XXL_JOB_QRTZ_TRIGGER_LOG add primary key(ID);
create index idx_xxl_jqtl_job_group on XXL_JOB_QRTZ_TRIGGER_LOG(job_group);
create index idx_xxl_jqtl_job_id on XXL_JOB_QRTZ_TRIGGER_LOG(job_id) GLOBAL;
SELECT TABLE_NAME,INDEX_NAME,PARTITIONED FROM SYS.DBA_INDEXES WHERE TABLE_NAME='XXL_JOB_QRTZ_TRIGGER_LOG';

达梦分区表创建索引，默认为局部索引，主键是全局索引

Tips!!!

当分区表存在MAXVALUE分区时，不可添加分区
只能对范围分区和LIST分区进行删除分区，哈希分区不支持删除分区
删除分区时，不会影响局部索引，全局索引会自动重建
当表数据量较大时，建议通过禁用全局索引再删除表分区，删除完成后再进行索引重建
禁用全局索引
alter index 用户名.索引名 unusable;
重建全局索引
alter index 用户名.索引名 rebuild online;

达梦社区地址
https://eco.dameng.com