|
本文是以下面文章为基础做练习测试:
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
|
|