The important thing in life is to have a great aim , and the determination

普通表轉(zhuǎn)分區(qū)表和交換分區(qū)（oracle）

將普通表轉(zhuǎn)換成分區(qū)表有4種方法：  

       1. Export/import method  

       2. Insert with a subquery method  

       3. Partition exchange method  

       4. DBMS_REDEFINITION           

    select * from t_user_info_test;  

    --方法一   

    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 * from t_phone_test partition(p0);  

  

select * from t_phone_test where part='0';  

  

--方法二 交換分區(qū)   

     這種方法只是對數(shù)據(jù)字典中分區(qū)和表的定義進行了修改，沒有數(shù)據(jù)的修改或復(fù)制，效率最高。適用于包含大數(shù)據(jù)量的表轉(zhuǎn)到分區(qū)表中的一個分區(qū)的操作。盡量在閑時進行操作。  

  

交換分區(qū)的操作步驟如下：  

     1. 創(chuàng)建分區(qū)表，假設(shè)有2個分區(qū)，P1，P2.  

     2. 創(chuàng)建表A存放P1規(guī)則的數(shù)據(jù)。  

     3. 創(chuàng)建表B 存放P2規(guī)則的數(shù)據(jù)。  

     4. 用表A 和P1 分區(qū)交換。 把表A的數(shù)據(jù)放到到P1分區(qū)  

     5. 用表B 和p2 分區(qū)交換。 把表B的數(shù)據(jù)存放到P2分區(qū)。  

  

  

  

create table t_phone_test_0 nologging  

as   

select user_mobile phone,substr(user_mobile,-1,1) part  

from t_user_info_test where substr(user_mobile,-1,1)='0';  

  

select count(*) from t_phone_test where part='0';  

--4410   

select count(*) from t_user_info_test where substr(user_mobile,-1,1)='0';  

--4410   

  

alter table t_phone_test exchange partition p0 with table t_phone_test_0;  

  

  

delete from   t_phone_test_0;  

  

select count(*) from t_phone_test where part='0';  

select count(*) from t_phone_test_0;  

  

insert into t_phone_test(phone,part) values('15267046070','0');  

  

--p0一條數(shù)據(jù)，t_phone_test_0里4410條數(shù)據(jù)，交換之后p0是4410，t_phone_test_0是1，再執(zhí)行一次數(shù)據(jù)又換回來了。   

  

insert into t_phone_test_0(phone,part) values('15267046070','1');  

alter table t_phone_test exchange partition p0 with table t_phone_test_0;  

delete from t_phone_test_0 where part='1';  

  

  

--合并分區(qū)   

----alter table tbname merge partitions/subpartitions pt1,pt2 into partition/subpartition pt3;   

  

alter table t_phone_test merge partitions p0,p1 into partition p0;  

  

  

select count(*) from t_phone_test where part='0';  

select count(*) from t_phone_test where part='1';  

  

select count(*)  from t_phone_test partition(p0);  

select count(*)  from t_phone_test partition(p1);  

  

  

  

 alter table t_phone_test  add partition p10 values(default);  

  

insert into t_phone_test(phone,part) values('15267046010','10');  

insert into t_phone_test(phone,part) values('15267046020','20');  

  

select * from   

  

--   

alter table t_phone_test drop partition p10;  

 alter table t_phone_test  add partition p10 values( '10');  

   

alter table t_phone_test exchange partition p10 with table t_phone_test_10;  

--ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION   

alter table T_PHONE_TEST_10 modify PART VARCHAR2(2);  

alter table t_phone_test merge partitions p0,p10 into partition p0;  

  

--此時p0中有p0和p10的數(shù)據(jù)，但是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  

    ),  

      

alter table t_phone_test exchange partition p0 with table t_phone_test_10;     

alter table t_phone_test drop partition p0;  

alter table t_phone_test  add partition p0 values( '0');      

  

alter table t_phone_test exchange partition p0 with table t_phone_test_10;     

  

  

drop table t_phone_test_10 purge;  

create table t_phone_test_10 nologging  

as   

select user_mobile phone,substr(user_mobile,-2,2) part  

from t_user_info_test where substr(user_mobile,-2,2)='10';  

  

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;  

  

alter table t_phone_test exchange partition p0 with table t_phone_test_0;  

  

  

select * from t_phone_test_10;  

  

  

  

select count(*)  from t_phone_test partition(p0);  

select count(*)  from t_phone_test partition(p10);  

select count(*) from t_phone_test_10;  

select count(*) from t_phone_test_0;  

  

  

  

select substr('123456',-1,1),substr('123456',-2,2),substr('123456',-3,2) from dual;  

  

  

---------------------------------------------------------   

1.創(chuàng)建分區(qū)表  

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.創(chuàng)建基表  

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.添加分區(qū)  

alter table t_phone_test  add partition p10 values( '10');      

select count(*)  from t_phone_test partition(p10);--0   

4.交換分區(qū)  

alter table t_phone_test exchange partition p10 with table t_phone_test_10;     

select count(*)  from t_phone_test partition(p10);--406   

5.合并分區(qū)  

alter table t_phone_test merge partitions p0,p10 into partition p0;  

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

--此時p0中有p0和p10的數(shù)據(jù)，但是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.交換分區(qū)  

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.刪除分區(qū) 和添加分區(qū)  

alter table t_phone_test  drop partition p0;  

alter table t_phone_test  add partition p0 values('0');  

  

7.篩選數(shù)據(jù)  

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.交換分區(qū)  

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  

posted on 2014-05-07 22:31 鴻雁 閱讀(322) 評論(0)  編輯  收藏 所屬分類: 數(shù)據(jù)庫