oracle 分区交换

需求

        一个几亿条数据的分区表，要把一些老数据分出来，放一个新建的表上。按时间划分，指定时间以前的放在老数据表，指定时间以后的数据保留在原表，然后把备份老数据表空间truncate掉。按照分区操作分区交换是最好的解决办法。

分区交换技术可以实现数据快速转移，所以在数据加载提速，历史数据清理等方面特别有用。分区交换技术实际上只修改了数据字典中的数据物理段位置，而不是实际的移动数据，所以速度很快。

交换分区的操作步骤如下

        1. 创建分区表t1，假设有2个分区，P1，P2. 
        2. 创建基表t11存放P1规则的数据。 
        3. 创建基表t12 存放P2规则的数据。 
        4. 用基表t11和分区表T1的P1分区交换。 把表t11的数据放到到P1分区 
        5. 用基表t12 和分区表T1p2 分区交换。 把表t12的数据存放到P2分区。

测试操作步骤

create table t1 
( 
sid int not null, 
sname  varchar2(50) 
) 
PARTITION BY list(sname) 
( PARTITION p1 VALUES('zhangsan'), 
  PARTITION p2 VALUES('lisi'), 
  PARTITION p3  VALUES('wangwu') 
); 
--local：每个分区上都建立索引(本地索引),查询条件里面带有分区字段时local效率高
--global：一种是在全局上建立索引，查询条件里面没带有分区字段，全局查询时global效率高
-- 创建索引时使用nologging选项可以加快速度，节省时间，减少产生的日志量
create index ix_t1_sname on t1(sname) nologging local ;
 
create table t11 
( 
sid int not null , 
sname  varchar2(50) 
);

create table t12 
( 
sid int not null , 
sname  varchar2(50) 
); 

create table t13 
( 
sid int not null , 
sname  varchar2(50) 
); 
create index ix_t11_sname on t11(sname) nologging;
create index ix_t12_sname on t12(sname) nologging;
create index ix_t13_sname on t13(sname) nologging;


declare 
  maxrecords constant int:=4999; 
  i int :=1; 
begin 
  for i in 1..maxrecords loop 
    insert into t11 values(i,'zhangsan'); 
  end loop; 
dbms_output.put_line(' 成功录入数据！ '); 
commit; 
end;  


declare 
  maxrecords constant int:=9999; 
  i int :=5000; 
begin 
  for i in 5000..maxrecords loop 
    insert into t12 values(i,'lisi'); 
  end loop; 
dbms_output.put_line(' 成功录入数据！ '); 
commit; 
end;  


declare 
  maxrecords constant int:=70000; 
  i int :=10000; 
begin 
  for i in 10000..maxrecords loop 
    insert into t13 values(i,'wangwu'); 
  end loop; 
dbms_output.put_line(' 成功录入数据！ '); 
commit; 
end;  

select count(*) from t1;
select count(*) from t11; 
select count(*) from t12; 
select count(*) from t13; 

alter table t1 exchange partition p1 with table t11 update indexes; 
alter table t1 exchange partition p2 with table t12 update indexes; 
alter table t1 exchange partition p3 with table t13 update indexes; 

select count(*) from t1 partition (p1);
select count(*) from t1 partition (p2);
select count(*) from t1 partition (p3);

-- 注意：
--1 按PARTITION BY list(sname)  ( PARTITION p1 VALUES('zhangsan'),..)分区时，sql中sname like 'zhangsan%' 和 sname like 'zhang%'可以命中分区，sname like 'zhangsansan%'等无法命中会全表扫描
--2 经过alter table t1 exchange partition p1 with table t11;分区转换后，t1表的包括主键在内的所有都失效了，所有查询都不走索引了
-- 2.1 解决方案 : 加 including indexes

实际使用案例：

/**
考虑到DE_INOCULATE数据链比较大现采用分区交换方式创建分区，交换分区的操作步骤如下:
1. 创建分区表t1，假设有2个分区，P1，P2. 
2. 创建基表t11存放P1规则的数据。 
3. 创建基表t12 存放P2规则的数据。 
4. 用基表t11和分区表T1的P1分区交换。 把表t11的数据放到到P1分区 
5. 用基表t12 和分区表T1p2 分区交换。 把表t12的数据存放到P2分区。
**/
--1 创建分区表
create table DE_INOCULATE_PART
(
  inoc_id        NUMBER(24) not null,
  pers_no        VARCHAR2(32 CHAR),
  pers_name      VARCHAR2(128 CHAR),
  pers_sex       VARCHAR2(8 CHAR),
  pers_birthday  DATE,
  pers_card_type VARCHAR2(8 CHAR),
  pers_card_no   VARCHAR2(256 CHAR),
  pers_tel       VARCHAR2(128 CHAR),
  vacc_code      VARCHAR2(4 CHAR),
  vacc_name      VARCHAR2(64 CHAR),
  cur_code       VARCHAR2(32 CHAR),
  inoc_needle    NUMBER(10),
  inoc_time      DATE,
  inoc_depa_code VARCHAR2(12 CHAR),
  inoc_doctor    VARCHAR2(128 CHAR),
  corp_code      VARCHAR2(4 CHAR),
  corp_name      VARCHAR2(128 CHAR),
  batch_no       VARCHAR2(64 CHAR),
  valid_date     DATE,
  dose_name      VARCHAR2(128 CHAR),
  spec_name      VARCHAR2(128 CHAR),
  vacc_price     VARCHAR2(16 CHAR),
  create_time    DATE,
  data_status    VARCHAR2(255 CHAR),
  sync_status    VARCHAR2(1 CHAR) default '0'
) 
PARTITION BY list(vacc_code) 
( PARTITION p1 VALUES('5601','5602','5603','5604','5605','5606','5607','5608','5609','5610','5611','5612','5613','5621','5622','5623','5699'), 
  PARTITION p2 VALUES('2101','2102','2103','2104','2105','2106','2107','2108','2109','2110','2111','2112','2113','2121','2122','2123','2199'), 
  PARTITION p3 VALUES('2201','2202','2203','2204','2205','2206','2207','2208','2209','2210','2211','2212','2213','2221','2222','2223','2299') 
);
comment on column DE_INOCULATE_PART.inoc_id is '主键';
comment on column DE_INOCULATE_PART.pers_no is '18位档案编码';
comment on column DE_INOCULATE_PART.pers_name is '姓名';
comment on column DE_INOCULATE_PART.pers_sex is '性别';
comment on column DE_INOCULATE_PART.pers_birthday is '生日';
comment on column DE_INOCULATE_PART.pers_card_type is '证件类型';
comment on column DE_INOCULATE_PART.pers_card_no is '证件号';
comment on column DE_INOCULATE_PART.pers_tel is '手机号码';
comment on column DE_INOCULATE_PART.vacc_code is '疫苗编码';
comment on column DE_INOCULATE_PART.vacc_name is '疫苗名称';
comment on column DE_INOCULATE_PART.cur_code is '追溯码';
comment on column DE_INOCULATE_PART.inoc_needle is '针次';
comment on column DE_INOCULATE_PART.inoc_time is '接种时间';
comment on column DE_INOCULATE_PART.inoc_depa_code is '接种单位编码';
comment on column DE_INOCULATE_PART.inoc_doctor is '接种医生';
comment on column DE_INOCULATE_PART.corp_code is '厂家编码';
comment on column DE_INOCULATE_PART.corp_name is '厂家名称';
comment on column DE_INOCULATE_PART.batch_no is '批号';
comment on column DE_INOCULATE_PART.valid_date is '效期';
comment on column DE_INOCULATE_PART.dose_name is '剂量';
comment on column DE_INOCULATE_PART.spec_name is '规格';
comment on column DE_INOCULATE_PART.vacc_price is '价格';
comment on column DE_INOCULATE_PART.create_time is '创建时间';
comment on column DE_INOCULATE_PART.data_status is '数据状态 1-可用 0-禁用';
comment on column DE_INOCULATE_PART.sync_status is '同步外网状态';
  
  
-- 1.1 创建分区表本地索引
create index IDX_PART_DE_CREATE_TIME on DE_INOCULATE_PART (CREATE_TIME DESC) nologging local ;
create index IDX_PART_DI_DEPA_CODE_IN on DE_INOCULATE_PART (INOC_DEPA_CODE) nologging local ;
create index IDX_PART_DI_INOC_TIME on DE_INOCULATE_PART (INOC_TIME) nologging local ;
create index IDX_PART_NAME_NO_IN on DE_INOCULATE_PART (PERS_NAME, PERS_CARD_NO) nologging local ;
create index IDX_PART_NAME_SEX_BIRTHDAY_TEL_IN on DE_INOCULATE_PART (PERS_TEL, PERS_NAME, PERS_SEX, PERS_BIRTHDAY) nologging local ;
create index IDX_PART_PERSNO_VACCCODE_INOCNEED on DE_INOCULATE_PART (PERS_NO, VACC_CODE, INOC_NEEDLE) nologging local ;

--1.2 删除DE_INOCULATE表主键，注意：如果失败请检查主键名称是否对得上
alter table DE_INOCULATE drop constraint  PRIMARY_2_in;

--2 分区交换，注意：如果失败请检查主步骤1.1的索引与DE_INOCULATE表的索引是否对得上, 对得上再检查select * from DE_INOCULATE where vacc_code not like '56%'
alter table DE_INOCULATE_PART exchange partition p1 with table DE_INOCULATE including indexes;

-- 2.1查询分区交换是否成功
--select count(*) from DE_INOCULATE_PART partition (p1); 

--2.2修改表名
alter table DE_INOCULATE rename to DE_INOCULATE_BAK_PART;
alter table DE_INOCULATE_PART rename to DE_INOCULATE;

--2.3创建主键 比较耗时
alter table DE_INOCULATE add constraint PRIMARY_2_in primary key (INOC_ID);