이번에 소개해드릴 내용은 실제 색인 생성을 하는 부분입니다.
실제로 색인을 생성하면서 이리저리 살펴 보도록 하지요.
간단한 클러스터드 인덱스와 넌클러스터드 인덱스의 생성은 앞에서 보았으니
이번엔 유니크 인덱스 생성을 간단히 알아보고 이어서 Fill Factor를 알아 보도록 하지요.
먼저 유니크 인덱스 입니다.
|
USE konanTestDB
GO
--데이터 조회
SELECT TOP 100 * FROM konan_test_table
SELECT COUNT(konan_id) FROM konan_test_table
--유니크 인덱스 생성
CREATE UNIQUE INDEX idx ON konan_test_table (konan_data)
--중복 데이터 삽입 - 에러
INSERT INTO konan_test_table(konan_data, konan_date) VALUES('1', getdate())
서버: 메시지 2601, 수준 14, 상태 3, 줄 1
고유 인덱스 'idx'이(가) 있는 'konan_test_table' 개체에 중복 키 행을 삽입할 수 없습니다.
문이 종료되었습니다.
--인덱스 삭제
DROP INDEX konan_test_table.idx
--유니크 인덱스 생성 - WITH IGNORE_DUP_KEY
CREATE UNIQUE INDEX idx ON konan_test_table (konan_data)
WITH IGNORE_DUP_KEY
INSERT INTO konan_test_table(konan_data, konan_date) VALUES('1', getdate())
서버: 메시지 3604, 수준 16, 상태 1, 줄 1
중복 키가 무시되었습니다.
|
테이블에 이미 PRIMARY KEY가 있더라도 얼마든지 유니크한 값을 구별할 수 있는
유니크 인덱스로 고유성을 보장받을 수 있게 됩니다.
다음으로 중요한 이야기인 Fill Factor를 이야기해 보도록 하지요.
인덱스를 생성하면 데이터가 변경될 경우 인덱스 페이지 역시 변경이 되어야 합니다.
이때 데이터의 삽입이 상당히 많다면 인덱스 페이지는 계속적으로 변경되므로
시스템에 어느정도 부하를 줄 수 있게 되지요. 이때 fill factor를 적절히 이용해
인덱스의 리프레벨과 중간레벨에 여유 공간을 두는 겁니다.
자 여기서 그렇다면!!!
만약 Fill Factor가 100%라면? 이는 현재 인덱스의 리프 레벨과 중간레벨이 모두 100%로
차 있는 상태인 겁니다. 적절한 수량만큼만 채우는 80% 정도로 값을 주면?
인덱스의 리프레벨은 20%정도의 여유 공간이 생기지요. 하지만!! 그만큼 SELECT의
속도는 느려질 수 있는 겁니다.
우선 샘플을 또한 봐 보도록 하지요.
|
USE konanTestDB
GO
--데이터 조회
SELECT TOP 100 * FROM konan_test_table
SELECT COUNT(konan_id) FROM konan_test_table
--인덱스 삭제
DROP INDEX konan_test_table.idx
CREATE CLUSTERED INDEX idx ON konan_test_table (konan_id)
--인덱스 정보 조회
sp_helpindex konan_test_table
--평균 페이지 밀도 판단.
DBCC SHOWCONTIG(konan_test_table)
DBCC SHOWCONTIG이(가) 'konan_test_table' 테이블을 스캔하는 중...
테이블: 'konan_test_table' (1977058079); 인덱스 ID: 0, 데이터베이스 ID: 18
TABLE 수준 스캔이 수행되었습니다.
- 스캔한 페이지................................: 141
- 스캔한 익스텐트..............................: 18
- 전환된 익스텐트..............................: 17
- 익스텐트 당 평균 페이지 수........................: 7.8
- 스캔 밀도[최적:실제].......: 100.00% [18:18]
- 익스텐트 스캔 조각화 상태 ...................: 0.00%
- 페이지 당 사용 가능한 평균 바이트 수.....................: 81.8
- 평균 페이지 밀도(전체).....................: 98.99%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.
--하나건너 하나씩 데이터 삭제
BEGIN TRAN
DELETE FROM konan_test_table WHERE konan_id % 2 = 0
COMMIT TRAN
--평균 페이지 밀도 판단.
DBCC SHOWCONTIG(konan_test_table)
DBCC SHOWCONTIG이(가) 'konan_test_table' 테이블을 스캔하는 중...
테이블: 'konan_test_table' (1977058079); 인덱스 ID: 0, 데이터베이스 ID: 18
TABLE 수준 스캔이 수행되었습니다.
- 스캔한 페이지................................: 141
- 스캔한 익스텐트..............................: 18
- 전환된 익스텐트..............................: 17
- 익스텐트 당 평균 페이지 수........................: 7.8
- 스캔 밀도[최적:실제].......: 100.00% [18:18]
- 익스텐트 스캔 조각화 상태 ...................: 0.00%
- 페이지 당 사용 가능한 평균 바이트 수.....................: 4018.8
- 평균 페이지 밀도(전체).....................: 50.35%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.
--인덱스 재생성
CREATE CLUSTERED INDEX idx ON konan_test_table (konan_id)
WITH FILLFACTOR = 80, DROP_EXISTING
--평균 페이지 밀도 판단.
DBCC SHOWCONTIG(konan_test_table)
DBCC SHOWCONTIG이(가) 'konan_test_table' 테이블을 스캔하는 중...
테이블: 'konan_test_table' (1977058079); 인덱스 ID: 1, 데이터베이스 ID: 18
TABLE 수준 스캔이 수행되었습니다.
- 스캔한 페이지................................: 87
- 스캔한 익스텐트..............................: 11
- 전환된 익스텐트..............................: 10
- 익스텐트 당 평균 페이지 수........................: 7.9
- 스캔 밀도[최적:실제].......: 100.00% [11:11]
- 논리 스캔 조각화 상태 ..................: 11.49%
- 익스텐트 스캔 조각화 상태 ...................: 9.09%
- 페이지 당 사용 가능한 평균 바이트 수.....................: 1601.7
- 평균 페이지 밀도(전체).....................: 80.21%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.
|
이와 비슷하게 생각하실 것이 PAD_INDEX 입니다.
PAD_INDEX와 FILL_FACTOR의 차이는 PAD_INDEX는 중간레벨까지 여유 공간을
둔다는 점입니다.
|
--인덱스 재생성
CREATE CLUSTERED INDEX idx ON konan_test_table (konan_id)
WITH FILLFACTOR = 80, PAD_INDEX, DROP_EXISTING
|
이와 비슷한 작업을 하는 인덱스를 재구성하는 DBCC명령이 두가지 있습니다.
먼저 DBCC DBREINDEX명령과
그다음 DBCC INDEXDEFRAG 입니다.
|
--DBCC DBREINDEX - 1개 인덱스인 idx인덱스를 fillfactor 90으로 재생성
DBCC DBREINDEX (konan_test_table, 'idx', 90)
--테이블내 모든 인덱스를 fillfactor 90으로 재생성
DBCC DBREINDEX (konan_test_table, '', 90)
|
DBCC DBREINDEX는 물리적으로 완전히 모든 행을 재배열 하며 인덱스까지 재구성
합니다. 비록 SQL2000부터는 이 작업이 빨라 지고 온라인 상에서 할 수 있다고
하지만.. 실제 대단한 부하가 걸리는 작업입니다.
이때 물리적으로 모든 행을 재배열 하지 않고 인덱스만 재구성하는 명령이 있으니
바로 DBCC INDEXDEFRAG 입니다. SQL2000만의 기능이기도 하지요.
|
--DBCC INDEXDEFRAG
DBCC INDEXDEFRAG (konanTestDB, konan_test_table, idx)
|
이런 식으로 사용 가능하며 비교적 좋은 성능을 보여 줍니다.
다음으로 통계데이터 입니다.
먼저 다음 질의를 봐 보도록 하지요.
|
--통계데이터 보기
DBCC SHOW_STATISTICS(konan_test_table,idx)
|
앞에서 잠깐 말 드린대로 선택도에 따라서 쿼리 최적화기가 인덱스를 타게할지
타게하지 않을지를 결정한다고 말씀 드렸지요.
이때 이 근거가 되는것이 바로 통계 데이터 입니다.
SQL서버2000은 자동으로 통계데이터가 해당하는 인덱스에 대해서 생성되며
자동으로 유지 됩니다.
|
--통계데이터 UPDATE
UPDATE STATISTICS konan_test_table(idx)
|
이런 식으로 update가 가능합니다.
튜닝적인 요소로 볼때 이는 수동으로 하지 않는것이 거의 대부분 좋으며 수동으로 할
경우는 대단히 많은 모니터링이 필요 합니다.
그럼 이정도로 색인 생성에 대한 부분은 마치도록 하지요.
다음으로 색인의 마지막 이야기인 성능과 관련된 부분을 조금더 보도록 하지요.
수고하셨습니다.
ex
DBCC SHOWCONTIG(tbl_report)
DBCC SHOW_STATISTICS(tbl_report,idx_report_01)
UPDATE STATISTICS tbl_report(idx_report_01)