자주 하는 작업 중 하나는 insert… as select… 문을 이용하여 다량의 데이터를 select 후 insert하는 일이다. 이 경우 가끔 키 값이 중복되어 오류가 발생 할 수 있는데 이 경우 insert문은 롤백 된다. 오라클 10g의 New Feature로 소개되었던 DML Error Logging을 이용하여 오류가 난 레코드를 별도의 로깅 테이블에 기록하고 에러 때문에 DML이 중지되는 것을 막을 수 있지만 사용하기에 번거롭다.
10g의 DML Error Logging은 아래 URL에서 확인하자.
http://oraclejavanew.kr/bbs/board.php?bo_table=LecOracle&wr_id=187&sca=&sfl=wr_subject%7C%7Cwr_content&stx=error+logging&sop=and
Oracle 11g에서 새로 소개된 ignore_row_on_dupkey_index 힌트를 사용하면 쉽게 해결할 수 있는데, 데이터 키값이 중복되는 경우 오류를 발생시키지 않고 중복되지 않는 데이터만 입력할 수 있게 해준다.
아쉬운 점이라면 SINGLE INSERT문만 지원하고 UPDATE, DELETE, MERGE, MULTI INSERT 구문에서는 안 된다는 것이며 APPEND, PARALEL 힌트와 같이 사용하면 이 두 힌트가 무시되며 Direct Path Load는 지원하지 않는다는 것이다.
[예제]
create table onj (
id number primary key,
name varchar2(20)
)
insert into onj values (1, '1길동');
insert into onj values (2, '2길동');
insert into onj values (3, '3길동');
commit
select * from onj;
1 1길동
2 2길동
3 3길동
create table onj_old (
id number primary key,
name varchar2(20)
)
insert into onj_old values (1, '1길동');
insert into onj_old values (4, '4길동');
insert into onj_old values (5, '5길동');
commit
-- onj_old의 내용을 onj 테이블로 insert하자. 1번 데이터가 중복되는 상황이다.
insert into onj select * from onj_old
1행에 오류:
ORA-00001: 무결성 제약 조건(SCOTT.SYS_C0011936)에 위배됩니다
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- ignore_row_on_dupkey_index 힌트를 사용하자.
SQL> select index_name from user_indexes where table_name = 'ONJ'
INDEX_NAME
------------
SYS_C0011936
SQL> insert /*+ ignore_row_on_dupkey_index(onj SYS_C0011936) */ into onj select * from onj_old;
2 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> select * from onj;
ID NAME
---------- --------------------
1 1길동
2 2길동
3 3길동
4 4길동
5 5길동
 | |||||
|---|---|---|---|---|---|
[기업100%환급]SQL기초에서 Schema Object까지  | 총 | 5일 | 40시간 | 01-20 | |
[기업100%환급]PL/SQL,ORACLE HINT,TUNING  | 총 | 5일 | 40시간 | 02-17 | |
 | |||||
| SQL초보에서실전전문가까지 | 총 | 18일 | 54시간 | 02-11 | |
| 빅데이터 하둡 실무과정 | 총 | 12일 | 36시간 | 02-18 | |
 | |||||
| SQL초보에서실전전문가까지 | 총 | 8일 | 56시간 | 01-18 | |
| 빅데이터 하둡 실무과정 | 총 | 5일 | 35시간 | 02-15 | |
댓글 없음:
댓글 쓰기