批量插入資料時主鍵衝突的處理

selectshen發表於2017-12-31
客戶有這樣一個需求：有一個大表A，有主鍵，經常需要往這個表中批量插入大量資料，但插入的資料可能自身重複或者跟表A重複。
下面測試幾種插入的方法：

DB:ORACLE 11.2.0.4

新建測試表：
新建表scott.tb_01
create table scott.tb_01
as
SELECT level c1,level c2,level c3
FROM DUAL
CONNECT BY LEVEL <= 100000;

在表scott.tb_01上增加主鍵
create unique index scott.pk_tb_01 on scott.tb_01(c1); --這一步不是必需的，因為下一步會自動建索引，但這種建索引再加主鍵的方式可以在建索引的時候加並行。
alter table scott.tb_01 add constraint pk_tb_01 primary key (c1) using index;

新建表scott.tb_02
create table scott.tb_02
as
SELECT level+100000 c1,level c2,level c3
FROM DUAL
CONNECT BY LEVEL <= 100000;

在表scott.tb_02上插入表scott.tb_01的100條資料做為重複資料
insert into scott.tb_02
SELECT c1,c2, c3
FROM scott.tb_01
where rownum <= 100;

commit;

現在需要將表scott.tb_02中和表scott.tb_01非重複的資料插入到scott.tb_01
方法1：關聯插入
INSERT INTO SCOTT.TB_01
SELECT A.* --這裡如果表SCOTT.TB_02自身有重複資料，還要加上distinct
FROM SCOTT.TB_02 A
LEFT JOIN SCOTT.TB_01 B
ON A.c1 = B.c2
WHERE B.c1 IS NULL;

100000 rows created.
上面一種常見的插入方法，這個方法的的問題在於，如果表SCOTT.TB_02和表SCOTT.TB_01都很大，兩個表關聯查詢的成本會很高，影響效能。

方法2：使用HINT:IGNORE_ROW_ON_DUPKEY_INDEX
不使用HINT:
INSERT
INTO SCOTT.TB_01
SELECT * FROM SCOTT.TB_02 ;

ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_TB_01) violated
從上面可以看出直接將表SCOTT.TB_02插入到表SCOTT.TB_01會出現主鍵衝突，報ORA-00001錯誤。

使用HINT:
INSERT /*+IGNORE_ROW_ON_DUPKEY_INDEX(a PK_tb_01)*/
INTO SCOTT.TB_01 A
SELECT * FROM SCOTT.TB_02 ;

100000 rows created.
從上面可以看出，加上HINT後，表SCOTT.TB_02中和表SCOTT.TB_01沒有主鍵衝突的記錄插入到表SCOTT.TB_01中，衝突的100條記錄沒有插入，也沒有報錯。

方法3：使用impdp的skip_constraint_errors選項
建立dump目錄
create directory dump_home as '/home/oracle';

匯出表scott.tb_02
expdp system tables=scott.tb_02 directory=dump_home dumpfile=expdp_tb_02.dmp logfile=expdp_tb_02.log
使用expdp匯出表scott.tb_02，用於後續匯入到表scott.tb_01中。

匯入表scott.tb_02到scott.tb_01，不加skip_constraint_errors選項
impdp system tables=scott.tb_02 remap_table=scott.tb_02:tb_01 content=data_only directory=dump_home dumpfile=expdp_tb_02.dmp logfile=impdp_tb_02.log

Import: Release 11.2.0.4.0 - Production on Tue Dec 5 23:18:49 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** tables=scott.tb_02 remap_table=scott.tb_02:tb_01 content=data_only directory=dump_home dumpfile=expdp_tb_02.dmp logfile=impdp_tb_02.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "SCOTT"."TB_01" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (SCOTT.PK_TB_01) violated
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Tue Dec 5 23:19:02 2017 elapsed 0 00:00:09
從上面可以看出，如果不加skip_constraint_errors選項就會報主鍵衝突錯誤，匯入失敗。

匯入表scott.tb_02到scott.tb_01，加skip_constraint_errors選項
impdp system tables=scott.tb_02 remap_table=scott.tb_02:tb_01 content=data_only directory=dump_home dumpfile=expdp_tb_02.dmp logfile=impdp_tb_02.log data_options=skip_constraint_errors

Import: Release 11.2.0.4.0 - Production on Tue Dec 5 23:21:29 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** tables=scott.tb_02 remap_table=scott.tb_02:tb_01 content=data_only directory=dump_home dumpfile=expdp_tb_02.dmp logfile=impdp_tb_02.log data_options=skip_constraint_errors
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."TB_01" 1.796 MB 100000 out of 100100 rows
100 row(s) were rejected with the following error:
ORA-00001: unique constraint (SCOTT.PK_TB_01) violated

Rejected rows with the primary keys are:
Rejected row #1:
column C1: 1
…
Rejected row #99:
column C1: 99
Rejected row #100:
column C1: 100
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Dec 5 23:21:56 2017 elapsed 0 00:00:23
從上面可以看出，匯入順利完成，並跳過了主鍵衝突的行，並在impdp的log中顯示行的資訊。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-2149527/，如需轉載，請註明出處，否則將追究法律責任。

相關文章