Bora.Choi

No

방법

문제점

1

클러스터 Key IGNORE_DUP_KEY 옵션

온라인 Reindex가 되지 않음 -DBA 상에서 관리 이슈 있음
성능 이슈 -  Data 비교를 하기 때문에 일반 보다는 비용이 더 소요 됨
성능 TEST No1 참고

2

유니크 Index를 생성하고 IGNORE_DUP_KEY 옵션

No 1의 문제점 해결
동일 Key에 Index가 더 생성되는 것이며,  No1 번과 마찬가지로
데이터 입력시 Sort 비용,  Semi Join 비용,  Buffer Pool 공간 사용 비용이 증가하여
기본 예상 비용 보다  3배 정도 초과 운영됨

데이터를 삽입 하는 동안 정합성을 유지 하기 위해  입력 Data Range 에 대한 Shared range, Update Lock를 발생 함

동시 변경시 문제가 있을 수 있고,  SQL 아키텍쳐상 Lock이  Escalation 될 수 있으므로 Table Lock으로 발전 될 수 있는 가능 성 있음

성능 Test No2 참고

3

쿼리 문을 수정 하는 방법

Insert 문을 수정해서 입력 하는 방법
- EXCEPT 방식, Merage 방식

-- Insert 문 수정  with except

INSERT dbo.PKDUP (pk)

select  i from dbo.DUMP_INSERT

where i between 10000 and 130000

except select pk from dbo.PKDUP

-- merge 문 사용

MERGE dbo.PKDUP  AS b

USING (SELECT i FROM dbo.DUMP_INSERT WHERE i BETWEEN 10000 AND 130000) AS s

ON s.i = b.pk

WHEN NOT MATCHED THEN

INSERT (pk) VALUES (s.i);

성능 TEST No3 참고

4

INSTEAD OF trigger 사용 방법

3번 으로 하면 가장 좋으나 대량의 bulk 성으로 입력을 해야 하는 경우 대안이 될 수 있을 것 같음

해당 트리거는 테이블당 하나만 생성 할 수 있고
INSTEAD 트리거가 제약 조건에 위배 되면 AFTER이 실행 되지 않음을 알아야 함

성능 Test No4 참고

CREATE TABLE dbo.PK_DUP

(

    pk int NOT NULL,  CONSTRAINT PK_PK_DUP PRIMARY KEY (pk) WITH (IGNORE_DUP_KEY = ON)

);

GO

select * from sys.indexes where object_id = object_id ('PK_DUP')

-- Unique values

INSERT dbo.PK_DUP (pk)

VALUES (1), (3), (5);

GO

-- key 3 already exists

INSERT dbo.PK_DUP (pk)

VALUES (2), (3), (4);

/*

중복 키가 무시되었습니다.

(2개 행이 영향을 받음)

*/

select * from PK_DUP

/*

pk

1

2

3

4

5

*/

/**  Problem 1 :  Rebild를 할  수 없다. **/

ALTER INDEX PK_PK_DUP ON dbo.PK_DUP

REBUILD WITH (FILLFACTOR = 90, ALLOW_ROW_LOCKS = OFF, ONLINE = ON, IGNORE_DUP_KEY = ON);

GO

/*

메시지 1979, 수준 16, 상태 1, 줄 1

인덱스 옵션 ignore_dup_key는 PRIMARY KEY 또는 UNIQUE 제약 조건을 적용하므로 이 인덱스 옵션을 사용하여 인덱스 'PK_PK_DUP'을(를) 변경할 수 없습니다.

*/

-- 새로운 Idea

-- 유니크 key를 만들어서 처리 한다.

CREATE TABLE dbo.PKDUP (pk int NOT NULL CONSTRAINT PK_PKDUP PRIMARY KEY);

GO

INSERT dbo.PKDUP (pk) VALUES (1), (2), (3);

GO

-- New constraint (or index) with IGNORE_DUP_KEY, added ONLINE

ALTER TABLE dbo.PKDUP  ADD CONSTRAINT UIDX_PKDUP  UNIQUE NONCLUSTERED (pk) WITH (IGNORE_DUP_KEY = ON, ONLINE = ON);

select * from sys.indexes where object_id = object_id ('PKDUP')

-- key 3을 입력 한다.

INSERT dbo.PKDUP (pk) VALUES (3), (4), (5);

GO

/*중복 키가 무시되었습니다. */

SELECT pk FROM dbo.PKDUP;

/*

pk

1

2

3

4

5

*/

-- Problem 1 해결

ALTER INDEX PK_PKDUP ON dbo.PKDUP

REBUILD WITH (FILLFACTOR = 90, ALLOW_ROW_LOCKS = OFF, ONLINE = ON);

GO

ALTER INDEX UIDX_PKDUP ON dbo.PKDUP

REBUILD WITH (FILLFACTOR = 100, ALLOW_ROW_LOCKS = OFF, ONLINE = ON);

GO

No1의 문제는 해결 되나  성능 상의 이슈로  입력 하는 데이터와 기존 데이터를 비교 하는 추가 적인 작업 단계가 늘어나면서

예상 비용이 3배 정도 증가 함. 

CREATE TABLE DUMP_INSERT ( i int )

set nocount on

declare @i int 

set @i =1

while (@i < 10000 )

begin

             insert into DUMP_INSERT  values ( @i )

             set @i = @i + 1

end

insert into PKDUP

select * from DUMP_INSERT

set nocount on

declare @i int 

set @i =90000

while (@i < 110001 )

begin

             insert into DUMP_INSERT  values ( @i )

             set @i = @i + 1

end

-- 유니크 Index 삭제

alter table dbo.PKDUP

drop constraint  UIDX_PKDUP

set nocount on

declare @i int 

set @i =120001

while (@i < 150001 )

begin

             insert into DUMP_INSERT  values ( @i )

             set @i = @i + 1

end

-- Insert 문 수정  with except

INSERT dbo.PKDUP (pk)

select  i from dbo.DUMP_INSERT

where i between 10000 and 130000

except select pk from dbo.PKDUP

n  여전히  Buffer pool 공간은 사용 하지만 예상 비용은 0.96

-- merge 문 사용

MERGE dbo.PKDUP  AS b

USING (SELECT i FROM dbo.DUMP_INSERT WHERE i BETWEEN 10000 AND 130000) AS s

ON s.i = b.pk

WHEN NOT MATCHED THEN

INSERT (pk) VALUES (s.i);

-    비용은 증가하나 Buffer pool 공간은 사용하지 않음

-- INSTEAD OF trigger

-- INSTEAD OF 트리거는 트리거 문의 표준 동작을 무시합니다.

-- 따라서 이 트리거를 사용하여 하나 이상의 열에서 오류나 값을 확인하고 행을 삽입, 업데이트 또는 삭제하기 전에 추가 동작을 수행할 수 있습니다.

-- INSTEAD OF 트리거가 제약 조건을 위한 하면 AFTER 트리거는 실행되지 않습니다.

CREATE TRIGGER  tI_PKDUP ON DBO.PKDUP

INSTEAD OF INSERT

AS

BEGIN

             SET NOCOUNT ON

             INSERT dbo.PKDUP (pk)

             SELECT PK FROM INSERTED

             except select pk from dbo.PKDUP

END

GO

INSERT dbo.PKDUP (pk)

SELECT i FROM dbo.DUMP_INSERT WHERE i BETWEEN 10000 AND 130000

-    Mearge랑 비슷한 예상 비용이 발생 함. 트리거 사용을 권하지는 않지만 대량 데이터 입력으로 인해 No3 번이 불가능 할 때 고려해 볼 만 함.