インデックスを無効にする場合のガイドライン

[アーティクル]
12/15/2008

インデックスを無効にすると、ユーザーはそのインデックスにアクセスできなくなります。クラスタ化インデックスの場合は、基になるテーブルのデータにもアクセスできなくなります。インデックスは、SQL Server のアップグレード時に SQL Server 2005 データベースエンジンにより自動的に無効にすることができます。また、手動で無効にすることもできます。詳細については、「インデックスの無効化」を参照してください。

インデックスはどの種類であっても無効にすることができます。インデックスの無効化には、次の規則が適用されます。

一意のインデックスの場合、PRIMARY KEY 制約または UNIQUE 制約と、他のテーブルのインデックス付き列を参照するすべての FOREIGN KEY 制約が無効になります。インデックスを無効にするユーザーには参照先テーブルに対する ALTER 権限が必要です。この権限がない場合、ALTER INDEX DISABLE ステートメントが失敗します。クラスタ化インデックスの場合、基になるテーブルを参照元または参照先とするすべての FOREIGN KEY 制約が無効になります。
インデックスを無効にすると、警告メッセージに制約名が表示されます。インデックスを再構築した後で、ALTER TABLE CHECK CONSTRAINT ステートメントを使用して制約を手動で有効にする必要があります。
無効なインデックスは保守されません。
クエリオプティマイザは、クエリの実行プランの作成時にインデックスを考慮しません。また、無効化されたインデックスをテーブルヒントで参照するクエリは失敗します。
インデックスの定義はメタデータに残るので、既存の無効化されたインデックスと同名のインデックスは作成できません。
無効化されたインデックスは削除できます。

非クラスタ化インデックスの無効化

非クラスタ化インデックスを削除すると、インデックスデータが物理的に削除されます。ただし、インデックスの定義はメタデータに残ります。非クラスタ化インデックスの無効化には、次のガイドラインも適用されます。

インデックスの統計はそのまま残り、必要なときに自動的に更新されます。
非クラスタ化インデックスは、関連付けられたクラスタ化インデックスを無効にすると自動的に無効になります。非クラスタ化インデックスを有効にするには、クラスタ化インデックスを有効にする (テーブルまたはビューの場合) か、クラスタ化インデックスを削除する (テーブルの場合) 必要があります。ALTER INDEX ALL REBUILD ステートメントを使用してクラスタ化インデックスを有効にした場合を除き、非クラスタ化インデックスは明示的に有効にする必要があります。詳細については、「インデックスと制約を有効にするためのガイドライン」を参照してください。

クラスタ化インデックスの無効化

クラスタ化インデックスの無効化には、次のガイドラインも適用されます。

無効化されたクラスタ化インデックスのデータ行には、クラスタ化インデックスを削除または再構築する場合以外はアクセスできません。これは、次のことを意味します。
- SELECT、UPDATE、DELETE、INSERT、CREATE INDEX、CREATE STATISTICS、UPDATE STATISTICS (対インデックス)、およびテーブル列または制約を変更する ALTER TABLE の各ステートメントの操作は失敗します。
- 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 制約の無効化には、次のガイドラインも適用されます。

PRIMARY KEY 制約および UNIQUE 制約を無効にするには、ALTER INDEX DISABLE ステートメントを使用して、関連付けられているインデックスを無効にします。
PRIMARY KEY 制約を無効にすると、関連付けられたすべての FOREIGN KEY 制約も無効になります。これは NOCHECK CONSTRAINT オプションを制約に設定した場合と同等の作用です。
参照先テーブルの ALTER 権限または CONTROL 権限が必要です。
CASCADE UPDATE または CASCADE 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 を指定すると、制約には再び信頼済みのマークが付きます。
次の例では、データとして許容される給与を制限する制約を無効にします。ALTER TABLE と共に 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;

参照

次の方法で共有