用exchange partition和split partition将表分区。
本文是以下面文章为基础做练习测试：
http://www.oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-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 (
idNUMBER(10),
created_dateDATE,
lookup_idNUMBER(10),
dataVARCHAR2(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 (
2id number(10),
3created_date date,
4lookup_id number(10),
5data varchar2(50)
6)
7partition by range (created_date)
8(partition big_table_2013 values less than (maxvalue));
增加主键：
SQL> alter table big_table2 add (
2constraint 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>
1alter table big_table2 add(
2constraint bita_look_fk2
3foreign key(lookup_id)
4* references lookup(id))
SQL> /
三.分区交换（EXCHANGE PARTITION）
SQL> alter table big_table2
2exchange partition big_table_2013
3with table big_table
4without validation
5update 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
2split partition big_table_2013 at(to_date('31-dec-2010 23:59:59','DD-MON-YYYY HH24:MI:SS'))
3into (partition big_table_2010,
4partition big_table_2013)
5update global indexes;
SQL> alter table big_table
2split partition big_table_2010 at(to_date('31-dec-2008 23:59:59','DD-MON-YYYY HH24:MI:SS'))
3into (partition big_table_2008,
4partition big_table_2010)
5update global indexes;
SQL> alter table big_table
2split 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
2where 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
页:
[1]