Oracle高效插入大量数据

前端之家收集整理的这篇文章主要介绍了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。

原文链接:https://www.f2er.com/oracle/208431.html

猜你在找的Oracle相关文章