인덱스 비활성화에 대한 지침

  • 아티클

인덱스를 비활성화하면 사용자가 인덱스에 액세스할 수 없으며 클러스터형 인덱스의 경우 기본 테이블 데이터에도 액세스할 수 없습니다. SQL Server 2005 데이터베이스 엔진 에서 SQL Server 업그레이드 동안 인덱스를 자동으로 비활성화하거나 사용자가 수동으로 인덱스를 비활성화할 수 있습니다. 자세한 내용은 인덱스 비활성화를 참조하십시오.

모든 인덱스 유형을 비활성화할 수 있습니다. 인덱스가 비활성화되면 다음과 같은 규칙이 적용됩니다.

  • 인덱스가 고유하면 다른 테이블의 인덱싱된 열을 참조하는 모든 FOREIGN KEY 제약 조건과 PRIMARY KEY 또는 UNIQUE 제약 조건이 비활성화됩니다. 인덱스를 비활성화하는 사용자는 이러한 테이블에 대한 ALTER 권한이 있어야 합니다. 그렇지 않으면 ALTER INDEX DISABLE 문이 실패합니다. 인덱스가 클러스터형인 경우 기본 테이블에 대한 들어오는 FOREIGN KEY 제약 조건과 나가는 FOREIGN KEY 제약 조건이 모두 비활성화됩니다.
    인덱스가 비활성화되면 경고 메시지에 제약 조건 이름이 나열됩니다. 인덱스를 다시 작성한 후 ALTER TABLE CHECK CONSTRAINT 문을 사용하여 제약 조건을 수동으로 활성화해야 합니다.
  • 비활성화된 인덱스는 유지 관리되지 않습니다.
  • 쿼리 최적화 프로그램에서 쿼리 실행 계획을 만들 때 인덱스가 고려되지 않습니다. 또한 테이블 힌트로 비활성화된 인덱스를 참조하는 쿼리가 실패합니다.
  • 비활성화된 기존 인덱스와 같은 이름의 인덱스를 만들 수 없습니다. 기존 인덱스 정의가 메타데이터에 남아 있기 때문입니다.
  • 비활성화된 인덱스는 삭제할 수 없습니다.

비클러스터형 인덱스 비활성화

비클러스터형 인덱스를 비활성화하면 해당 인덱스 데이터가 물리적으로 삭제됩니다. 그러나 인덱스 정의는 메타데이터에 남아 있습니다. 다음은 비클러스터형 인덱스 비활성화에 대한 추가 지침입니다.

  • 인덱스의 통계는 해당 위치에 그대로 있고 필요에 따라 자동으로 업데이트됩니다.
  • 비클러스터형 인덱스는 관련 클러스터형 인덱스를 비활성화할 경우 자동으로 비활성화됩니다. 이렇게 비활성화된 인덱스는 테이블이나 뷰의 클러스터형 인덱스를 활성화하거나 테이블의 클러스터형 인덱스를 삭제해야 활성화할 수 있습니다. ALTER INDEX ALL REBUILD 문을 사용하여 클러스터형 인덱스를 활성화하지 않는 한 비클러스터형 인덱스는 명시적으로 활성화해야 합니다. 자세한 내용은 인덱스 및 제약 조건 활성화에 대한 지침을 참조하십시오.

클러스터형 인덱스 비활성화

다음은 클러스터형 인덱스 비활성화에 대한 추가 지침입니다.

  • 클러스터형 인덱스를 삭제하거나 다시 작성할 경우를 제외하고는 비활성화된 클러스터형 인덱스의 데이터 행에 액세스할 수 없습니다. 이에 따라 다음과 같은 결과가 나타납니다.
    • 테이블 행이나 제약 조건을 수정하는 ALTER TABLE 문 및 인덱스에 대한 SELECT, UPDATE, DELETE, INSERT, CREATE INDEX, CREATE STATISTICS, UPDATE STATISTICS 문이 실패합니다.
    • CREATE VIEW, DROP VIEW, CREATE TRIGGER, DROP TRIGGER, DROP INDEX, ALTER TABLE ENABLE/DISABLE TRIGGER, TRUNCATE TABLE 및 DROP TABLE 문은 성공합니다.
    • 클러스터형 인덱스가 비활성화된 동안에는 비클러스터형 인덱스를 만들 수 없습니다.
  • 테이블과 연결된 기존의 비클러스터형 인덱스와 XML 인덱스가 자동으로 비활성화되어 액세스할 수 없습니다.
  • 테이블을 참조하는 뷰의 모든 클러스터형 인덱스와 비클러스터형 인덱스가 비활성화됩니다. 이러한 인덱스는 참조되는 테이블의 인덱스에 따라 다시 작성해야 합니다.

제약 조건 비활성화

다음은 PRIMARY KEY, FOREIGN KEY 및 UNIQUE 제약 조건 비활성화에 대한 추가 지침입니다.

  • ALTER INDEX DISABLE 문을 사용하여 관련 인덱스를 비활성화하면 PRIMARY KEY 및 UNIQUE 제약 조건도 비활성화됩니다.

  • PRIMARY KEY 제약 조건을 비활성화하면 모든 관련 FOREIGN KEY 제약 조건도 비활성화됩니다. 이는 제약 조건의 NOCHECK CONSTRAINT 옵션을 설정하는 것과 같습니다.

  • 참조되는 테이블에 대해 ALTER 또는 CONTROL 권한이 있어야 합니다.

  • CASCADE UPDATE 또는 DELETE 작업이 외래 키 참조에 선언되어 있고 해당 참조가 비활성화된 상태에서 제약 조건이 수정 내용을 참조 테이블에 전파하는 업데이트 또는 삭제 문을 실행하면 해당 문이 실패합니다.

  • PRIMARY KEY 또는 UNIQUE 인덱스가 비활성화된 경우나 SQL Server 업그레이드 동안 인덱스를 비활성화하는 변경 내용에 의해 중복 값이 실수로 테이블에 추가될 수 있습니다. 중복 행을 수동으로 수정해야 인덱스를 활성화할 수 있습니다. 다음과 같은 해결 방법이 있습니다.

    • 중복 값을 수동으로 제거하거나 변경합니다.
    • UNIQUE 제약 조건을 만드는 과정에서 UNIQUE 인덱스가 생성된 경우가 아니면 CREATE INDEX WITH DROP_EXISTING을 사용하여 UNIQUE를 지정하지 않고 인덱스를 다시 만듭니다.
    • PRIMARY KEY 또는 UNIQUE 제약 조건을 만드는 과정에서 인덱스가 생성된 경우에는 제약 조건을 삭제해야 합니다. 그러면 인덱스가 삭제됩니다. PRIMARY KEY 제약 조건의 경우 FOREIGN KEY 제약 조건도 삭제해야 합니다.

  • 비활성화된 FOREIGN KEY 및 CHECK 제약 조건은 is_not_trusted로 표시됩니다. sys.check_constraints 및 sys.foreign_keys 카탈로그 뷰에서 이를 확인할 수 있습니다. 이는 시스템에서 테이블의 모든 행에 대해 해당 제약 조건을 더 확인하지 않음을 나타냅니다. 제약 조건을 다시 활성화해도 ALTER TABLE의 WITH CHECK 옵션을 지정하기 전까지는 테이블에 대해 기존 행이 다시 확인되지 않습니다. WITH CHECK를 지정하면 제약 조건이 다시 trusted로 표시됩니다.
    다음 예에서는 데이터에 허용되는 급여를 제한하는 제약 조건을 비활성화합니다. ALTER TABLE에 WITH NOCHECK CONSTRAINT를 사용하면 제약 조건이 비활성화되어 해당 제약 조건을 위반하는 데이터를 삽입할 수 있습니다. WITH CHECK CHECK CONSTRAINT는 제약 조건을 다시 활성화하고 이 제약 조건에 대해 기존 데이터의 유효성도 검사합니다.

    CREATE TABLE cnst_example 
(id INT NOT NULL,
    name VARCHAR(10) NOT NULL,
    salary MONEY NOT NULL
    CONSTRAINT salary_cap CHECK (salary < 100000);
)

-- Disable the constraint.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap;

-- Reenable the constraint.
ALTER TABLE cnst_example WITH CHECK CHECK CONSTRAINT salary_cap;

뷰의 인덱스 비활성화

뷰의 클러스터형 인덱스를 비활성화하면 해당 인덱스 데이터가 물리적으로 삭제됩니다. 다음은 뷰의 인덱스 비활성화에 대한 추가 지침입니다.

  • 뷰의 클러스터형 인덱스를 비활성화해도 기본 테이블을 수정할 수 있습니다.
  • 뷰의 클러스터형 인덱스를 비활성화하면 해당 뷰의 비클러스터형 인덱스도 비활성화됩니다.
  • 클러스터형 인덱스와 비클러스터형 인덱스에 대한 인덱스 데이터 행이 삭제됩니다. 그러나 뷰 정의와 인덱스 정의는 메타데이터에 유지되며 인덱스를 다시 작성하여 다시 만들 수 있습니다.
  • ALTER INDEX ALL REBUILD 문은 테이블의 비활성화된 인덱스를 모두 작성하고 비활성화합니다. 뷰의 비활성화된 인덱스는 예외입니다. 뷰의 인덱스는 별도의 ALTER INDEX ALL REBUILD 문으로 활성화해야 합니다.
  • 뷰의 클러스터형 인덱스를 다시 작성해도 뷰의 비클러스터형 인덱스가 자동으로 활성화되지 않습니다.
  • 클러스터형 인덱스를 다시 작성한 후 비클러스터형 인덱스를 다시 작성하여 수동으로 활성화해야 합니다.

비활성화된 인덱스에 대해 온라인으로 인덱스 작업 수행

테이블에 비활성화된 클러스터형 인덱스가 없으면 비활성화된 비클러스터형 인덱스를 온라인으로 다시 작성할 수 있습니다. 그러나 ALTER INDEX REBUILD 또는 CREATE INDEX WITH DROP_EXISTING 문을 사용하는 경우에는 비활성화된 클러스터형 인덱스를 오프라인으로 다시 작성해야 합니다. 온라인 인덱스 작업에 대한 자세한 내용은 온라인으로 인덱스 작업 수행을 참조하십시오.

비활성화된 인덱스의 통계

다음은 인덱스가 비활성화된 경우 인덱스 통계의 제한 사항입니다.

  • 비활성화된 클러스터형 인덱스가 있는 테이블에서는 CREATE STATISTICS 문을 실행할 수 없습니다.
  • 인덱스가 비활성화된 상태이고 다음 조건에 해당하면 AUTO_CREATE_STATISTICS 데이터베이스 옵션이 열에 대한 새 통계를 만듭니다.
    • AUTO_CREATE_STATISTICS가 ON으로 설정된 경우
    • 열에 대한 기존 통계가 없는 경우
    • 쿼리 최적화 동안 통계가 필요한 경우
  • 지정한 테이블에 비활성화된 클러스터형 인덱스가 있으면 sp_autostats가 실패합니다.
  • sp_updatestats는 비활성화된 클러스터형 인덱스의 통계를 업데이트하지 않습니다.
  • sp_createstats는 비활성화된 인덱스의 선행 열에 대한 통계를 만듭니다. indexonly가 지정되어 있으면 비활성화된 인덱스의 열에 대한 통계가 생성되지 않습니다. 해당 열이 활성화된 다른 인덱스에도 사용되는 경우는 예외입니다.

DBCC 명령

클러스터형 인덱스가 비활성화되면 DBCC CHECKDB가 기본 테이블에 대한 정보를 반환할 수 없습니다. 대신 클러스터형 인덱스가 비활성화되었다고 보고합니다. 비활성화된 인덱스를 조각 모음하는 데 DBCC INDEXDEFRAG를 사용할 수 없습니다. 이 문은 오류 메시지와 함께 실패합니다. DBCC DBREINDEX를 사용하여 비활성화된 인덱스를 다시 작성할 수 있습니다.

비활성화된 인덱스의 상태 보기

인덱스나 PRIMARY KEY 또는 UNIQUE 제약 조건을 비활성화하면 영향을 받는 인덱스와 FOREIGN KEY 또는 CHECK 제약 조건이 모두 나열된 경고 메시지가 표시됩니다. 또한 sys.indexes 카탈로그 뷰나 INDEXPROPERTY 함수를 사용하여 인덱스의 비활성 상태를 볼 수 있습니다. FOREIGN KEY 제약 조건과 CHECK 제약 조건의 비활성 상태는 각각 sys.foreign_keys 카탈로그 뷰와 sys.check_constraints 카탈로그 뷰에서 볼 수 있습니다. 자세한 내용은 인덱스 정보 보기를 참조하십시오.

예

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

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

참고 항목

개념

인덱스 비활성화
인덱스 및 제약 조건 활성화에 대한 지침

관련 자료

ALTER INDEX(Transact-SQL)
CREATE INDEX(Transact-SQL)
DBCC CHECKDB(Transact-SQL)
DBCC INDEXDEFRAG(Transact-SQL)
DBCC DBREINDEX(Transact-SQL)
NDEXPROPERTY(Transact-SQL)
sys.check_constraints(Transact-SQL)
sys.foreign_keys(Transact-SQL)
sys.indexes(Transact-SQL)

도움말 및 정보

SQL Server 2005 지원 받기