通过交换分区的方式将数据以累加的形式添加到分区表中

来源：互联网发布：java培训班编辑：程序博客网时间：2024/04/28 10:03

有个模型：一张非分区表，一个分区表，现在需要不通过insert的方式，将数据添加到分区表中。

http://blog.csdn.net/tianlesoftware/article/details/4717318

http://blog.csdn.net/robinson_0612/article/details/5925572

---------------------------------------------------------
1.创建分区表
drop table t_phone_test purge;
create table t_phone_test(phone,part) nologging partition by list(part)
(
partition p0 values('0'),
partition p1 values('1'),
partition p2 values('2'),
partition p3 values('3'),
partition p4 values('4'),
partition p5 values('5'),
partition p6 values('6'),
partition p7 values('7'),
partition p8 values('8'),
partition p9 values('9')
)
as
select user_mobile phone,substr(user_mobile,-1,1) part
from t_user_info_test;

select count(*) from t_phone_test partition(p0);--4410
select count(*) from t_phone_test partition(p10);
select count(*) from t_phone_test_10;
select count(*) from t_phone_test_0;

2.创建基表
drop table t_phone_test_10 purge;
create table t_phone_test_10 nologging
as
select phone,substr(phone,-2,2) part
from t_phone_test where substr(phone,-2,2)='10';

select count(*) from t_phone_test_10;--406

--ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
alter table T_PHONE_TEST_10 modify PART VARCHAR2(2);

3.添加分区
alter table t_phone_test add partition p10 values( '10');
select count(*) from t_phone_test partition(p10);--0
4.交换分区
alter table t_phone_test exchange partition p10 with table t_phone_test_10;
select count(*) from t_phone_test partition(p10);--406
5.合并分区
alter table t_phone_test merge partitions p0,p10 into partition p0;
select count(*) from t_phone_test partition(p0);--4816
--此时p0中有p0和p10的数据，但是p0的list不再是0而是0和10
partition P0 values ('10', '0')
    tablespace APP_DATAN
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),

6.交换分区
alter table t_phone_test exchange partition p0 with table t_phone_test_10;

select count(*) from t_phone_test partition(p0);--0
select count(*) from t_phone_test_10;--4816

6.删除分区和添加分区
alter table t_phone_test drop partition p0;
alter table t_phone_test add partition p0 values('0');

7.筛选数据
drop table t_phone_test_0 purge;
create table t_phone_test_0 nologging
as
select phone,substr(phone,-1,1) part
from t_phone_test_10 where substr(phone,-1,1)='0';

select count(*) from t_phone_test_0;--4816

8.交换分区
alter table t_phone_test exchange partition p0 with table t_phone_test_0;

select count(*) from t_phone_test partition(p0);--4816
select count(*) from t_phone_test_0;--0

0 0