上个星期,一个朋友问我,同一个实例上,将某张表从A用户下移动到B用户。表有1亿+条记录。表很大。当时说的方法的是用create table name as select * from B .tbname.
因为记录比较多,也不知道用这种方法的效率怎么样。之前本本上有2个dump的测试库。上次一不小心删了一个dump文件。现在就剩一个。刚才导入了一下。最大的分区表记录1200w.数据量没有朋友的那个生产库大。不过可以测试一下。
这里测试的同一个实例下的。如果是不同实例间,可以采用数据泵+并行,这样速度也会很快。具体参考:
Oracle插入大量数据
http://blog.csdn.net/xujinyang/article/details/6829903
exp/imp与expdp/impdp对比及使用中的一些优化事项
http://blog.csdn.net/xujinyang/article/details/6831324
先看表的信息:
SQL> select sum(bytes)/1024/1024/1024 "GB" from dba_extents where SEGMENT_NAME='TAGENTOPRINFO' and owner='ICD';
GB
----------
1.03125
--单张分区表1G
SQL> select count(*) from TAGENTOPRINFO;
COUNT(*)
----------
12172500
--表中记录1200w
方法一:create table as select * from
SQL> set timing on
SQL> create table ta as select * from TAGENTOPRINFO;
表已创建。
已用时间:00: 01: 04.01
SQL> select count(*) from ta;
COUNT(*)
----------
12172500
已用时间:00: 00: 21.11
SQL>
注意:新建的表没有原来表的索引和默认值,只有非空(not null)的约束素条件可以继承过来,其它的约束条件或索引需要重新建立.如果原始表是分区表,采用这种方式创建的是非分区表。
方法二:insert /*+APPEND */ into tbname select * from
--先创建表结构
SQL> create table tb as select * from TAGENTOPRINFO where 1=0;
表已创建。
已用时间:00: 00: 00.05
SQL> select count(*) from tb;
COUNT(*)
----------
0
已用时间:00: 00: 00.00
--insert data
SQL> insert /*+APPEND */ into tb select * from TAGENTOPRINFO;
已创建12172500行。
已用时间:00: 00: 55.43
SQL> commit;
提交完成。
已用时间:00: 00: 06.57
在这里再尝试用nologing模式,看看这次需要多长时间:
SQL> alter table tb nologging;
表已更改。
已用时间:00: 00: 00.14
SQL> insert /*+APPEND */ into tb select * from TAGENTOPRINFO;
已创建12172500行。
已用时间:00: 01: 13.78
SQL> commit;
提交完成。
已用时间:00: 00: 06.57
SQL> alter table tb logging;
表已更改。
已用时间:00: 00: 00.58
注意:
用INSERT /*+ APPEND */的方法会对目标表(这里的TB)产生级别为6的独占锁,如果运行此命令时还有对TB的DML操作会排队在它后面。
通过上面2个测试,时间都差不多。我还是在我的本本上测试的。硬盘的读写能力比服务器硬盘还要差很多。所以,如果在服务器上进行这种操作,还应该会快一点。
------------------------------------------------------------------------------