ALTER INDEX(Transact-SQL)

아티클
12/15/2008

인덱스를 비활성화, 다시 작성 또는 다시 구성하거나 인덱스에 대한 옵션을 설정하여 기존 테이블 또는 뷰 인덱스(관계형 또는 XML)를 수정합니다.

구문

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
          | [ PARTITION = partition_number 
                [ WITH ( <single_partition_rebuild_index_option>
                        [ ,...n ] )
                ] 
            ]
        ]
    | DISABLE
    | REORGANIZE 
        [ PARTITION = partition_number ]
        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
    | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
        table_or_view_name
}

<rebuild_index_option > ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor 
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

<set_index_option>::=
{
    ALLOW_ROW_LOCKS= { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

인수

index_name
인덱스의 이름입니다. 인덱스 이름은 테이블이나 뷰에서 고유해야 하지만 데이터베이스 내에서 고유할 필요는 없습니다. 인덱스 이름은 식별자 규칙을 따라야 합니다.

ALL
인덱스 유형에 관계없이 테이블이나 뷰에 연결된 모든 인덱스를 지정합니다. ALL을 지정하면 하나 이상의 인덱스가 오프라인 또는 읽기 전용 파일 그룹에 있거나 지정한 작업이 하나 이상의 인덱스 유형에 대해 허용되지 않을 경우 해당 문이 실패합니다. 다음 표에서는 인덱스 작업과 허용되지 않는 인덱스 유형을 나열합니다.

이 작업에 ALL 지정	테이블에 인덱스가 하나 이상 있으면 실패함
REBUILD WITH ONLINE = ON	XML 인덱스 큰 개체 데이터 형식 열: image, text, ntext, varchar(max), nvarchar(max), varbinary(max) 및 xml
REBUILD PARTITION = partition_number	분할되지 않은 인덱스, XML 인덱스 또는 비활성 인덱스
REORGANIZE	ALLOW_PAGE_LOCKS가 OFF로 설정된 인덱스
REORGANIZE PARTITION = partition_number	분할되지 않은 인덱스, XML 인덱스 또는 비활성 인덱스
SET	비활성 인덱스

PARTITION = partition_number에 ALL을 지정하는 경우에는 모든 인덱스가 정렬되어야 합니다. 즉, 해당 파티션 함수를 기준으로 인덱스가 분할됩니다. PARTITION 절에 ALL을 사용하면 partition_number가 동일한 모든 인덱스 파티션이 다시 작성되거나 다시 구성됩니다. 분할된 인덱스에 대한 자세한 내용은 분할된 테이블 및 인덱스를 참조하십시오.

database_name
데이터베이스의 이름입니다.

schema_name
테이블이나 뷰가 속한 스키마의 이름입니다.

table_or_view_name
인덱스와 관련된 테이블이나 뷰의 이름입니다. 개체의 인덱스에 대한 보고서를 표시하려면 sys.indexes 카탈로그 뷰를 사용합니다.

REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]
동일한 열, 인덱스 유형, 고유성 특성 및 정렬 순서를 사용하여 인덱스가 다시 작성되도록 지정합니다. 이 절은 DBCC DBREINDEX와 동일합니다. REBUILD는 비활성 인덱스를 활성화합니다. ALL 키워드를 지정하지 않으면 클러스터형 인덱스를 다시 작성해도 관련 비클러스터형 인덱스는 다시 작성되지 않습니다. 인덱스 옵션을 지정하지 않으면 sys.indexes에 저장된 기존 인덱스 옵션 값이 적용됩니다. sys.indexes에 값이 저장되지 않은 인덱스 옵션의 경우에는 옵션의 인수 정의에 표시된 기본값이 적용됩니다.

XML 인덱스를 다시 작성할 때는 ONLINE과 IGNORE_DUP_KEY 옵션이 유효하지 않습니다.

ALL을 지정한 경우 기본 테이블이 힙이면 다시 작성 작업을 수행해도 테이블에는 아무 영향이 없습니다. 테이블에 연결된 비클러스터형 인덱스는 모두 다시 작성됩니다.

데이터베이스 복구 모델이 대량 로그 또는 단순으로 설정되어 있으면 다시 작성 작업이 최소한으로 기록될 수 있습니다. 자세한 내용은 인덱스 작업에 대한 복구 모델 선택을 참조하십시오.

[!참고] 기본 XML 인덱스를 다시 작성할 때는 인덱스 작업 중에 기본 사용자 테이블을 사용할 수 없습니다.

PARTITION
인덱스의 한 파티션만 다시 작성하거나 다시 구성하도록 지정합니다. index_name이 분할된 인덱스가 아니면 PARTITION을 지정할 수 없습니다.

partition_number
다시 작성하거나 다시 구성할 분할된 인덱스의 파티션 번호입니다. partition_number는 변수를 참조할 수 있는 상수 식입니다. 여기에는 사용자 정의 유형 변수 또는 함수와 사용자 정의 함수가 포함될 수 있지만 Transact-SQL 문은 참조할 수 없습니다. partition_number 를 지정하지 않으면 해당 문이 실패합니다.

WITH (<single_partition_rebuild_index_option>)
SORT_IN_TEMPDB와 MAXDOP는 단일 파티션(PARTITION = n)을 다시 작성할 때 지정할 수 있는 옵션입니다. 단일 파티션 다시 작성 작업에는 XML 인덱스를 지정할 수 없습니다.

분할된 인덱스를 다시 작성하는 작업은 온라인으로 수행할 수 없습니다. 이 작업을 수행하는 동안 전체 테이블이 잠깁니다.

DISABLE
인덱스를 비활성 및 SQL Server 2005 데이터베이스 엔진에서 사용할 수 없음으로 표시합니다. 모든 인덱스를 비활성화할 수 있습니다. 비활성 인덱스의 인덱스 정의는 기본 인덱스 데이터 없이 시스템 카탈로그에 유지됩니다. 클러스터형 인덱스를 비활성화하면 사용자가 기본 테이블 데이터에 액세스하지 못합니다. 인덱스를 활성화하려면 ALTER INDEX REBUILD 또는 CREATE INDEX WITH DROP_EXISTING을 사용합니다. 자세한 내용은 인덱스 비활성화를 참조하십시오.

REORGANIZE
인덱스 리프 수준이 다시 구성되도록 지정합니다. 이 절은 DBCC INDEXDEFRAG와 동일합니다. ALTER INDEX REORGANIZE 문은 항상 온라인으로 수행됩니다. 즉, 장기간 차단 테이블 잠금이 유지되지 않으며 ALTER INDEX REORGANIZE 트랜잭션 중 기본 테이블에 대한 쿼리나 업데이트를 계속할 수 있습니다. ALLOW_PAGE_LOCKS가 OFF로 설정된 인덱스 또는 비활성 인덱스에는 REORGANIZE를 지정할 수 없습니다.

WITH ( LOB_COMPACTION = { ON | OFF } )
LOB(Large Object) 데이터가 포함된 모든 페이지를 압축하도록 지정합니다. LOB 데이터 형식은 image, text, ntext, varchar(max), nvarchar(max), varbinary(max) 및 xml입니다. 이 데이터를 압축하면 디스크 공간을 더 효율적으로 사용할 수 있습니다. 기본값은 ON입니다.
- ON
  큰 개체 데이터가 포함된 모든 페이지가 압축됩니다.
  
  지정한 클러스터형 인덱스를 다시 구성하면 클러스터형 인덱스에 포함된 모든 LOB 열이 압축됩니다. 비클러스터형 인덱스를 다시 구성하면 인덱스에서 키가 아닌(포괄) 열인 LOB 열이 모두 압축됩니다. 자세한 내용은 포괄 열을 사용하여 인덱스 만들기를 참조하십시오.
  
  ALL을 지정하면 지정한 테이블이나 뷰에 연결된 모든 인덱스가 다시 구성되고 클러스터형 인덱스, 기본 테이블 또는 포괄 열에 포함된 비클러스터형 인덱스에 연결된 모든 LOB 열이 압축됩니다.
- OFF
  큰 개체 데이터가 포함된 페이지가 압축되지 않습니다.
  
  OFF를 지정해도 힙에는 아무 영향이 없습니다.
LOB 열이 없는 경우 LOB_COMPACTION 절은 무시됩니다.

SET ( <set_index option> [ ,... n] )
인덱스를 다시 작성하거나 다시 구성하지 않고 인덱스 옵션을 지정합니다. 비활성 인덱스에는 SET을 지정할 수 없습니다.

PAD_INDEX = { ON | OFF }
인덱스 패딩을 지정합니다. 기본값은 OFF입니다.
- ON
  FILLFACTOR로 지정된 사용 가능한 공간의 비율이 인덱스의 중간 수준 페이지에 적용됩니다. FILLFACTOR를 지정함과 동시에 PAD_INDEX를 ON으로 설정하지 않으면 sys.indexes에 저장된 채우기 비율 값이 사용됩니다.
- OFF 또는 fillfactor가 지정되지 않았습니다.
  중간 수준 페이지는 용량 한도 가까이 채워집니다. 이로 인해 중간 페이지의 키 집합을 기준으로 인덱스에 포함될 수 있는 최대 크기의 행 하나 이상을 위한 충분한 공간이 남겨집니다.
자세한 내용은 CREATE INDEX(Transact-SQL)를 참조하십시오.

FILLFACTOR = fillfactor
데이터베이스 엔진에서 인덱스 생성 또는 변경 중에 각 인덱스 페이지의 리프 수준에 대한 채우기 정도를 나타내는 백분율을 지정합니다. fillfactor는 1에서 100 사이의 정수 값이어야 하며 기본값은 0입니다.

[!참고] 채우기 비율 값 0과 100은 모든 면에서 동일합니다.

명시적 FILLFACTOR 설정은 인덱스를 처음 만들거나 다시 작성할 때만 적용됩니다. 데이터베이스 엔진에서는 페이지의 빈 공간에 대해 지정된 비율을 동적으로 유지하지 않습니다. 자세한 내용은 CREATE INDEX(Transact-SQL)를 참조하십시오.

채우기 비율 설정을 보려면 sys.indexes를 사용하십시오.

중요:
데이터베이스 엔진에서는 클러스터형 인덱스를 만들 때 데이터를 다시 배포하므로 FILLFACTOR 값으로 클러스터형 인덱스를 만들거나 변경하면 데이터가 차지하는 저장 공간 크기에 영향이 미칩니다.

SORT_IN_TEMPDB = { ON | OFF }
tempdb에 정렬 결과를 저장할지 여부를 지정합니다. 기본값은 OFF입니다.
- ON
  인덱스 작성에 사용되는 중간 정렬 결과가 tempdb에 저장됩니다. tempdb가 사용자 데이터베이스와는 다른 디스크 집합에 있으면 인덱스를 만드는 데 필요한 시간이 줄어들 수 있습니다. 그러나 이렇게 하면 인덱스를 작성하는 동안 사용되는 디스크 공간의 크기가 늘어납니다.
- OFF
  중간 정렬 결과는 인덱스와 동일한 데이터베이스에 저장됩니다.
정렬 작업이 필요하지 않거나 메모리에서 정렬을 수행할 수 있으면 SORT_IN_TEMPDB 옵션이 무시됩니다.

자세한 내용은 tempdb 및 인덱스 만들기를 참조하십시오.

IGNORE_DUP_KEY = { ON | OFF }
고유 클러스터형 또는 고유 비클러스터형 인덱스의 다중 행 삽입 트랜잭션에 중복된 키 값이 있을 경우에 대한 오류 응답을 지정합니다. 기본값은 OFF입니다.
- ON
  경고 메시지가 발생하며 UNIQUE 인덱스를 위반하는 행만 실패합니다.
- OFF
  오류 메시지가 발생하고 전체 트랜잭션이 롤백됩니다.
IGNORE_DUP_KEY 설정은 인덱스를 만들거나 다시 작성한 후에 수행되는 삽입 작업에만 적용됩니다. 인덱스 작업 중에는 이 설정이 적용되지 않습니다. UPDATE 문에서는 IGNORE_DUP_KEY를 사용해도 아무 효과가 없습니다.

XML 인덱스 및 뷰에 만든 인덱스의 경우 IGNORE_DUP_KEY를 ON으로 설정할 수 없습니다. 자세한 내용은 CREATE INDEX(Transact-SQL)를 참조하십시오.

STATISTICS_NORECOMPUTE = { ON | OFF }
배포 통계를 다시 계산할지 여부를 지정합니다. 기본값은 OFF입니다.

ON
이전 통계는 자동으로 다시 계산되지 않습니다.

OFF
자동 통계 업데이트가 설정됩니다.

자동 통계 업데이트를 복원하려면 STATISTICS_NORECOMPUTE를 OFF로 설정하거나 NORECOMPUTE 절 없이 UPDATE STATISTICS를 실행합니다.

중요:
배포 통계 자동 재계산 기능을 해제하면 쿼리 최적화 프로그램에서 테이블과 관련된 쿼리에 대해 최적의 실행 계획을 선택할 수 없습니다.

ONLINE = { ON | OFF }
인덱스 작업 중 기본 테이블과 관련 인덱스를 쿼리 및 데이터 수정에 사용할 수 있는지 여부를 지정합니다. 기본값은 OFF입니다.

[!참고] 온라인 인덱스 작업은 SQL Server 2005 Enterprise Edition에서만 사용할 수 있습니다.
- ON
  인덱스 작업 중에 장기 테이블 잠금이 유지되지 않습니다. 인덱스 작업의 주 단계 동안 의도 공유(IS) 잠금만 원본 테이블에서 유지됩니다. 따라서 기본 테이블 및 인덱스를 계속 쿼리 또는 업데이트할 수 있습니다. 작업이 시작될 때 아주 짧은 기간 동안 공유(S) 잠금이 원본 개체에서 유지됩니다. 작업이 끝날 때 짧은 기간 동안 비클러스터형 인덱스가 생성되는 경우에는 원본에 대해 공유(S) 잠금이 유지되고, 온라인 상태에서 클러스터형 인덱스가 생성 또는 삭제될 때나 클러스터형 또는 비클러스터형 인덱스가 다시 작성될 때는 스키마 수정(SCH-M) 잠금이 획득됩니다. 로컬 임시 테이블에서 인덱스를 만드는 경우에는 ONLINE을 ON으로 설정할 수 없습니다.
- OFF
  인덱스 작업 중에 테이블 잠금이 적용됩니다. 클러스터형 인덱스를 생성, 다시 작성 또는 삭제하거나 비클러스터형 인덱스를 다시 작성 또는 삭제하는 오프라인 인덱스 작업은 테이블에 대해 스키마 수정(SCH-M) 잠금을 획득합니다. 이 경우 작업 중에 모든 사용자가 기본 테이블에 액세스할 수 없게 됩니다. 비클러스터형 인덱스를 만드는 오프라인 인덱스 작업은 테이블에 대해 공유(S) 잠금을 획득합니다. 따라서 기본 테이블을 업데이트할 수는 없지만 SELECT 문과 같은 읽기 작업은 허용됩니다.
자세한 내용은 온라인 인덱스 작동 방식을 참조하십시오. 잠금에 대한 자세한 내용은 잠금 모드를 참조하십시오.

전역 임시 테이블의 인덱스를 비롯한 인덱스를 온라인으로 다시 작성할 수 있습니다. 단, 다음 항목은 예외입니다.
- 비활성 인덱스
- XML 인덱스
- 로컬 임시 테이블의 인덱스
- 분할된 인덱스
- 기본 테이블에 LOB 데이터 형식이 포함된 경우 클러스터형 인덱스
- LOB 데이터 형식 열과 함께 정의된 비클러스터형 인덱스
테이블에 LOB 데이터 형식이 포함되지만 이러한 열 중에서 인덱스 정의에 키 또는 키가 아닌 열로 사용되는 열이 없으면 비클러스터형 인덱스를 온라인으로 다시 작성할 수 있습니다.

ALLOW_ROW_LOCKS = { ON | OFF }
행 잠금의 허용 여부를 지정합니다. 기본값은 ON입니다.
- ON
  인덱스에 액세스할 때 행 잠금이 허용됩니다. 데이터베이스 엔진은 행 잠금이 사용될 시기를 결정합니다.
- OFF
  행 잠금이 사용되지 않습니다.

ALLOW_PAGE_LOCKS = { ON | OFF }
페이지 잠금의 허용 여부를 지정합니다. 기본값은 ON입니다.
- ON
  인덱스에 액세스할 때 페이지 잠금이 허용됩니다. 데이터베이스 엔진에서 페이지 잠금이 사용되는 시기를 결정합니다.
- OFF
  페이지 잠금을 사용하지 않습니다.
[!참고] ALLOW_PAGE_LOCKS가 OFF로 설정되면 인덱스를 다시 구성할 수 없습니다.

MAXDOP = max_degree_of_parallelism
인덱스 작업 기간 중 최대 병렬 처리 수준 구성 옵션을 무시합니다. 자세한 내용은 max degree of parallelism 옵션을 참조하십시오. MAXDOP를 사용하여 병렬 계획 실행에 사용되는 프로세서 수를 제한할 수 있습니다. 최대 프로세서 수는 64입니다.

max_degree_of_parallelism은 다음 값이 될 수 있습니다.
- 1
  병렬 계획을 생성하지 않습니다.
- >1
  병렬 인덱스 작업에 사용되는 최대 프로세서 수를 지정된 값으로 제한합니다.
- 0(기본값)
  현재 시스템 작업을 기반으로 실제 프로세서 수 이하의 프로세서를 사용합니다.
자세한 내용은 병렬 인덱스 작업 구성을 참조하십시오.

[!참고] 병렬 인덱스 작업은 SQL Server 2005 Enterprise Edition에서만 사용할 수 있습니다.

주의

인덱스를 다시 분할하거나 다른 파일 그룹으로 이동하는 데는 ALTER INDEX를 사용할 수 없습니다. 이 문을 사용하여 열 추가 또는 삭제, 열 순서 변경과 같은 인덱스 정의를 수정할 수 없습니다. 이러한 작업을 수행하려면 DROP_EXISTING 절에 CREATE INDEX를 사용하십시오.

옵션을 명시적으로 지정하지 않으면 현재 설정이 적용됩니다. 예를 들어 REBUILD 절에 FILLFACTOR 설정을 지정하지 않으면 다시 작성하는 동안 시스템 카탈로그에 저장된 채우기 비율 값이 사용됩니다. 현재 인덱스 옵션 설정을 보려면 sys.indexes를 사용하십시오.

[!참고] ONLINE, MAXDOP 및 SORT_IN_TEMPDB에 대한 값은 시스템 카탈로그에 저장되지 않습니다. 인덱스 문에서 지정하지 않으면 해당 옵션의 기본값이 사용됩니다.

다중 프로세서 컴퓨터에서는 다른 쿼리의 경우와 마찬가지로 ALTER INDEX REBUILD가 자동으로 프로세서를 더 사용하여 인덱스 수정과 관련된 정렬 및 검색 작업을 수행합니다. LOB_COMPACTION을 사용하거나 사용하지 않고 ALTER INDEX REORGANIZE를 실행할 경우 최대 병렬 처리 수준 값은 단일 스레드 작업입니다. 자세한 내용은 병렬 인덱스 작업 구성을 참조하십시오.

인덱스가 위치한 파일 그룹이 오프라인이거나 읽기 전용으로 설정되어 있으면 인덱스를 다시 구성할 수 없습니다. ALL 키워드를 지정하면 하나 이상의 인덱스가 오프라인 또는 읽기 전용 파일 그룹에 있을 경우 해당 문이 실패합니다.

인덱스 다시 작성

인덱스를 다시 작성하면 이 인덱스가 삭제된 다음 다시 생성됩니다. 이렇게 하면 조각화를 제거하고, 지정된 채우기 비율 또는 기존 채우기 비율 설정을 기준으로 페이지를 압축하여 디스크 공간을 회수하고, 인덱스 행을 연속된 페이지로 다시 정렬할 수 있습니다. ALL을 지정하면 테이블의 모든 인덱스가 단일 트랜잭션으로 삭제되고 다시 작성됩니다. FOREIGN KEY 제약 조건은 미리 삭제하지 않아도 됩니다. 익스텐트가 128개 이상인 인덱스를 다시 작성하면 데이터베이스 엔진에서 실제 페이지 할당 취소와 해당 관련 잠금이 트랜잭션 커밋 후까지 지연됩니다. 자세한 내용은 큰 개체 삭제 및 다시 작성을 참조하십시오.

[!참고] 일반적으로 작은 인덱스는 자주 다시 작성하거나 다시 구성하더라도 조각화가 줄어들지 않습니다. 작은 인덱스의 페이지는 혼합 익스텐트에 저장됩니다. 혼합 익스텐트는 최대 8개의 개체에서 공유하므로 작은 인덱스를 다시 구성하거나 다시 작성해도 인덱스의 조각화가 줄어들지 않을 수 있습니다. 혼합 익스텐트에 대한 자세한 내용은 페이지 및 익스텐트를 참조하십시오.

이전 버전의 SQL Server에서는 비클러스터형 인덱스를 다시 작성하여 하드웨어 오류로 인한 불일치를 해결할 수 있는 경우도 있습니다. SQL Server 2005에서도 비클러스터형 인덱스를 오프라인으로 다시 작성하여 인덱스와 클러스터형 인덱스 간의 불일치를 복구할 수 있습니다. 그러나 인덱스를 온라인으로 다시 작성하는 경우에는 비클러스터형 인덱스 간의 불일치를 해결할 수 없습니다. 온라인으로 다시 작성하는 경우 기존의 비클러스터형 인덱스를 사용하므로 불일치가 계속 남아 있게 됩니다. 반대로 오프라인으로 인덱스를 다시 작성하면 클러스터형 인덱스 검색 또는 힙 검색이 수행되어 불일치가 제거됩니다. 이전 버전의 경우처럼 영향을 받은 데이터를 백업한 후 복원하여 불일치를 제거하는 것이 좋습니다. 비클러스터형 인덱스의 경우에는 오프라인으로 인덱스를 다시 작성하여 인덱스 간 불일치를 해결할 수 있습니다. 자세한 내용은 DBCC CHECKDB(Transact-SQL)를 참조하십시오.

인덱스 다시 구성

인덱스를 다시 구성할 때는 최소한의 시스템 리소스가 사용됩니다. 이때는 테이블에서 클러스터형 및 비클러스터형 인덱스의 리프 수준에 대한 조각 모음이 수행되고 왼쪽에서 오른쪽으로 표시되는 리프 노드의 논리적 순서에 맞도록 리프 수준 페이지가 물리적으로 다시 정렬되어 표시됩니다. 다시 구성 작업을 수행하면 인덱스 페이지도 압축됩니다. 이때 압축은 기존 채우기 비율 값을 기준으로 수행됩니다. 채우기 비율 설정을 보려면 sys.indexes를 사용하십시오.

ALL을 지정하면 테이블에서 관계형 인덱스, 클러스터형 및 비클러스터형 모두와 XML 인덱스가 다시 구성됩니다. ALL을 지정할 때는 몇 가지 제한 사항이 적용됩니다. 자세한 내용은 인수 섹션에서 ALL에 대한 정의를 참조하십시오.

자세한 내용은 인덱스 다시 구성 및 다시 작성을 참조하십시오.

인덱스 비활성화

인덱스를 비활성화하면 사용자가 인덱스에 액세스할 수 없으며 클러스터형 인덱스의 경우 기본 테이블 데이터에도 액세스할 수 없습니다. 인덱스 정의는 시스템 카탈로그에 유지됩니다. 뷰의 비클러스터형 인덱스 또는 클러스터형 인덱스를 비활성화하면 인덱스 데이터가 물리적으로 삭제됩니다. 클러스터형 인덱스를 비활성화하면 데이터에 액세스할 수 없지만 인덱스가 삭제되거나 다시 작성될 때까지는 데이터가 B-트리에서 유지 관리되지 않는 상태로 남아 있습니다. 활성 또는 비활성 인덱스의 상태를 보려면 sys.indexes 카탈로그 뷰의 is_disabled 열을 쿼리합니다.

트랜잭션 복제 게시의 테이블에서는 기본 키 열에 연결된 인덱스를 비활성화할 수 없습니다. 이러한 인덱스는 복제에 필요합니다. 인덱스를 비활성화하려면 게시에서 테이블을 먼저 삭제해야 합니다. 자세한 내용은 데이터 및 데이터베이스 개체 게시를 참조하십시오.

ALTER INDEX REBUILD 문 또는 CREATE INDEX WITH DROP_EXISTING 문을 사용하여 인덱스를 활성화할 수 있습니다. ONLINE 옵션이 ON으로 설정되어 있으면 클러스터형 비활성 인덱스를 다시 작성할 수 없습니다. 자세한 내용은 인덱스 비활성화를 참조하십시오.

옵션 설정

지정한 인덱스에 대해 해당 인덱스를 다시 작성하거나 다시 구성하지 않고 ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY 및 STATISTICS_NORECOMPUTE 옵션을 설정할 수 있습니다. 수정된 값은 인덱스에 바로 적용됩니다. 이러한 설정을 보려면 sys.indexes를 사용하십시오. 자세한 내용은 인덱스 옵션 설정을 참조하십시오.

행 및 페이지 잠금 옵션

ALLOW_ROW_LOCKS = ON이고 ALLOW_PAGE_LOCK = ON이면 인덱스에 액세스할 때 행 수준, 페이지 수준 및 테이블 수준 잠금이 허용됩니다. 데이터베이스 엔진에서는 적절한 잠금을 선택하고 행 또는 페이지 잠금에서 테이블 잠금으로 에스컬레이션할 수 있습니다.

ALLOW_ROW_LOCKS = OFF이고 ALLOW_PAGE_LOCK = OFF이면 인덱스에 액세스할 때 테이블 수준 잠금만 허용됩니다. 인덱스에 대한 잠금 세분성을 구성하는 방법은 인덱스 잠금 사용자 지정을 참조하십시오.

행 또는 페이지 잠금 옵션이 설정된 경우 ALL을 지정하면 설정이 모든 인덱스에 적용됩니다. 기본 테이블이 힙인 경우 다음과 같은 방식으로 설정이 적용됩니다.

ALLOW_ROW_LOCKS = ON 또는 OFF	힙 및 연관된 비클러스터형 인덱스
ALLOW_PAGE_LOCKS = ON	힙 및 연관된 비클러스터형 인덱스
ALLOW_PAGE_LOCKS = OFF	비클러스터형 인덱스 전체 즉, 비클러스터형 인덱스에는 모든 페이지 잠금이 허용되지 않습니다. 힙에서는 페이지에 대한 공유(S), 업데이트(U) 및 배타(X) 잠금만 허용되지 않습니다. 데이터베이스 엔진에서는 내부에서 사용하기 위해 의도 페이지 잠금(IS, IU 또는 IX)을 획득할 수 있습니다.

자세한 내용은 잠금 에스컬레이션(데이터베이스 엔진)을 참조하십시오.

온라인 인덱스 작업

인덱스를 다시 작성할 때 ONLINE 옵션이 ON으로 설정되어 있으면 기본 개체, 테이블 및 연결된 인덱스를 쿼리와 데이터 수정에 사용할 수 있습니다. 변경 중에는 아주 잠시 동안만 배타적 테이블 잠금이 유지됩니다.

인덱스를 다시 구성하는 과정은 항상 온라인으로 수행됩니다. 이 프로세스는 잠금을 장기간 유지하지 않으므로 실행 중인 업데이트나 쿼리를 차단하지 않습니다.

다음을 수행할 때만 동일한 테이블에서 동시 온라인 작업을 수행할 수 있습니다.

여러 개의 비클러스터형 인덱스 생성
동일한 테이블에서 여러 인덱스 다시 구성
동일한 테이블에서 겹치지 않는 인덱스를 다시 작성하는 동안 여러 인덱스 다시 구성

동시에 수행된 그 밖의 모든 온라인 인덱스 작업은 실패합니다. 예를 들어 동일한 테이블에서 두 개 이상의 인덱스를 다시 작성할 수 없습니다. 또는 동일한 테이블에서 기존 인덱스를 다시 작성하면서 새 인덱스를 생성할 수 없습니다.

자세한 내용은 온라인으로 인덱스 작업 수행을 참조하십시오.

사용 권한

ALTER INDEX를 실행하려면 최소한 테이블이나 뷰에 대한 ALTER 권한이 필요합니다.

예

1. 인덱스 다시 작성

다음 예에서는 Employee 테이블의 단일 인덱스를 다시 작성합니다.

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

2. 테이블의 모든 인덱스 다시 작성 및 옵션 지정

다음 예에서는 ALL 키워드를 지정합니다. 이 키워드는 테이블에 연결된 인덱스를 모두 다시 작성합니다. 3개의 옵션이 지정됩니다.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

3. LOB 압축을 사용하여 인덱스 다시 구성

다음 예에서는 단일 클러스터형 인덱스를 다시 구성합니다. 인덱스에 리프 수준의 LOB 데이터 형식이 포함되어 있으므로 해당 문은 큰 개체 데이터가 포함된 페이지도 모두 압축합니다. 기본값이 ON이므로 WITH(LOB_COMPACTION) 옵션은 지정하지 않아도 됩니다.

USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

4. 인덱스에 옵션 설정

다음 예에서는 AK_SalesOrderHeader_SalesOrderNumber 인덱스에 몇 가지 옵션을 설정합니다.

USE AdventureWorks;
GO
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

5. 인덱스 비활성화

다음 예에서는 Employee 테이블의 비클러스터형 인덱스를 비활성화합니다.

USE AdventureWorks;
GO
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee
DISABLE ;
GO

6. 제약 조건 비활성화

다음 예에서는 PRIMARY KEY 인덱스를 비활성화하여 PRIMARY KEY 제약 조건을 비활성화합니다. 기본 테이블에 대한 FOREIGN KEY 제약 조건이 자동으로 비활성화되고 경고 메시지가 표시됩니다.

USE AdventureWorks;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
DISABLE ;
GO

결과 집합에서 다음과 같은 경고 메시지를 반환합니다.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID' on table 'EmployeeDepartmentHistory' referencing table 'Department' was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

7. 제약 조건 활성화

다음 예에서는 6번 예에서 비활성화된 PRIMARY KEY와 FOREIGN KEY 제약 조건을 활성화합니다.

PRIMARY KEY 인덱스를 다시 작성하여 PRIMARY KEY 제약 조건이 활성화됩니다.

USE AdventureWorks;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
REBUILD ;
GO

그런 다음 FOREIGN KEY 제약 조건이 활성화됩니다.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

8. 분할된 인덱스 다시 작성

다음 예에서는 분할된 인덱스 IX_TransactionHistory_TransactionDate의 단일 파티션인 파티션 번호 5를 다시 작성합니다. 이 예에서는 분할된 인덱스 샘플이 설치되었다고 가정합니다. 설치 정보는 Readme_PartitioningScript를 참조하십시오.

USE AdventureWorks;
GO
-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5;
GO

참고 항목

참조

CREATE INDEX(Transact-SQL)
sys.dm_db_index_physical_stats
EVENTDATA(Transact-SQL)

도움말 및 정보

SQL Server 2005 지원 받기

다음을 통해 공유