达梦数据库（DM7）间隔分区表

1.1 创建表

CREATE TABLE t_range_interval
  (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
  PARTITION BY RANGE (deal_date)
  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
    (  partition p_201911 values less than (TO_DATE('2019-12-01', 'YYYY-MM-DD')),
       partition p_201912 values less than (TO_DATE('2020-01-01', 'YYYY-MM-DD')));

1.2 插入数据

insert into t_range_interval (id,deal_date,area_code,nbr,contents)
      select rownum,
             sysdate-360+ TRUNC(DBMS_RANDOM.VALUE(0,365)),
             ceil(dbms_random.value(591,599)),
             ceil(dbms_random.value(18900000001,18999999999)),
             rpad('*',400,'*')
        from dual
      connect by rownum <= 10000;

 select MIN(deal_date),MAX(deal_date)  from t_range_interval;

1.3 查看自动生成的分区

select segment_name,
       partition_name,
       segment_type,
       bytes ,
       tablespace_name
  from user_segments
 where  segment_name ='T_RANGE_INTERVAL';

1.4 验证数据分布

select          
(select count(*) from T_RANGE_INTERVAL partition(SYS_P2662_2673)) SYS_P2662_2673,
(select count(*) from T_RANGE_INTERVAL partition(SYS_P2662_2671)) SYS_P2662_2671,
(select count(*) from T_RANGE_INTERVAL partition(SYS_P2662_2669)) SYS_P2662_2669,
(select count(*) from T_RANGE_INTERVAL partition(SYS_P2662_2667)) SYS_P2662_2667,
(select count(*) from T_RANGE_INTERVAL partition(SYS_P2662_2665)) SYS_P2662_2665,
(select count(*) from T_RANGE_INTERVAL partition(P_201912)) P_201912,
(select count(*) from T_RANGE_INTERVAL partition(P_201911)) P_201911
from dual

select  MIN(deal_date),MAX(deal_date),'SYS_P2662_2673' from T_RANGE_INTERVAL partition(SYS_P2662_2673)
UNION
select  MIN(deal_date),MAX(deal_date),'SYS_P2662_2671' from T_RANGE_INTERVAL partition(SYS_P2662_2671)
UNION
select  MIN(deal_date),MAX(deal_date),'SYS_P2662_2669' from T_RANGE_INTERVAL partition(SYS_P2662_2669)
UNION
select  MIN(deal_date),MAX(deal_date),'SYS_P2662_2667' from T_RANGE_INTERVAL partition(SYS_P2662_2667)
UNION
select  MIN(deal_date),MAX(deal_date),'SYS_P2662_2665' from T_RANGE_INTERVAL partition(SYS_P2662_2665)
UNION
select  MIN(deal_date),MAX(deal_date),'P_201912' from T_RANGE_INTERVAL partition(P_201912)
UNION
select  MIN(deal_date),MAX(deal_date),'P_201911' from T_RANGE_INTERVAL partition(P_201911)