ITPub博客

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

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

原创 Linux操作系统 作者:pingley 时间:2012-05-16 22:58:04 0 删除 编辑
往表中插入大量的数据的方法(二)
虽然可以使用insert append 的方式来往一张表中插入大量的数据,但是
毕竟存在诸多的限制和缺陷,下面讨论另外一种方式。先创建一张测试表。
并添加主键约束。
SQL> ed
Wrote file afiedt.buf
  1  create table t_insert as select object_id,object_name
  2  from dba_objects
  3* where 1 = 2
SQL> /
Table created.
SQL> ed
Wrote file afiedt.buf
  1* alter table t_insert add constraint t_insert_pk primary key(object_id)
SQL> /
Table altered.
尝试往测试表中插入较大量的数据,为了节省时间我们把插入的数据量设置为10W。加大数据量
对比的结果会更加的明显。
SQL> set timing on
SQL> ed
Wrote file afiedt.buf
  1   begin
  2     for i in 1..100000
  3     loop
  4       insert into t_insert values(i,rpad('A',100,'A'));
  5     end loop;
  6*  end;
  7  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:14.40
SQL> rollback;
Rollback complete.
Elapsed: 00:00:06.41
下面把主键约束disable,在并查询user_indexes 表。也许你还没有反应过来
为什么要查询user_indexes 表,待会就明白了。
SQL> alter table t_insert disable constraint t_insert_pk;
Table altered.
Elapsed: 00:00:00.32
SQL> select index_name,status
  2  from user_indexes
  3  where table_name = 'T_INSERT';
no rows selected
Elapsed: 00:00:00.21
尝试往测试表中插入相同的数据库,并比对两次插入的用时。可以发现当我们把
主键约束给disable 以后,insert语句执行的效率提高了1倍多,这是非常可观的
特别是当数据量很大的时候。
SQL> ed
Wrote file afiedt.buf
  1  begin
  2    for i in 1..100000
  3    loop
  4      insert into t_insert values(i,rpad('A',100,'A'));
  5    end loop;
  6* end;
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.30
SQL> rollback;
Rollback complete.
或许我们还可以把这种方式和insert append的方式结合起来一起使用。下面
我们来进行这个实验吧。这次的插入用时更少,但是改善的并不明显,因为
数据量还不够大。我们在插入前,disable 了表中的pk 约束,现在插入完成了
我们应该enable。
SQL> ed
Wrote file afiedt.buf
  1  begin
  2    for i in 1..100000
  3    loop
  4      insert /*+append*/into t_insert values(i,rpad('A',100,'A'));
  5    end loop;
  6* end;
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.52
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> alter table t_insert enable constraint t_insert_pk;
Table altered.
Elapsed: 00:00:00.46
做下面这个查询,我想你已经明白为什么前面要做相同的查询了。
因为oracle 会在主键上面自动的创建唯一索引,可见当我们disable
主键约束的时候,相应的把索引给删除了。本质上是因为没有所以
所以上面的insert 语句执行起来才会那么飘逸。
SQL> select index_name,status
  2  from user_indexes
  3  where table_name = 'T_INSERT';
INDEX_NAME                                                   STATUS
------------------------------------------------------------ ----------------
T_INSERT_PK                                                  VALID
Elapsed: 00:00:00.00
你可以使用下面的小脚本来获得表中的索引,并unusable 以免影响insert插入的性能。
下面是一个在HR 模式下的一个示例,当插入完成以后,执行相应的rebulid 语句来使
索引生效。另外一种可选的方式是先将待插入数据的表中的index drop 掉,以后使用
相应的sql 语句重建,不过在你这样做之前,确保你得到了重建索引需要的脚本。
select 'alter index ' || index_name || ' unusable;'
from user_indexes where table_name=upper('&table_name');
SQL> ed
Wrote file afiedt.buf
  1  select 'alter index ' || index_name || 'unusable;'
  2* from user_indexes where table_name=upper('&table_name')
SQL> /
Enter value for table_name: employees
old   2: from user_indexes where table_name=upper('&table_name')
new   2: from user_indexes where table_name=upper('employees')
'ALTERINDEX'||INDEX_NAME||'UNUSABLE;'
-----------------------------------------------------------------
alter index EMP_EMAIL_UK unusable;
alter index EMP_EMP_ID_PK unusable;
select 'alter index ' || index_name || ' rebuild;'
from user_indexes where table_name=upper('&table_name');
由刚才的primary key ,index 脑中飘过一个词约束。。。约束会对insert 语句有影响吗?
我们来尝试下,眼见为实。除了primary key 和 unique  约束oracle 会自动的为这些列创建
index(并不是总是会创建,也存在例外的),其他约束的列不会隐式的为其创建索引。
SQL> drop table t_insert;
Table dropped.
SQL> create table t_insert (x int check (x >= 100));
Table created.
SQL> create table t_insert1 (x int);
Table created.
SQL>  select index_name from user_indexes
  2   where table_name in ('T_INSERT','T_INSERT1');
no rows selected
SQL> ed
Wrote file afiedt.buf
  1  begin
  2    for i in 110..300000
  3    loop
  4      insert into t_insert values(i);
  5    end loop;
  6* end;
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:40.18
SQL> ed
Wrote file afiedt.buf
  1  begin
  2    for i in 110..300000
  3    loop
  4      insert into t_insert1 values(i);
  5    end loop;
  6* end;
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:36.17
通过上面的对比我们可以知道约束会影响insert 语句,因为数据库要花费资源来
检查插入的数据是否符合表中的约束的定义。
小结:在这里我们已经讨论了索引对insert语句的影响,对此如果需要插入大量的数据
可以把索引unusable,插入完以后rebuild。并知道表中的约束也会影响insert语句的
性能,但是确定要使用上述的方式insert大量数据的时候,你必须清楚你要插入的数据
是怎样的,有没有违反表中的约束的定义。不应该只追求性能,而不顾数据的正确性。

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

请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    169
  • 访问量
    717506