往表中插入大量的数据的方法（二）

往表中插入大量的数据的方法（二）

虽然可以使用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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26110315/viewspace-729967/