티스토리 뷰
안녕하세요. 우연히 IBM 문서에서 발견한 내용을 소개해 드립니다. 이렇게 매뉴얼을 정독하지 않은 티를 내게 되는군요.
인포믹스에서 UNIQUE 제약조건과 UNIQUE 인덱스의 기능에 차이가 있다는 내용입니다.
평소에 두개의 기능이 다를 것이라고 생각하지 않았는데, 알고나니 개발할 때 이런 점까지 고려해야 되나 생각이 들기도 하네요.
문서에서 설명한 주요 기능의 차이는 아래와 같습니다.
In DML statements, enabled unique constraints on a logged table are checked at the end of a statement, but unique indexes are checked on a row-by-row basis, thereby preventing any insert or update of a row that might potentially violate the uniqueness of the specified column (or for a multiple-column column constraint or index, the column list).
요약하자면, 로깅 모드 (unbuffered, buffered) 테이블에서는
unique 제약조건에서는 insert나 update 문장 실행의 끝날때 중복여부를 확인하고,
unique 인덱스에서는 row-by-row, 건건이 실행할 때마다 중복값을 확인한다는 차이가 있다는 것입니다.
위의 IBM 문서에 기반한 내용으로 아래의 시나리오를 만들어 봤습니다.
시나리오 #1 ==================
1. 정수형(integer) 데이터 타입 1개를 포함한 테이블 작성
> create table test (c int);
2. 테스트 데이터 10만개 입력
> insert into test select level from sysmaster:sysdual connect by level <= 100000;
3. 해당 컬럼에 UNIQUE 인덱스 생성
> create unique index test_idx on test(c);
4. UPDATE 문장 실행
> update test set c=c+1;
346: Could not update a row in the table. << UPDATE 문장 실행 즉시 오류가 발생합니다.
100: ISAM error: duplicate value for a record with unique key.
============================
시나리오 #2 ==================
1. 정수형(integer) 데이터 타입 1개를 포함한 테이블 작성, 해당 컬럼에 UNIQUE 제약조건 설정.
> create table test (c int, unique(c));
2. 테스트 데이터 10만개 입력
> insert into test select level from sysmaster:sysdual connect by level <= 100000;
3. UPDATE 문장 실행
> update test set c=c+1;
100000 row(s) updated. << 약 3~7초 정도 경과후 중복값 오류 없이 UPDATE가 실행됩니다.
============================
여기서 시나리오 2번에서 오류가 발생하지 않았으니 unique 제약조건으로 선언하는 것이 낫겠다는 생각이 들기도 합니다.
그러나 몇가지 고려할 사항이 있습니다.
unique 제약조건이 정의되어 있으면 UPDATE/INSERT 문장이 수행되는 도중에 중복값이 발생하더라도
오류가 곧바로 발생하지 않습니다. 아래에서 극단적인 예를 들어보겠습니다.
> drop table test;
> create table test (c int, unique(c));
> insert into test select level from sysmaster:sysdual connect by level <= 100000;
100000 row(s) inserted.
$ echo "update test set c=3;" | timex dbaccess stores_demo
Database selected.
268: Unique constraint (informix.u160_134) violated.
100: ISAM error: duplicate value for a record with unique key.
Error in line 1
Near character position 18
Database closed.
real 37.48
user 0.02
sys 0.03
시나리오 #2의 내용과 동일한 테이블과 데이터를 만들고 같은 값 3으로 UPDATE를 실행했습니다.
unique 제약조건이 있기 때문에 사실 수행될 수 없는 문장입니다.
그러나 수행이 이뤄지고 마지막에 중복값을 확인하기 때문에 약 37초가 소요되었습니다.
수행시간은 시스템 성능에 따라 차이는 있겠지만 수행중 오류가 발생하고 변경사항이 rollback 되었음을 확인할 수 있습니다.
여기서 몇가지 더 의문이 생겼습니다.
그럼 primary key 제약조건에서는 어떨까?
unique 제약조건과 동일하게 268 오류가 발생했습니다.
그러면 unique 인덱스, primary key 제약조건을 만들었을때는?
마찬가지로 268 오류가 발생했습니다.
그럼 unique 인덱스, primary key 제약조건을 만들고 UPDATE 문장에 unique 인덱스를 사용하도록 힌트를 주었다면?
UPDATE 문장에서 INDEX를 강제로 사용하면 달라지지 않을까 예상했지만,
역시 아래와 같이 268 오류가 발생했습니다.
> drop table test;
> create table test (c int);
> insert into test select level from sysmaster:sysdual connect by level <= 100000;
> create unique index test_pk on test(c);
> alter table test add constraint primary key (c) constraint test_pk;
$ echo "update {+ explain index(test test_pk)} test set c=3;" | timex dbaccess stores_demo
Database selected.
268: Unique constraint (informix.test_pk) violated.
100: ISAM error: duplicate value for a record with unique key.
Error in line 1
Near character position 50
Database closed.
real 39.24
user 0.02
sys 0.03
결론적으로 unique/primary key 제약조건이 설정된 경우에는
UPDATE/INSERT 문장이 실행된 이후에 중복값이 검사된다는 것입니다.
속성이 유일한 값이다보니 UPDATE가 드물긴 하겠지만, unique index와는 작동 방식이 다르니 참고할 필요는 있을 것 같습니다.
** 참고
- Total
- Today
- Yesterday