mysql insert append_insert append需要注意的一个小问题

1. append方式添加记录对insert into ... values语句不起作用。

2. 以append方式批量插入的记录，其存储位置在hwm 之上，即使hwm之下存在空闲块也不能使用。

3. 以append方式插入记录后，要执行commit，才能对表进行查询。否则会出现错误：

ORA-12838: 无法在并行模式下修改之后读/修改对象

4. 在归档模式下，要把表设置为nologging，然后以append方式批量添加记录，才会显著减少redo数量。在非归档模式下，不必设置表的nologging属性，即可减少redo数量。如果表上有索引，则append方式批量添加记录，不会减少索引上产生的redo数量，索引上的redo数量可能比表的redo数量还要大。

用insert append可以实现直接路径加载，速度比常规加载方式快。但有一点需要注意: insert append时在表上加“6”类型的锁，会阻塞表上的所有DML语句。因此在有业务运行的情况下要慎重使用。若同时执行多个insert append对同一个表并行加载数据，并不一定会提高速度。因为每一时刻只能有一个进程在加载(排它锁造成)。

SQL> create table test as select * from dba_objects where 1=2;

表已创建。

SQL> insert into test select * from dba_objects;

已创建11344行。

SQL> set lines 150

SQL> col object_type for a10

SQL> col object for a30

SQL> col username for a10

SQL> col osuser for a10

SQL> col program for a30

SQL> col sid for 99,999

SQL> col locked_mode for 99

SQL> col spid for 999,999

SQL> select o.object_type,o.owner||'.'||o.object_name object,s.sid,s.serial#,p.spid,s.username,s.osuser,s.program,l.lock

ed_mode

2  from v$locked_object l,dba_objects o,v$session s,v$process p

3  where l.object_id = o.object_id

4  and s.sid=l.session_id

5  and s.paddr=p.addr

6  and o.object_name = upper('&obj');

输入 obj 的值:  test

原值    6: and o.object_name = upper('&obj')

新值    6: and o.object_name = upper('test')

OBJECT_TYP OBJECT                             SID    SERIAL# SPID         USERNAME   OSUSER     PROGRAM

LOCKED_MODE

---------- ------------------------------ ------- ---------- ------------ ---------- ---------- ------------------------

------ -----------

TABLE      SYS.TEST                           147         19 784          SYS        CNPEKALT02 sqlplus.exe

3

2\jyu

可以看到,insert 时在表上加的是“3”类型的锁。

SQL> rollback;

回退已完成。

SQL> insert /*+ append */ into test select * from dba_objects;

已创建11344行。

SQL> set lines 150

SQL> col object_type for a10

SQL> col object for a30

SQL> col username for a10

SQL> col osuser for a10

SQL> col program for a30

SQL> col sid for 99,999

SQL> col locked_mode for 99

SQL> col spid for 999,999

SQL> select o.object_type,o.owner||'.'||o.object_name object,s.sid,s.serial#,p.spid,s.username,s.osuser,s.program,l.lock

ed_mode

2  from v$locked_object l,dba_objects o,v$session s,v$process p

3  where l.object_id = o.object_id

4  and s.sid=l.session_id

5  and s.paddr=p.addr

6  and o.object_name = upper('&obj');

输入 obj 的值:  test

原值    6: and o.object_name = upper('&obj')

新值    6: and o.object_name = upper('test')

OBJECT_TYP OBJECT                             SID    SERIAL# SPID         USERNAME   OSUSER     PROGRAM

LOCKED_MODE

---------- ------------------------------ ------- ---------- ------------ ---------- ---------- ------------------------

------ -----------

TABLE      SYS.TEST                           147         19 784          SYS        CNPEKALT02 sqlplus.exe

6

2\jyu

而执行insert append时在表上加的是“6”类型的锁。

insert append与一般的insert在表上加的锁不一样。insert append加的是exclusive的锁。因此要注意在执行insert append尽快提交，否则会阻塞其它事务对同一张表的DML语句。

此外， ORA-12838:是由于在执行insert append之后没有提交或回滚，接着又执行DML语句造成的。解决办法是在insert append 之后加上commit或rollback。

请看下面的测试：

SQL> delete from test;

已删除9831行。

SQL> insert /*+ append */ into test select * from temp_fsum_od;

已创建3277行。

SQL> insert into test select * from temp_fsum_od;

insert into test select * from temp_fsum_od

*

第 1 行出现错误:

ORA-12838: 无法在并行模式下修改之后读/修改对象

SQL> update test set OD_CODE=upper(OD_CODE) ;

update test set OD_CODE=upper(OD_CODE)

*

第 1 行出现错误:

ORA-12838: 无法在并行模式下修改之后读/修改对象

SQL> delete from test;

delete from test

*

第 1 行出现错误:

ORA-12838: 无法在并行模式下修改之后读/修改对象

SQL>

SQL> insert /*+ append */ into test select * from temp_fsum_od;

insert /*+ append */ into test select * from temp_fsum_od

*

第 1 行出现错误:

ORA-12838: 无法在并行模式下修改之后读/修改对象

注意，我先执行了一个delete语句，又执行了insert append. 这个delete语句并没有造成后面的insert append报错。

但在我执行了一个insert append之后，再执行任何DML语句都会报错。

这说明，在执行了insert append 之后，必须commit或rollback，才能再执行其它DML语句。

但在insert append之前可以执行DML语句，而不会对insert append造成影响。

所以我们注意一点就可以了，即只要业务允许，在执行insert append后马上提交。