2014년 2월 2일 일요일

[오라클11g힌트교육]Oracle 11g Hint ignore_row_on_dupkey_index (오라클11g 중복키 무시 힌트)

[오라클11g힌트교육]Oracle 11g Hint ignore_row_on_dupkey_index (오라클11g 중복키 무시 힌트) 

자주 하는 작업 중 하나는 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길동 

댓글 없음:

댓글 쓰기