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

     有个模型：一张非分区表，一个分区表，现在需要不通过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