ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 往表中插入大量数据的方法(一)

往表中插入大量数据的方法(一)

原创 Linux操作系统 作者:pingley 时间:2012-05-10 21:45:54 0 删除 编辑
往表中插入大量数据的方法(一)
下面这个表是根据all_objects 表的结构构造的。用来模拟大量的数据。
SQL> select count(*) from allobjects;
  COUNT(*)
----------
   3375996
Elapsed: 00:00:07.71
创建两张与allobjcts 结构相同的表。
SQL> edit
Wrote file afiedt.buf
  1  create table alltest01 as select * from allobjects
  2* where 1=2
SQL> /
Table created.
Elapsed: 00:00:00.06
SQL> edit
Wrote file afiedt.buf
  1  create table alltest02 as select * from allobjects
  2* where 1=2
SQL> /
Table created.
Elapsed: 00:00:00.08
使用传统的insert 语句往alltest01 插入大量的数据。
SQL> insert into alltest01 select * from allobjects;
3375996 rows created.
Elapsed: 00:01:12.60
使用insert append 方式往alltest02 插入大量的数据。
SQL> insert /*+ append */ into alltest02 select * from allobjects;
3375996 rows created.
Elapsed: 00:00:53.78
通过上述的两条insert 语句我们可以明显的发现性能上面的差别。传统的
insert 方式比insert append 方式慢了19秒左右,两者不在同一个数量级上。
貌似insert append 方式在插入数据方面很有性能优势。但是除了性能方面
的优势,存在以下的一些限制:
一、在commit或者rollback前当前不能对表进行查询,和执行DML语句。
其他会话中可以进行查询,但是看到的是insert append 以前的数据,并且
其他会话中执行的DML 语句会被阻塞。另外一个时间点只能有一个会话对
指定的表发起insert append.
SQL> select count(*) from alltest02;
select count(*) from alltest02
                     *
ERROR at line 1:
ORA-12838: 无法在并行模式下修改之后读/修改对象
Elapsed: 00:00:00.04
SQL> delete from alltest02;
delete from alltest02
            *
ERROR at line 1:
ORA-12838: 无法在并行模式下修改之后读/修改对象
Elapsed: 00:00:00.02
SQL> update alltest02
  2  set object_id = 1
  3  /
update alltest02
       *
ERROR at line 1:
ORA-12838: 无法在并行模式下修改之后读/修改对象
Elapsed: 00:00:00.01
SQL> insert into alltest02 select * from allobjects;
insert into alltest02 select * from allobjects
            *
ERROR at line 1:
ORA-12838: 无法在并行模式下修改之后读/修改对象
Elapsed: 00:00:00.52
在另一个会话中执行select 语句。查询结果是表中记录为零,因为这个会话
看到的是insert append 以前的数据。
SQL> select count(*) from alltest02;
  COUNT(*)
----------
         0
二、使用insert append 方式插入数据的时候,将会使用HWM之上的空白块,
如果要插入的表中HWM以下有很多没有使用的空间的话,将会导致
存储空间的浪费,所以说小数据量的插入没有必要使用insert append 方式,
插入的表最好空的。有一种说法是如果要插入的表格是空白的,或者已经被
truncate 过,使用传统的insert 和insert append 插入数据的方式hwm 是相同的
但是我个人认为是不相同的insert append 方式还是需要消耗更多的存储空间。
因为在insert append 方式下hwm 推进的时候,总有一部分空间不能够利用。
SQL>  analyze table alltest01 estimate statistics;
Table analyzed.
Elapsed: 00:00:11.71
SQL> column segment_name format a20
SQL>  select segment_name,blocks,bytes/1024 as "Size[KB]"
  2   from user_segments
  3   where segment_name = 'ALLTEST01';
SEGMENT_NAME             BLOCKS   Size[KB]
-------------------- ---------- ----------
ALLTEST01                 48128     385024
Elapsed: 00:00:00.07
SQL> select empty_blocks
  2  from user_tables
  3  where table_name = 'ALLTEST01';
EMPTY_BLOCKS
------------
         179
Elapsed: 00:00:00.12
SQL> analyze table alltest02 estimate statistics;
Table analyzed.
Elapsed: 00:00:09.36
SQL> select segment_name,blocks,bytes/1024 as "Size[KB]"
  2  from user_segments
  3  where segment_name = 'ALLTEST02';
SEGMENT_NAME             BLOCKS   Size[KB]
-------------------- ---------- ----------
ALLTEST02                 49152     393216
Elapsed: 00:00:00.01
SQL>  select empty_blocks
  2   from user_tables
  3   where table_name = 'ALLTEST02';
EMPTY_BLOCKS
------------
        1012
Elapsed: 00:00:00.00
alltest01 的hwm = 48128  - 179 - 1 =47948
alltest02 的 hwm = 49152  - 1012 -1 = 48139
可见使用insert append 方式插入数据的表alltest02 需要更多的存储空间。
三、如果插入的表中存在外键约束,将会使用传统的insert 方式。
下面先来构造一个外键存在的情况。
SQL> create table emp_01 as select employee_id,first_name,last_name
  2  from hr.employees;
Table created.
Elapsed: 00:00:00.12
SQL> alter table emp_01 add constraint emp_01_pk primary key (employee_id);
Table altered.
Elapsed: 00:00:00.59
SQL> create table emp_02 as select employee_id,department_id,salary
  2  from hr.employees;
Table created.
Elapsed: 00:00:00.08
SQL> alter table emp_02 add constraint emp_02_fk foreign key (employee_id)
  2  references emp_01 (employee_id);
Table altered.
Elapsed: 00:00:00.52
使用insert append 方式来插入数据,结果插入完成以后可以查询,说明有外键存在的
情况下insert append 方式将会自动转化为传统的insert 方式。如果在这种情况下确实
想要使用isnert append 方式,可以先disable constraint。
SQL> insert /*+append*/ into emp_02 select employee_id,department_id,salary
  2  from hr.employees;
107 rows created.
Elapsed: 00:00:00.12
SQL> select count(*) from emp_02;
  COUNT(*)
----------
       214
Elapsed: 00:00:00.07
四、并不是所以的表都可以使用insert append 方式插入数据,比如不支持对象类型,
堆组织表等。
小结:使用insert append 虽然可以带来插入数据性能上面的优势,但是也存在诸多的
缺点和限制,需要综合全面考虑。建议在插入大数据量和刚创建的表或者truncate 以后
的表中使用。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26110315/viewspace-723285/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 关于yum lock
请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    169
  • 访问量
    715180