imp -- commit array insert

Posted by Vincent on May 25, 2009

在给数据库用户做逻辑迁移,imp的时候涉及 commit array insert 这个参数,第一次使用了 commit=y,发现速度相当滴慢,这个参数默认是 N的。

文档上说:
COMMIT

Default: n

Specifies whether Import should commit after each array insert. By default, Import commits only after loading each table, and Import performs a rollback when an error occurs, before continuing with the next object.

If a table has nested table columns or attributes, the contents of the nested tables are imported as separate tables. Therefore, the contents of the nested tables are always committed in a transaction distinct from the transaction used to commit the outer table.

If COMMIT=n and a table is partitioned, each partition and subpartition in the Export file is imported in a separate transaction.

Specifying COMMIT=y prevents rollback segments from growing inordinately large and improves the performance of large imports. Specifying COMMIT=y is advisable if the table has a uniqueness constraint. If the import is restarted, any rows that have already been imported are rejected with a recoverable error.

If a table does not have a uniqueness constraint, Import could produce duplicate rows when you reimport the data.

For tables containing LONG, LOB, BFILE, REF, ROWID, UROWID, or DATE columns, array inserts are not done. If COMMIT=y, Import commits these tables after each row.

n: default ,以表为单位commit;
y: 以buffer为单位commit,
如果表含有LONG, LOB, BFILE, REF, ROWID, UROWID,   DATE类型字段 , 以行为单位

ITPUB上有一个讨论的帖子
http://www.itpub.net/thread-530380-1-1.html


This work is licensed under a CC A-S 4.0 International License.