-- Title : [2k] Clustered Index 변경 시 WITH DROP_EXISTING 사용
-- Reference : blog.naver.com/choyjoy, dbrang.tistory.com
-- Key word : 클러스터된 인덱스 clustered index with drop existing
********************************************************************************************/
클러스터된 인덱스를 다시 만들 떄 CREATE INDEX 구문에 DROP EXISTING 절 사용
넌클러스터(nonclustered) 인덱스의 내부 구조는 해당 테이블에 클러스터된 인덱스가 존재하는지 여부에 따라 결정된다. 클러스터 인덱스가 있는 테이블을 클러스터된 테이블이라고 하고, 클러스터된 인덱스가 없는 테이블을 힙(heap) 테이블이라고 한다. 힙 테이블에 만들어진 넌클러스터 인덱스의 경우에는 SQL 서버가 넌클러스터 인덱스의 리프 레벨에서 데이터 페이지를 찾기 위해서 행ID(RID)를 사용하게 된다. 행ID를 인덱스내부에 포함하게 되면 데이터가 변경되었을 때 넌클러스터 인덱스를 유지관리하는 데에 연관된 오버헤드를 최소한으로 줄여줄 수 있기 때문에 성능향상에 도움이 된다(넌클러스터 인덱스에 대한 좀 더 자세한 정보는 Kalen Delaney의 2001년 9월호 “튜닝할 시점” 기사와 2001년 10월호의 “커버된 인덱스” 기사를 참조한다).
하지만 클러스터된 인덱스가 존재하는 테이블의 넌클러스터 인덱스의 경우에는 데이터 페이지를 찾기 위한 행식별자로 행ID가 아닌 클러스터된 인덱스에 포함된 키를 사용하기 때문에, 만약 넌클러스터 인덱스가 존재하고 있는 테이블의 클러스터된 인덱스를 삭제하게 되면, SQL 서버는 각 레코드의 위치를 지정하는 지시자의 역할을 기존 클러스터된 인덱스의 키에서 다시 행ID를 사용할 수 있도록 변경시켜 주기 위해, 해당 테이블에 존재하는 모든 넌클러스터 인덱스를 삭제하고 다시 생성하게 된다. 반대로 이미 넌클러스터 인덱스가 존재하고 있는 테이블에 클러스터된 인덱스를 추가하는 경우에도 기존의 넌클러스터 인덱스의 내부구조에서 사용하고 있던 행ID 대신 클러스터된 인덱스의 키를 포함할 수 있도록 반드시 한 번은 넌클러스터 인덱스를 삭제하고 다시 생성하는 작업을 수행하게 된다. 넌클러스터 인덱스를 삭제하고 다시 생성하는 작업은 대상 테이블이 매우 대용량인 경우에는 상당히 많은 시간과 디스크 I/O, CPU 시간을 사용하게 된다.
클러스터된 인덱스를 변경할 때 CREATE INDEX 구문에 DROP_EXISTIING 절을 함께 사용하게 되면 좀 더 빠르게 작업할 수 있다. DROP_EXISTING 절은 SQL 서버가 클러스터된 인덱스를 삭제하고 다시 만들 때 기존에 존재하고 있던 넌클러스터 인덱스에 포함되어 있는 클러스터된 인덱스의 키 부분을 삭제하지 않고 새로 만들어진 클러스터된 인덱스의 키로 변경시키도록 하는 역할을 한다. (DBCC DBREINDEX 명령을 사용하게 되면 기존에 존재하는 클러스터된 인덱스를 다시 만드는 작업을 할 때 SQL 서버가 넌클러스터 인덱스를 새로 생성하는 작업을 수행하지 않도록 해 준다. 하지만 DBCC DBREINDEX 명령은 기존 인덱스에 포함되어 있는 컬럼 간의 위치를 이동시키기 위해서는 사용할 수 없다). 결국 DROP_EXISTING 절을 사용하게 되면 넌클러스터 인덱스를 삭제하고 다시 만드는 전체 과정에 소요되는 시간 및 자원을 절감할 수 있게 되는 것이다. 추가적으로, 기존에 존재하는 클러스터된 인덱스의 키를 변경하지 않고 UNIQUE 옵션만을 추가한 경우에는 SQL 서버가 기존에 존재하고 있는 넌클러스터 인덱스를 다시 만드는 작업을 하지 않기 때문에, 클러스터된 인덱스에 UNIQUE 옵션만 추가했다고 해서 확실한 성능의 개선이 되는 것은 아니다. DROP_EXISTING 절은 클러스터된 인덱스와 넌클러스터 인덱스가 공존하는 테이블의 클러스터된 인덱스를 수정하는 작업을 수행할 때 시간 및 자원을 효과적으로 절감해 주는 유용한 기능으로 사용할 수 있다.
use credit
go
select * into charge9 from charge
select * into charge99 from charge
go
create clustered index pk_charge9 on charge9(charge_no)
create clustered index pk_charge99 on charge99(charge_no)
create nonclustered index ix_charge9 on charge9(member_no)
create nonclustered index ix_charge99 on charge99(member_no)
go
sp_helpindex 'charge9'
sp_helpindex 'charge99'
go
set statistics io on
go
-- charge9의 CL을 삭제 후 재생성
drop index charge9.pk_charge9
create clustered index pk_charge9 on charge9(charge_no, member_no)
/*
'charge9' 테이블. 스캔 수 2, 논리적 읽기 수 1164, 물리적 읽기 수 0, 미리 읽기 수 0.
'charge9' 테이블. 스캔 수 2, 논리적 읽기 수 1164, 물리적 읽기 수 0, 미리 읽기 수 0.
'charge9' 테이블. 스캔 수 2, 논리적 읽기 수 1170, 물리적 읽기 수 0, 미리 읽기 수 0.
*/
go
-- charge99의 CL을 with_drop_existing으로 재생성
create clustered index pk_charge99 on charge99(charge_no, member_no)
with drop_existing
/*
'charge99' 테이블. 스캔 수 2, 논리적 읽기 수 1164, 물리적 읽기 수 0, 미리 읽기 수 0.
'charge99' 테이블. 스캔 수 2, 논리적 읽기 수 1170, 물리적 읽기 수 0, 미리 읽기 수 0.
*/
go
sp_helpindex 'charge9'
sp_helpindex 'charge99'
go