达梦创建复杂分区表
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
原文地址:https://blog.csdn.net/qq_25045631/article/details/140646425
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!