フィルター選択されたインデックスのデザイン ガイドライン

クエリに関連する少数の値だけが列に含まれている場合、値のサブセットにフィルター選択されたインデックスを作成できます。たとえば、列の値がほとんど NULL の場合に、クエリで常に NULL 以外の値を選択するときは、NULL 以外のデータ行にフィルター選択されたインデックスを作成できます。作成したインデックスは、同じキー列に定義されているテーブル全体の非クラスター化インデックスよりも小さく、メンテナンス コストが少なくなります。

たとえば、AdventureWorks2008R2 データベースには、2679 行の Production.BillOfMaterials テーブルがあります。EndDate 列では、NULL 以外の値を含む行は 199 行だけで、それ以外の 2480 行には NULL が含まれています。フィルター選択された次のインデックスは、インデックスで定義された列を返し、EndDate で NULL 以外の値を含む行のみを選択するクエリに対応します。

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

フィルター選択されたインデックス FIBillOfMaterialsWithEndDate は次のクエリに対して有効です。クエリ実行プランを表示して、クエリ オプティマイザーでフィルター選択されたインデックスが使用されたかどうかを確認できます。クエリ実行プランの表示方法の詳細については、「クエリの分析」を参照してください。

SELECT ProductAssemblyID, ComponentID, StartDate 
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL 
    AND ComponentID = 5 
    AND StartDate > '01/01/2008' ;
GO

フィルター選択されたインデックスの作成方法およびフィルター選択されたインデックスの述語式の定義方法の詳細については、「CREATE INDEX (Transact-SQL)」を参照してください。

テーブルに異種データの行が含まれている場合、1 つ以上のカテゴリのデータに対してフィルター選択されたインデックスを作成できます。

たとえば、Production.Product テーブルに示される製品がそれぞれ ProductSubcategoryID に割り当てられ、Bikes、Components、Clothing、Accessories の製品カテゴリに関連付けられています。Production.Product テーブル内にあるこうしたカテゴリの列の値はあまり密接に関連していないので、異種カテゴリとなります。たとえば、Color、ReorderPoint、ListPrice、Weight、Class、および Style の特性は、各製品カテゴリに対して固有です。サブカテゴリ 27 ～ 36 を含む Accessories に対して頻繁に使用されるクエリがあるとします。Accessories のサブカテゴリにフィルター選択されたインデックスを作成することで、Accessories に対するクエリのパフォーマンスを向上させることができます。

次の例では、Production.Product テーブルの Accessories のサブカテゴリに含まれるすべての製品にフィルター選択されたインデックスを作成します。

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIProductAccessories'
    AND object_id = OBJECT_ID ('Production.Product'))
DROP INDEX FIProductAccessories
    ON Production.Product;
GO
CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (ProductSubcategoryID, ListPrice) 
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

フィルター選択されたインデックス FIProductAccessories は次のクエリに対応します。

これは、クエリ結果がインデックスに含まれ、クエリ プランにベース テーブルの参照が含まれないためです。たとえば、クエリ述語式 ProductSubcategoryID = 33 はフィルター選択されたインデックスの述語 ProductSubcategoryID >= 27 および ProductSubcategoryID <= 36 のサブセットで、クエリ述語の ProductSubcategoryID 列と ListPrice 列はどちらもインデックスのキー列であり、名前は付加列としてインデックスのリーフ レベルに格納されます。

SELECT Name, ProductSubcategoryID, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;
GO

ビューは、クエリの定義を格納する仮想テーブルであり、フィルター選択されたインデックスよりも幅広い目的と機能を備えています。ビューの詳細については、「ビューについて」および「ビューの使用に関するシナリオ」を参照してください。次の表では、ビューで使用できる一部の機能を、フィルター選択されたインデックスの機能と比較しています。

*述語の単純な比較ロジックについては、「CREATE INDEX」の WHERE 句の構文を参照してください。

**述語の複雑な比較ロジックについては、「SELECT」の WHERE 句の構文を参照してください。

フィルター選択されたインデックスをビューに作成することはできません。ただし、クエリ オプティマイザーにとって、ビューで参照されているテーブルに定義されたフィルター選択されたインデックスは役立ちます。クエリ オプティマイザーでは、クエリ結果が正しくなる場合、ビューから選択するクエリに対してフィルター選択されたインデックスが検討されます。次の例では、開始日が 2000 年 4 月 1 日より後のビューと、開始日が 2000 年 8 月 1 日より後のフィルター選択されたインデックスを作成します。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('ViewOnBillOfMaterials') IS NOT NULL
DROP VIEW ViewOnBillOfMaterials;
GO
CREATE VIEW ViewOnBillOfMaterials AS 
SELECT ComponentID, StartDate, EndDate, StartDate + 2 AS ShipDate
FROM Production.BillOfMaterials
WHERE StartDate > '20000401';
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsByStartDate'
    AND object_ID = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsByStartDate 
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsByStartDate
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE StartDate > '20000801';
GO

次の例では、クエリによって 2004 年 9 月 1 日より後の開始日が選択されます。この場合、開始日はすべて、フィルター選択されたインデックスとフィルターを適用したビューに含まれます。フィルター選択されたインデックス FIBillOfMaterialsByStartDate にはクエリの正しい結果が含まれるので、クエリ オプティマイザーではこのインデックスが検討されます。

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040901';
GO

次の例では、クエリによって 2004 年 6 月 1 日より後の開始日が選択されます。この場合、開始日はすべて、ビューに含まれますが、フィルター選択されたインデックスには含まれません。クエリ オプティマイザーでは、フィルター選択されたインデックス FIBillOfMaterialsByStartDate が検討されません。これは、クエリがビューから選択する際に正しい結果が返されるのと対照的に、フィルター選択されたインデックスを使用すると、異なる結果が返される可能性があるためです。

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040601';
GO

フィルター選択されたインデックスは、インデックス付きビューよりも次の点で優れています。

• インデックスのメンテナンス コストの削減。たとえば、インデックス付きビューを更新する場合よりもフィルター選択されたインデックスを更新する場合の方が、クエリ プロセッサで使用する CPU リソースが少なくなります。

• プランの品質の向上。たとえば、クエリのコンパイル時、同等のインデックス付きビューよりも多くの状況でフィルター選択されたインデックスを使用することがクエリ オプティマイザーで検討されます。

• オンラインでのインデックス再構築。フィルター選択されたインデックスは、クエリで使用可能なときに再構築できます。オンラインでのインデックス再構築は、インデックス付きビューではサポートされていません。詳細については、「ALTER INDEX (Transact-SQL)」の REBUILD オプションの説明を参照してください。

• 一意ではないインデックス。フィルター選択されたインデックスは一意ではないインデックスにすることができますが、インデックス付きビューは一意である必要があります。

上記の理由から、可能であれば、インデックス付きビューではなくフィルター選択されたインデックスを使用することをお勧めします。インデックス付きビューではなくフィルター選択されたインデックスを使用できるのは、ビューでテーブルを 1 つだけ参照する、クエリにより計算列が返されない、およびビューの述語で単純な比較ロジックを使用するという条件が満たされる場合です。たとえば、次の述語式は LIKE 演算子を含んでいるため、ビュー定義では使用できますが、フィルター選択されたインデックスでは使用できません。

WHERE StartDate > '20040701' AND ModifiedDate LIKE 'E%'

フィルター選択されたインデックスの定義に少数のキーまたは付加列を含めること、およびフィルター選択されたインデックスをクエリ オプティマイザーによってクエリ実行プランで選択するために必要な列だけを組み込むことをお勧めします。クエリ オプティマイザーでは、フィルター選択されたインデックスがクエリに対応するかどうかに関係なく、フィルター選択されたインデックスがクエリに対して選択されます。ただし、フィルター選択されたインデックスがクエリに対応する場合は、そのインデックスが選択される可能性は高くなります。クエリへの対応の詳細については、「付加列インデックスの作成」を参照してください。

場合によっては、フィルター選択されたインデックスは、その式の列をキー列または付加列としてフィルター選択されたインデックスの定義に含めなくても、クエリに対応します。次のガイドラインでは、フィルター選択されたインデックスの式の列をフィルター選択されたインデックスの定義でキー列または付加列にする必要がある場合について説明します。次の例では、以前に作成したフィルター選択されたインデックス FIBillOfMaterialsWithEndDate を使用します。

フィルター選択されたインデックスの式がクエリ述語と同じであり、フィルター選択されたインデックスの式の列がクエリ結果と共に返されない場合、その式の列を、フィルター選択されたインデックスの定義でキー列または付加列にする必要はありません。たとえば、クエリ述語がフィルター式と同じであり、EndDate がクエリ結果と共に返されないため、FIBillOfMaterialsWithEndDate は次のクエリに対応します。FIBillOfMaterialsWithEndDate は、フィルター選択されたインデックスの定義のキー列または付加列として EndDate を必要としません。

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

フィルター選択されたインデックスの式と異なるクエリ述語で比較に列が使用される場合は、フィルター選択されたインデックスの式の列を、フィルター選択されたインデックスの定義でキー列または付加列にする必要があります。たとえば、FIBillOfMaterialsWithEndDate は、フィルター選択されたインデックスから行のサブセットを選択するので、次のクエリに対して有効です。ただし、EndDate が比較 EndDate > '20040101' で使用されるため、次のクエリには対応していません。この比較は、フィルター選択されたインデックスの式と異なります。クエリ プロセッサでは、EndDate の値を参照せずにこのクエリを実行することはできません。したがって、EndDate をフィルター選択されたインデックスの定義でキー列または付加列にする必要があります。

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20040101';
GO

フィルター選択されたインデックスの式の列がクエリ結果セットに含まれる場合、その列をフィルター選択されたインデックスの定義でキー列または付加列にする必要があります。たとえば、FIBillOfMaterialsWithEndDate はクエリ結果に含まれる EndDate 列を返すので、次のクエリに対応しません。したがって、EndDate をフィルター選択されたインデックスの定義でキー列または付加列にする必要があります。

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

テーブルのクラスター化インデックス キーは、フィルター選択されたインデックスの定義でキー列または付加列にする必要はありません。クラスター化インデックス キーは、フィルター選択されたインデックスなど、すべての非クラスター化インデックスに自動的に含まれます。

フィルター選択されたインデックスでは、その式に指定された比較演算子によって暗黙的または明示的なデータ変換が行われる場合、変換が比較演算子の左辺で行われると、エラーが発生します。解決方法としては、比較演算子の右辺にデータ変換演算子 (CAST または CONVERT) を含む、フィルター選択されたインデックスの式を記述します。

次の例では、さまざまなデータ型が含まれるテーブルを作成します。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.TestTable') IS NOT NULL
DROP TABLE dbo.TestTable;
GO
CREATE TABLE TestTable (a int, b varbinary(4));
GO

次のフィルター選択されたインデックスの定義では、列 b は、定数 1 と比較するために、整数データ型に暗黙的に変換されます。これにより、フィルター選択された述語の演算子の左辺で変換が行われるため、エラー メッセージ 10611 が生成されます。

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name from sys.indexes 
    WHERE name = N'TestTabIndex'
    AND object_id = OBJECT_ID (N'dbo.TestTable'))
DROP INDEX TestTabIndex on dbo.TestTable
GO
CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;
GO

解決策として、次の例に示すように、右辺の定数を、列 b と同じ型になるように変換します。

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);
GO

データ変換を比較演算子の左辺から右辺に移動すると、変換の意味が変わることがあります。上記の例では、CONVERT 演算子を右側に追加したときに、整数の比較から varbinary の比較に変わりました。

sys.sql_expression_dependencies カタログ ビューでは、フィルター選択されたインデックスの式の各列を、参照による依存関係として追跡します。フィルター選択されたインデックスの式で定義されているテーブル列の定義を削除、名前変更、または変更することはできません。

フィルター選択されたインデックスは、クエリが SELECT ステートメントで参照する、適切に定義されたデータのサブセットが列に含まれている場合に役立ちます。次に例を示します。

• NULL 以外の値を少数しか含まないスパース列。

• 複数のカテゴリのデータを含む異種列。

• 金額、時間、日付など、値の範囲を含む列。

• 列の値の単純な比較ロジックで定義されるテーブル パーティション。

フィルター選択されたインデックスのメンテナンス コストの削減は、そのインデックスに含まれる行数がテーブル全体のインデックスと比較して少ない場合に、最も明確になります。フィルター選択されたインデックスにテーブル内のほとんどの行が含まれる場合は、テーブル全体のインデックスよりもメンテナンス コストがかかることがあります。この場合は、フィルター選択されたインデックスではなく、テーブル全体のインデックスを使用する必要があります。

フィルター選択されたインデックスは 1 つのテーブルで定義され、単純な比較演算子のみをサポートします。複数のテーブルを参照するフィルター式や複雑なロジックを含むフィルター式が必要な場合は、ビューを作成する必要があります。

一般に、データベース エンジンとツールでは、フィルター選択されたインデックスを特殊な非クラスター化インデックスと見なすので、テーブル全体の非クラスター化インデックスの場合と同じように、フィルター選択されたインデックスをサポートします。フィルター選択されたインデックスを完全にサポートする、サポートしない、またはサポートが制限されているツールと機能に関する注意事項を次に示します。

• ALTER INDEX はフィルター選択されたインデックスをサポートします。フィルター選択されたインデックスの式を変更するには、CREATE INDEX WITH DROP_EXISTING を使用します。

• 欠落したインデックス機能では、フィルター選択されたインデックスを推奨しません。

• データベース エンジン チューニング アドバイザーでは、インデックス チューニング アドバイスの推奨時に、フィルター選択されたインデックスが検討されます。フィルター選択されたインデックス is not null が推奨されることもあります。

• オンライン インデックス操作では、フィルター選択されたインデックスがサポートされます。

• テーブル ヒントでは、フィルター選択されたインデックスがサポートされますが、これ以外のインデックスには適用されない制限がいくつかあります。これらの制限については、次のセクションで説明します。

パラメーター化クエリには、コンパイル時に、クエリ オプティマイザーがフィルター選択されたインデックスを選択するのに十分な情報が含まれていない場合もあります。クエリを書き直して不足情報を提供することができる場合があります。次の例では、@p および @q のパラメーター値がコンパイル時に確定していないため、クエリ オプティマイザーでは、SELECT ステートメントに対してフィルター選択されたインデックス FIBillOfMaterialsWithComponentID を検討しません。次に示すクエリの例は、SHOWPLAN_XML が ON に設定された状態で実行されるので、パラメーター化クエリの一致しないフィルター選択されたインデックスを SHOWPLAN_XML 出力に表示できます。

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithComponentID'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE ComponentID IN (533, 324, 753);
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID from Production.BillOfMaterials 
WHERE ComponentID = @p OR ComponentID = @q;
GO
SET SHOWPLAN_XML OFF;
GO

SHOWPLAN_XML 出力の UnmatchedIndexes 要素と Parameterization サブ要素は、フィルター選択されたインデックスがクエリに一致しなかったことを示します。SHOWPLAN_XML 出力の表示方法については、「XML プラン表示」を参照してください。

この問題を解決するには、パラメーター化された式がフィルター述語のサブセットではない場合にクエリ結果が空になるように、クエリを変更します。次のクエリでは、この変更を示します。WHERE 句に ComponentID in (533, 324, 753) 式を追加すると、クエリ結果は確実にフィルター選択された述語式のサブセットになります。この変更により、クエリ オプティマイザーは、次の SELECT ステートメントに対してフィルター選択されたインデックス FIBillOfMaterialsWithComponentID を検討できるようになります。

USE AdventureWorks2008R2;
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WHERE ComponentID in (533, 324, 753)
    AND (ComponentID = @p OR ComponentID = @q);
GO
SET SHOWPLAN_XML OFF;
GO

簡易パラメーター化

クエリ オプティマイザーでは、クエリに対応しない場合でも、キー参照を実行して、フィルター選択されたインデックスが対応しない残りの列を取得することで、フィルター選択されたインデックスを使用できます。キー参照の詳細については、「Key Lookup プラン表示操作」を参照してください。キー参照の推定数が少ない場合、クエリ オプティマイザーでは、この方法が選択されることがあります。次のクエリでは、インデックス ヒントを使用し、EndDate のブックマーク参照と共に FIBillOfMaterialsWithEndDate をクエリ プロセッサで使用します。キー参照は、クエリ述語の EndDate > @date 比較に対して行われます。

USE AdventureWorks2008R2;
GO
DECLARE @date AS DATE;
SET @date = '20040825'
SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WITH ( INDEX (FIBillOfMaterialsWithEndDate) )
WHERE EndDate > @date;
GO

EndDate > @Date はフィルター選択されたインデックスの式 EndDate IS NOT NULL と完全一致ではないことに注意してください。フィルター選択されたインデックスの式で定義された行のサブセットが返されるので、フィルター選択されたインデックスは、このパラメーター化クエリに対して引き続き有効です。