- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=16482310&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
1. 목표 : 데이터 정돈하기
수열 중간에 숫자가 몇 개 빠지면 마음이 매우 불편해지는 사람들이 있다.
데이타베이스 기술에 대한 이해나 확신이 부족하여, 빠진 이빨을 맞추려고 함.
2. 안티패턴: 모든 틈 메우기
틈을 발견했을 때 대다수 사람들의 반응은 틈을 메우고 싶어 한다는 것이다.
2.1. 시퀀스에서 벗어난 번호 할당하기
시퀀스 중에 사용하지 않은 값을 찾아서 입력하기
이 방법 역시 두 애플리케이션이 동시에, 사용되지 않은 가장 작은 값을 찾으려 하면
한쪽에선 에러가 발생한다.
SELECT b1.bug_id + 1 FROM Bugs b1 LEFT OUTER JOIN Bugs AS b2 ON (b1.bug_id + 1 = b2.bug_id) WHERE b2.bug_id IS NULL ORDER BY b1.bug_id LIMIT 1;
2.2. 기존 행의 번호를 다시 매기기
기존 행에 대한 키 값을 업데이트해 틈을 메우고 모든 값을 연속이 되도록 할 수 있다고 생각함
동시 업데이트 시, 충돌 우려, 부모-자식 관계 시, 복잡한 업데이트 절차 필요
UPDATE Bugs SET bug_id = 3 WHERE bug_id = 4;
2.3. 데이터 불일치 만들기
PK 값을 재사용을 자제 하자 ( PK 업데이트 포함 )
3. 안티패턴 인식 방법
- "INSERT 를 롤백했을 때 자동 생성된 아이디 값을 어떻게 재상요할 수 있지 ?"
- "bug_id 4에 무슨 일이 일어난 거지?"
- "사용되지 않은 첫 번째 아이디 값을 어떻게 조회할 수 있을까?"
- "번호가 넘치면 어떻게 되지?"
4.안티패턴 사용이 합당한 경우
가상키 값을 바꿔야 할 이유는 없다.
가상키 값은 아무런 의미도 가지지 말아야 한다.
PK 칼럼의 값이 어떤 의미를 가진다면 그건 자연키(natural key)지 가상키가 아니다.
5. 해법: 극복하라
PK 값은 유일하고 NULL 이 아니어서 각 행을 참조한느 데 사용할 수 있어야 한다.
이게 전부다. 행을 식별하는 데 연속적인 숫자일 필요는 없다.
5.1. 행에 번호 메기기
ROW_NUMBER() 를 활용 ( ROWNUM )
SELECT t1.* FROM (SELECT a.account_name, b.bug_id, b.summary, ROW_NUMBER() OVER (ORDER BY a.account_name, b.date_reported) AS rn FROM Accounts a JOIN Bugs b ON (a.account_id = b.reported_by)) AS t1 WHERE t1.rn BETWEEN 51 AND 100;
5.2.GUID 사용 하기 ( MS SQL )
CREATE TABLE Bugs ( bug_id UNIQUEIDENTIFIER DEFAULT NEWID(), summary varchar(100) ); INSERT INTO Bugs (bug_id, summary) VALUES (DEFAULT, 'crashes when I save'); select bug_if from Bugs ==> 48D4D3F8-D323-4E90-A56B-1B852BE86EBC
장점
- 중복 걱정하지 않고 여러 데이터베이스 서버에서 동시에 가상키를 생성할 수 있다.
- 아무도 틈에 대해 불평하지 않는다. PK 값으로 16진수를 32자리나 입력해야 하는 것을
불평하느라 바쁠 것이기 때문이다.
단점
- 값이 너무 길고 입력하기 나쁘다.
- 값이 랜덤하기 때문에 어떤 패턴을 추론할 수 없고 큰 값이 최근 행에 나타낸다고 생각할 수 없다.
( 고로 값이 의미 없다. 그저 Unique 할뿐 ) - GUID를 저장하는데 16바이트가 필요하다. 일반적인 4바이트 정수 가상키를 사용하는 것보다
공간도 많이 차지하고 속도도 느리다.
가장 중요한 문제
커뮤니케이션 문제지 기술적 문제가 아니다.
- 기술을 설명하기
틈 메우기가 의미 없음을 기술적으로 이해시켜라. - 비용을 명확하게 하기
틈 메우기에 대한 비용을 설명하라. - 자연키 사용하기
의미있는 PK 값이 필요하다면, 가상키를 사용하지 말고,
어떤 의미를 식별할 수 있는 문자열이나 숫자를 사용하라.
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=16482310&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.