대용량 테이블 컬럼 추가시 가용성 최적화

목적 : 대용량 테이블에 컬럼추가시 sch-lock이 아닌 key lock락으로 가용성을 높여보자 

 

 약 25000만건 의 대용량 더미 테이블 생성

 

 

똑같은 조건의 또하나의 테이블 생성

Select * INTO TEST_T1 FROM TEST

 

유닉크 클러스터드  인덱스 생성

CREATE unique Clustered  INDEX CL_IDX ON TEST_T(SEQ)

with(MAXDOP = 4)

 

CREATE unique Clustered  INDEX CL_IDX ON TEST_T1(SEQ)

with(MAXDOP = 4)

 

1.TEST_T 테이블에 NOT NULL 속성 디폴드 0으로 칼럼 추가

스키마 락 발생 으로 테이블 뿐만 아니라 시스템 오브젝트 또한 Lock 발생 유도

 Insert와 Select update 등 모든것이 블락 당함

 

 

대략 2500만건에 약 10분 정도 소요

 

 2.TEST_T1 테이블에 NULL 속성 으로 컬럼 추가(널 속성 추가 후 Not null로 변경 예정)

ALTER TABLE TEST_T1

ADD TestColumn1 char(50) null default(0);   --> 0.01 소요

 

 

10000건씩 업데이트 실행(1번의 한방 쿼리를 통한 Update 보다 속도는 1.5배 느린편)

DECLARE @ROWS int

SET @ROWS=10000;

UPDATE TOP (@ROWS) TEST_T1

SET TESTCN1 = 0

WHERE TESTCN1 IS NULL;

WHILE @@ROWCOUNT = 10000

BEGIN

UPDATE TOP (@ROWS) TEST_T1

SET TESTCN1 =0

WHERE TESTCN1 IS NULL;

END

 

Lock 현황을 보면 Key 단위로 Lock 이 발생

Insert는 Identity로 인하여 문제 없음 와 Select 시 문제 없음(NO LOCK) 단 현재 X락 거린 KEY 업데이트시 블락이 걸릴 수 있음

 

 

임시 인덱스 삭제

DROP INDEX TEST_T1.NC_IDX

 

컬럼 변경(Null --> Not null로)   

ALTER TABLE TEST_T1

ALTER COLUMN TESTCN1char(50) NOT NULL;

 

컬럼변경시 스키마 락 및 50초 정도가 소요지만 어차피 값들은 모두 NOT NULL인 상태고 Default 0으로 처리 되어있기 떄문에

추후 서버를 내릴때 실행해도 상관없음

2010-03-05 13:36 에 bun-ker님이 마지막으로 수정