用exchange partition和split partition将表分区。

opli

本文是以下面文章为基础做练习测试：

http://www.oracle-base.com/artic ... hange-partition.php

这个方法更适合在生产环境中将普通表变为分区表。

主要方法有分区转换与分区分裂。
一.创建一个模拟环境

1. 创建一个小表

SQL> create table lookup(

id number(10),

description varchar2(50)

);

加主键

SQL> alter table lookup add ( constraint lookup_pk primary key(id));

插入数据

SQL> INSERT INTO lookup (id, description) VALUES (1, 'ONE');

INSERT INTO lookup (id, description) VALUES (2, 'TWO');

INSERT INTO lookup (id, description) VALUES (3, 'THREE');



2.创建一个大表

SQL> CREATE TABLE big_table (

id  NUMBER(10),

created_date  DATE,

lookup_id  NUMBER(10),

data  VARCHAR2(50)

);

增加数据：



SQL> declare

t_lookup_id lookup.id%TYPE;

t_create_date date;

begin

for i in 1 .. 1000000 loop

if mod(i,3)=0 then

t_create_date:=add_months(sysdate,-24);

t_lookup_id :=2;

elsif mod(i,2)=0 then

t_create_date:=add_months(sysdate,-12);

t_lookup_id :=1;

else

t_create_date:=sysdate;

t_lookup_id:=3;

end if;

INSERT INTO big_table(id, created_date, lookup_id, data) VALUES (i, t_create_date, t_lookup_id, 'This is some data for ' || i);

end loop;

commit;

end;

/

加主键

ALTER TABLE big_table ADD (

  CONSTRAINT big_table_pk PRIMARY KEY (id)

);

加外键

ALTER TABLE big_table ADD (

  CONSTRAINT bita_look_fk

  FOREIGN KEY (lookup_id)

  REFERENCES lookup(id)

);

加索引

CREATE INDEX bita_created_date_i ON big_table(created_date);

CREATE INDEX bita_look_fk_i ON big_table(lookup_id);



收集表信息

EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE);

EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
二.创建一个分区的目标表。

SQL> create table big_table2 (

  2  id number(10),

  3  created_date date,

  4  lookup_id number(10),

  5  data varchar2(50)

  6  )

  7  partition by range (created_date)

  8  (partition big_table_2013 values less than (maxvalue));

增加主键：

SQL> alter table big_table2 add (

  2  constraint big_table_pk2 primary key(id)

  3  );

增加相应的索引

SQL> create index bita_created_date_i2 on big_table2(created_date) local;

SQL> create index bita_look_fk_i2 on big_table2(lookup_id) local;



增加外键

SQL>

  1  alter table big_table2 add(

  2  constraint bita_look_fk2

  3  foreign key(lookup_id)

  4* references lookup(id))

SQL> /
三.分区交换（EXCHANGE PARTITION）

SQL> alter table big_table2

  2  exchange partition big_table_2013

  3  with table big_table

  4  without validation

  5  update global indexes;

SQL> drop table big_table;

SQL> rename big_table2 to big_table;

SQL> alter table big_table rename constraint big_table_pk2 to big_table_pk;

SQL> alter table big_table rename constraint bita_look_fk2 to bita_look_fk;

SQL> alter index big_table_pk2 rename to big_table_pk;

SQL> alter index bita_look_fk_i2 rename to bita_look_fk_i;

SQL> alter index bita_created_date_i2 rename to bita_created_date_i;
四.分区分裂（SPLIT PARTITION）

SQL> alter table big_table

  2  split partition big_table_2013 at(to_date('31-dec-2010 23:59:59','DD-MON-YYYY HH24:MI:SS'))

  3  into (partition big_table_2010,

  4  partition big_table_2013)

  5  update global indexes;



SQL> alter table big_table

  2  split partition big_table_2010 at(to_date('31-dec-2008 23:59:59','DD-MON-YYYY HH24:MI:SS'))

  3  into (partition big_table_2008,

  4  partition big_table_2010)

  5  update global indexes;

SQL> alter table big_table

  2  split partition big_table_2013 at(to_date('31-dec-2012 23:59:59','DD-MON-YYYY HH24:MI:SS'))

into (partition big_table_2012,

partition big_table_2013)

update global indexes;



SQL> exec dbms_stats.gather_table_stats(user,'big_table',cascade=>true);
五.进行检查：

SQL> select partitioned from user_tables

  2  where table_name='BIG_TABLE';



PAR

---

YES

SQL> select partition_name,num_rows from user_tab_partitions

where table_name='BIG_TABLE';  2



PARTITION_NAME                   NUM_ROWS

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

BIG_TABLE_2008                          0

BIG_TABLE_2010                          0

BIG_TABLE_2012                     666667

BIG_TABLE_2013                     333333