今天有个哥们说,要把四个普通表的数据全部导到一个分区表中,他说一个表大概也就2到3亿的记录,如果插入的方式的话,估计得要十几个小时,问我有什么办法!我问了一下,结构相同吗,他说相同,并且每个表有一个日期字段,第一个表是08年的,第二个是09年的,第三个是10年的,第四个是11年的!一听这个样子,真的很好办,我说,按我的步骤做绝对没问题,下面就把今天做的与大家分享一下:
以下是我写给他的测试步骤:
1、首先建几个表来测试一下
SQL> create table tab_part1 as select object_id,object_name from dba_objects where object_id < 30000;
Table created.
SQL> select count(*) from tab_part1;
COUNT(*)
----------
29191
SQL> create table tab_part2 as select object_id,object_name from dba_objects where object_id < 60000 and object_id >=30000;
Table created.
SQL> select count(*) from tab_part2;
COUNT(*)
----------
29309
SQL> create table tab_part3 as select object_id,object_name from dba_objects where object_id >= 60000;
Table created.
SQL> select count(*) from tab_part3;
COUNT(*)
----------
23257
2、建好分区表来对应几个普通的表
SQL> create table p_table (object_id number,object_name varchar2(120))
2 partition by range(object_id)
3 (partition part1 values less than (30000),
4 partition part2 values less than (60000),
5 partition part3 values less than (90000),
6 partition part4 values less than (maxvalue));
Table created.
如果是日期型的,那么建分区表的时候就得如下:
SQL> create table x_mugua (credate date,person_id number,names varchar2(10))
2 partition by range(credate)
3 (partition part1 values less than to_date('20090101','yyyymmdd'),
4 partition part2 values less than to_date('20100101','yyyymmdd'),
5 partition part3 values less than to_date('20110101','yyyymmdd'),
6 partition part4 values less than (maxvalue));
3、把普通表的数据转入到分区表中
SQL> alter table p_table exchange partition part1 with table tab_part1;
alter table p_table exchange partition part1 with table tab_part1
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
--这里说字段的值大小不相符,改一下就行了。
SQL> desc tab_part1;
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NUMBER
OBJECT_NAME VARCHAR2(12
SQL> alter table p_table modify object_name varchar2(12 ;
Table altered.
SQL> alter table p_table exchange partition part1 with table tab_part1;
Table altered.
SQL> alter table p_table exchange partition part2 with table tab_part2;
Table altered.
SQL> alter table p_table exchange partition part3 with table tab_part3;
Table altered.
4、速度相当的快,验证一下数据
SQL> select count(*) from p_table;
COUNT(*)
----------
81757
SQL> select count(*) from p_table partition(part1);
COUNT(*)
----------
29191
SQL> select count(*) from p_table partition(part2);
COUNT(*)
----------
29309
SQL> select count(*) from p_table partition(part3);
COUNT(*)
----------
23257
再看看原表的数据:
SQL> select count(*) from tab_part1;
COUNT(*)
----------
0
SQL> select count(*) from tab_part2;
COUNT(*)
----------
0
SQL> select count(*) from tab_part3;
COUNT(*)
----------
0
说明数据已经转移过去了!
exchange是通过修改数据字典实现的,不会产生什么日志,速度是相当的快速!以前我们在一家通信公司做的时候,每个月要转出的记录数有几亿条,都是通过这种方式进行的!
分区转出,以及分区转分区,表转表,表转分区,分区转表都非常好用!
以下是我写给他的测试步骤:
1、首先建几个表来测试一下
SQL> create table tab_part1 as select object_id,object_name from dba_objects where object_id < 30000;
Table created.
SQL> select count(*) from tab_part1;
COUNT(*)
----------
29191
SQL> create table tab_part2 as select object_id,object_name from dba_objects where object_id < 60000 and object_id >=30000;
Table created.
SQL> select count(*) from tab_part2;
COUNT(*)
----------
29309
SQL> create table tab_part3 as select object_id,object_name from dba_objects where object_id >= 60000;
Table created.
SQL> select count(*) from tab_part3;
COUNT(*)
----------
23257
2、建好分区表来对应几个普通的表
SQL> create table p_table (object_id number,object_name varchar2(120))
2 partition by range(object_id)
3 (partition part1 values less than (30000),
4 partition part2 values less than (60000),
5 partition part3 values less than (90000),
6 partition part4 values less than (maxvalue));
Table created.
如果是日期型的,那么建分区表的时候就得如下:
SQL> create table x_mugua (credate date,person_id number,names varchar2(10))
2 partition by range(credate)
3 (partition part1 values less than to_date('20090101','yyyymmdd'),
4 partition part2 values less than to_date('20100101','yyyymmdd'),
5 partition part3 values less than to_date('20110101','yyyymmdd'),
6 partition part4 values less than (maxvalue));
3、把普通表的数据转入到分区表中
SQL> alter table p_table exchange partition part1 with table tab_part1;
alter table p_table exchange partition part1 with table tab_part1
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
--这里说字段的值大小不相符,改一下就行了。
SQL> desc tab_part1;
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NUMBER
OBJECT_NAME VARCHAR2(12
SQL> alter table p_table modify object_name varchar2(12 ;
Table altered.
SQL> alter table p_table exchange partition part1 with table tab_part1;
Table altered.
SQL> alter table p_table exchange partition part2 with table tab_part2;
Table altered.
SQL> alter table p_table exchange partition part3 with table tab_part3;
Table altered.
4、速度相当的快,验证一下数据
SQL> select count(*) from p_table;
COUNT(*)
----------
81757
SQL> select count(*) from p_table partition(part1);
COUNT(*)
----------
29191
SQL> select count(*) from p_table partition(part2);
COUNT(*)
----------
29309
SQL> select count(*) from p_table partition(part3);
COUNT(*)
----------
23257
再看看原表的数据:
SQL> select count(*) from tab_part1;
COUNT(*)
----------
0
SQL> select count(*) from tab_part2;
COUNT(*)
----------
0
SQL> select count(*) from tab_part3;
COUNT(*)
----------
0
说明数据已经转移过去了!
exchange是通过修改数据字典实现的,不会产生什么日志,速度是相当的快速!以前我们在一家通信公司做的时候,每个月要转出的记录数有几亿条,都是通过这种方式进行的!
分区转出,以及分区转分区,表转表,表转分区,分区转表都非常好用!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29371470/viewspace-1062686/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29371470/viewspace-1062686/