Oracle高效插入大量数据
|
需求：数据迁移，从远端数据库查询数据写入到生产库中。 遇到问题，数据量为千万级别（具体是6千多万条记录的数据），直接使用insert into 目标表 select * from 数据源表。 因为目标表本身有上千万数据，且有主键和索引，导致写表非常慢。 执行了5,6个小时还没跑完，第二天看索性plsqldev已经卡死了。 只能强行杀掉了plsqldev的进程。 再看看数据，一条都没写进去，有点崩溃。。。 网上查找了高效插入大量数据的方法，原文地址http://www.cnblogs.com/quanweiru/p/5325635.html 写的很好，很实用。 在测试库测试了下，使用分区表，插入数据到底能快多少。 前提：测试库环境下，表都是没有主键和索引的。 step1,在测试库新建了一个分区表 create table AC83_P ( aaz219 NUMBER(16) not null,aaz220 NUMBER(16),aaa027 VARCHAR2(12),aac001 NUMBER(20),aaa036 VARCHAR2(6),aaa038 VARCHAR2(3),aaa085 VARCHAR2(1),aaa088 VARCHAR2(1),aae140 VARCHAR2(6),aae002 NUMBER(6),aae003 NUMBER(6),aae019 NUMBER(16,2),bae134 NUMBER(16,aae013 VARCHAR2(150),baz057 NUMBER(16),baa018 NUMBER(20) not null,bad709 VARCHAR2(20),bae023 VARCHAR2(9),bad305 VARCHAR2(20) ) partition by hash(aaz219)( partition part01 tablespace data01,partition part02 tablespace data01,partition part03 tablespace data01,partition part04 tablespace data01,partition part05 tablespace data01,partition part06 tablespace data01,partition part07 tablespace data01,partition part08 tablespace data01 ); step2,将表 ac83 （66,325,831条记录）写入到分区表。 alter table ac83 nologging; insert /*+ append */ into ac83_p select * from ac83;--2分钟 alter table ac83 logging; commit; 结果吓了我一跳，这也 太！快！了！ 吧！。2分钟，更准确是116秒完成了整个写入。 顿时有种喜极而泣的感觉。似乎看到了胜利的曙光 ps: +append 只适用于 串行的情况，并行会产生enqueue。 --=========实验结果很好！=========== ==>>但问题来了： 1）迁移到目标库， 目标库的表本身有记录，且有主键和索引，都不能删的。 2）目标库的表不是分区表。 ==>>新思路： 根据博文提示，可以适用并的方式写入。数据源表 做分区。对每个分区分别insert到目标库。 然而，执行分区写入的时候，报错了 SELECT * FROM AC83_p@dblink_zhdata partition(part01); ORA-14100: 分区扩展表名不能指远程对象 分析出错原因： 原文地址http://blog.csdn.net/annicybc/article/details/852561 测试发现虽然通过建立远端对象同义词的方式可以使用PARTITION语句，但是PARTITION语句并没有起任何作用。而且在最后的查询中，指定了一个不存在的分区，但是并没有报错，说明Oracle忽略了PARTITION语句。 说明oracle还是不能通过数据库链进行PARTITION相关的操作，但是如果对同义词采用这种方式的查询，则Oracle没有进行相应的判断，而仅仅是忽略分区语句。 很遗憾，不能通过数据源表 改为分区表，分别insert的办法进行并行写入到目标库。 --============方法2：将分区表按分区拆成几个普通表，并行写入到目标库======= step1,按分区进行拆分表，新建分表 create table ac83_p1 as SELECT * FROM ac83_p partition(part01); create table ac83_p2 as SELECT * FROM ac83_p partition(part02); create table ac83_p3 as SELECT * FROM ac83_p partition(part03); create table ac83_p4 as SELECT * FROM ac83_p partition(part04); create table ac83_p5 as SELECT * FROM ac83_p partition(part05); create table ac83_p6 as SELECT * FROM ac83_p partition(part06); create table ac83_p7 as SELECT * FROM ac83_p partition(part07); create table ac83_p8 as SELECT * FROM ac83_p partition(part08); 6千多万数据，分配到8张表中，每个表有8百多万记录。 step2,尝试单独写入一个分表。
alter table ac83 nologging; INSERT /*+ append */ INTO AC83 SELECT * FROM AC83_p1@dblink_zhdata; commit; alter table ac83 logging; 8百多万的数据，看需要多上时间，在写入表有主键和索引的情况下，从远端写入需要多长时间。 13分钟过去了，还没insert完。预估它需要1个小时。静待结果 执行完了， 结果是 1488 s，约24分钟。比预估的还好。 但并行insert同一个表时，不能用/*+ append*/，会产生enqueue。 执行剩下的 7个分表 写入过程中，提示空间不足了。 增加了表空间，写入前为 31G --insert 前
SELECT a.tablespace_name,sum(bytes)/1024/1024 FROM dba_free_space a WHERE a.tablespace_name in ('GDYLSY_INDEX','GDYLSY_DATA') group by a.tablespace_name;
SELECT 34352398336/1024/1024/1024 FROM dual;--31G
GDYLSY_INDEX;--2554.56mb
GDYLSY_DATA;--32069.56mb--31.31G
测试下 6千万条记录，占用表空间大概多少？ insert 完成查看剩余表空间 GDYLSY_DATA;--26949.56mb--26.31G GDYLSY_INDEX;--604.125mb --索引花掉 1950mb --1.9G --数据记录花掉 5120mb --5G 6千万的数据，占用的空间大致是6-7G。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |