交互分区方式可以替换分区表中的某个分区,典型地,当前分区为非压缩表,可以构建相同数据的压缩表替换掉这个分区。
例如:
--1.创建分区转换临时表
CREATE TABLE my_schema.for_exchange
with (APPENDONLY=true, COMPRESSLEVEL=9)
as (select * from my_schema.current_partition)
DISTRIBUTED BY (day_id, fst_agc_offc_cd, carr_cd, flt_nbr);
--3.比较数据内容
select 'org',count(*) from my_schema.current_partition group by 1
union
select 'new',count(*) from my_schema.for_exchange group by 1
===============================================================
"new";35375423
"org";35375423
===========================================================
--4.交换分区
alter table my_schema.schl_pnrd_daily exchange partition month201303 with table my_schema.for_exchange
--5.检验交换无问题
select 'org',count(*) from my_schema.current_partition group by 1
union
select 'new',count(*) from my_schema.for_exchange group by 1
"new";35375423
"org";35375423
-- 6.删除交换出去的分区
drop table my_schema.for_exchange