SQL Server 2000 のインデックス付きビューによるパフォーマンスの向上

Gail Erickson (著者)
Lubor Kollar (助言者)
Jason Ward (助言者)
Microsoft Corporation

September 2000
日本語版最終更新日 2001年1月24日

要約 : この記事では、SQL Server 2000 Enterprise Edition の新機能であるインデックス付きビューについて説明します。インデックス付きビューとパフォーマンスを向上する使用例について説明します。

内容

インデックス付きビューとは
   インデックス付きビューによるパフォーマンスの向上
インデックス付きビューを使用する利点
   クエリ オプティマイザによるインデックス付きビューの使用
設計に関する考慮事項
   設計ガイドライン
   インデックス チューニング ウィザードの使用
   インデックス付きビューの管理
インデックス付きビューの作成
   整合性のある結果を得るための SET オプションの使用
   決定的関数の使用
   その他の要件
例
詳細情報

インデックス付きビューとは

Microsoft® SQL Server では長年にわたり、ビューとして知られている仮想テーブルの作成機能をサポートしてきました。従来のこれらのビューは 2 つの主な目的を果たすために提供されています。

1. ユーザーの利用を、基本テーブルのデータの一部に制限するセキュリティ メカニズムを提供するため。

2. ユーザーが基本テーブル内に格納されているデータを論理的に参照する方法を、開発者がカスタマイズできるメカニズムを提供するため。

SQL Server 2000 では、SQL Server のビュー機能はシステム パフォーマンスの向上を実現するように拡張されています。ビューでは一意のクラスタ化インデックスおよび非クラスタ化インデックスを作成し、最も複雑なクエリでのデータ アクセス パフォーマンスを向上させることができます。SQL Server 2000 では、一意のクラスタ化インデックスを使用したビューをインデックス付きビューと呼びます。

注 : インデックス付きビューは、SQL Server 2000 Enterprise Edition および SQL Server 2000 Developer Edition のみの機能です。

データベース管理システム (DBMS) から見ると、ビューはデータの記述 (メタ データ) です。一般的なビューを作成した場合、メタ データは、仮想テーブルとして表される結果セットを定義する SELECT ステートメントのカプセル化によって定義されます。別のクエリの FROM 句でビューを参照している場合は、ビューを参照する代わりにこのメタ データがシステム カタログから取得され、展開されます。ビューの展開後、クエリ オプティマイザによって、実行するクエリの単一の実行プランがコンパイルされます。

インデックスのないビューの場合、ビューは実行時に具体化されます。結合や集計などの計算は、ビューを参照している各クエリの実行時に行われます。ビューは必ずしも完全に具体化されている必要はありません。クエリには、そのビューが参照しているテーブルやほかのビューに適用されるその他の述語、結合、または集計を含めることができます。一意のクラスタ化インデックスをビューで作成すると、ビューの結果セットはすぐに具体化され、データベースの物理ストレージに保存されます。これによって、具体化というコストのかかる操作を実行時に行うと発生するオーバーヘッドをなくします。

インデックス付きビューをクエリ実行に使用する方法は 2 種類あります。まず、クエリはインデックス付きビューを直接参照することができます。さらに重要なこととして、クエリ オプティマイザは、あるビューがクエリの一部またはすべてとして代用でき、低コストなクエリ プランであることが分かった場合に、そのビューを選択することができます。2 番目の方法として、インデックス付きビューを基のテーブルおよび通常のインデックスの代わりに使用することができます。クエリ実行時にクエリ オプティマイザが使用できるように、クエリでビューを参照する必要はありません。したがって、既存のアプリケーションを変更せずに、新しく作成したインデックス付きビューを使用できるようになります。

インデックス付きビューによるパフォーマンスの向上

インデックスを使用してクエリのパフォーマンスを向上する方法は以前からありますが、インデックス付きビューは、標準のインデックスでは不可能なパフォーマンスのさらなる向上を実現します。インデックス付きビューは次の方法でクエリ パフォーマンスを向上します。

• クエリ実行時のコストのかかる計算を最小限に抑えるため、集計を事前に計算し、インデックスに格納します。

• テーブルを事前に結合し、結果のデータ セットを格納します。

• 結合や集計の組み合わせを格納します。

次のグラフは、クエリ オプティマイザでインデックス付きビューを使用した場合に達成される典型的なパフォーマンスの向上例を示しています。使用されたクエリは、集計計算数、使用テーブル数、述語数などが広範囲に及ぶ複雑なもので、実際の稼働環境で使用されている何百万行もの巨大なテーブルを含んでいます。

図 1. クエリ オプティマイザでインデックス付きビューを使用した場合に達成される典型的なパフォーマンスの向上例

ビューでのセカンダリ インデックスの使用

ビューの非クラスタ化セカンダリ インデックスは、クエリ パフォーマンスをさらに向上します。テーブルのセカンダリ インデックスと同様、ビューのセカンダリ インデックスは、コンパイル処理の際に選択可能なオプションをクエリ オプティマイザに提供します。たとえば、クラスタ化インデックスで扱っていない列がクエリに含まれている場合、オプティマイザはクエリ プランから 1 つまたは複数のセカンダリ インデックスを選択し、時間のかかるインデックス付きビューや基本テーブルに対するフル スキャンを回避します。

スキーマにインデックスを追加すると、継続的な管理が必要になるため、データベースにおけるオーバーヘッドが増加します。十分に検討し、インデックスと管理オーバーへッドの適切なバランスを見いだす必要があります。

インデックス付きビューを使用する利点

インデックス付きビューを実装する前に、データベースの作業負荷を分析します。クエリに関する知識だけでなく、SQL プロファイラなどのさまざまなツールを使用して、インデックス付きビューによってパフォーマンスの向上するクエリを特定します。集計や結合が頻繁に行われる場合は、インデックス付きビューに最適であると言えます。

インデックス付きビューを使用することですべてのクエリのパフォーマンスが向上するわけではありません。通常のインデックスと同様、インデックス付きビューを使用しなければ、パフォーマンスは変化しません。この場合、パフォーマンスの変化がないだけでなく、ディスク領域、保守、および最適化などの追加コストが発生します。しかし、インデックス付きビューを使用した場合、データ アクセスに関してはかなりの向上が見られます。これは、クエリ オプティマイザが、インデックス付きビューに格納されている事前に計算された結果を使用することで、クエリ実行コストを大幅に削減するからです。

クエリ オプティマイザは、コストへの影響が大きいクエリについてのみインデックス付きビューの使用を検討します。これによって、クエリ オプティマイザがさまざまなインデックス付きビューを照合しようとして発生するコストにより、インデックス付きビューを使用することで節約できるコストが相殺されてしまう状況が回避できます。コストが 1 未満であるクエリでインデックス付きビューが使用されることはほとんどありません。

以下に対してインデックス付きビューを実装するとパフォーマンスが向上します。

• 意思決定支援作業

• データ マート

• OLAP (Online Analytical Processing) ストアとソース

• データ マイニング作業

クエリの種類やパターンの視点から見ると、以下を含むクエリはパフォーマンスが向上する種類として特徴付けることができます。

• サイズの大きいテーブルの結合および集計

• クエリのパターンの繰り返し

• 同じ列または一部重複する列の集計の繰り返し

• 同じキーによる同じテーブルの結合の繰り返し

• 上記の組み合わせ

反対に、書き込み数が多いオンライン トランザクション処理 (OLTP) システムや、頻繁に更新されるデータベースの場合は、インデックス付きビューを使用することでパフォーマンスを向上することはできません。ビューと基の基本テーブルの両方の更新に関わる保守コストが増加するからです。

クエリ オプティマイザによるインデックス付きビューの使用

SQL Server のクエリ オプティマイザは、所定のクエリ実行に対してインデックス付きビューがいつ使用できるかを自動的に判断します。オプティマイザがクエリ実行プランでビューを使用する際、ビューがクエリで直接参照されている必要はありません。したがって、既存のアプリケーションそのものを変更しなくても、インデックス付きビューを使用することでパフォーマンスを向上させることができます。この場合は、インデックス付きビューだけを作成します。

オプティマイザによる検討事項

クエリ オプティマイザは、インデックス付きビューがクエリの一部または全体に対応できるかどうかを調べるために、いくつかの条件を検討します。これらの条件は、クエリの FROM 句に対応しており、以下が含まれます。

• クエリの FROM 句のテーブルは、インデックス付きビューの FROM 句のテーブルのスーパーセットであること。

• クエリの結合条件は、ビューの結合条件のスーパーセットであること。

• クエリの集計列は、ビューの集計列のサブセットであること。

• クエリの選択リスト内のすべての式は、ビューの選択リストまたはビュー定義に含まれていないテーブルから派生可能であること。

• クエリの検索条件の述語は、ビュー定義の検索条件の述語のスーパーセットであること。ビューの検索述語の各結合は、クエリの検索述語の結合と同じフォームで表示されること。

• クエリの検索条件の述語にあるすべての列で、ビュー定義のテーブルに属するものは、以下で使用されていること (複数可)。

  • ビュー定義の同じ述語。

  • GROUP BY リスト。

  • GROUP BY がない場合は、ビューの選択リスト。

サブクエリ、派生テーブル、UNION など、クエリに複数の FROM 句が含まれている場合、オプティマイザは、複数のインデックス付きビューを選択して、複数の FROM 句を使用したクエリを管理することができます。

注 : 例外として、オプティマイザがサブクエリを結合したり、派生テーブルを結合に変換して、2 つの FROM 句を 1 つにまとめることがあります。この場合、まとめられたインデックス付きビューは元のクエリに含まれる複数の FROM 句に対応することができます。

これらの条件を説明するクエリの例は、この文書の後半で紹介します。最適な使用方法として、クエリ実行プランではクエリ オプティマイザが、使用するインデックスを決定できるようにすることをお勧めします。

NOEXPAND オプションの使用

NOEXPAND オプションにより、クエリ オプティマイザに対して、ビューを強制的に通常のテーブル (クラスタ化インデックスを使用したもの) のように扱わせることができます。この場合、インデックス付きビューは、FROM 句で直接参照されていなければなりません。たとえば、次のようになります。

  SELECT Column1, Column2, ... FROM Table1, View1 WITH (NOEXPAND)WHERE ...

EXPAND VIEWS オプションの使用

または、クエリの最後部に EXPAND VIEWS オプションを使用することで、インデックス付きビューを明示的に除外することができます。たとえば、次のようになります。

  SELECT Column1, Column2, ... FROM Table1, View1 WHERE ...OPTION (EXPAND VIEWS)

このオプションを使用した場合、クエリ オプティマイザは、クエリの参照列を含める低コストな方法を見積もる際に、すべてのビューのインデックスを無視します。

設計に関する考慮事項

データベース システムに最適なインデックスの特定は複雑な場合があります。通常のインデックスを設計する際も、さまざまな可能性について検討しますが、スキーマにインデックス付きビューを追加することで、設計の複雑さおよび考えられる結果は大幅に増加します。たとえば、インデックス付きビューは次のような状況で使用します。

• クエリで参照されるテーブルのサブセット。

• 上記のテーブルのサブセットに対するクエリ条件のサブセット。

• グループ列。

• SUM などの集計関数。

テーブルのインデックスおよびインデックス付きビューは、各結合から最適な結果が得られるように、同時に設計する必要があります。インデックスとインデックス付きビューはいずれも、所定のクエリに対しては有効であるため、これらを別々に設計すると、ストレージや管理における無駄なオーバーヘッドが発生することになります。データベースの物理設計の調整時には、さまざまなクエリのパフォーマンス要件と、データベース システムでサポートすべき更新要件とをトレードオフする必要があります。したがって、インデックス付きビューに適切な物理設計の特定は困難な作業であるため、できるだけインデックス チューニング ウィザードを使用します。

クエリ オプティマイザが特定のクエリに対して検討するインデックス付きビューが多数ある場合、クエリの最適化コストはかなり増加します。クエリ オプティマイザは、クエリ内のテーブルのサブセットについて定義されているすべてのインデックス付きビューを検討します。つまり、不適切なビューを除外するために、すべてのビューを解析し、代用できるビューがないか調査しなければなりません。特に、所定のクエリに対してこのようなビューが何百もある場合は、それだけ時間がかかる可能性があります。

一意のクラスタ化インデックスを作成するには、ビューがいくつかの要件を満たしている必要があります。設計段階では、次の要件について検討します。

• ビュー、およびビューで参照されるすべてのテーブルが同じデータベース内に存在しており、所有者が同じである必要があります。

• オプティマイザで使用するクエリが参照しているすべてのテーブルが、インデックス付きビューに含まれている必要はありません。

• ビューでほかのインデックスを作成する前に、一意のクラスタ化インデックスを作成します。

• 基本テーブル、ビュー、およびインデックスを作成するとき、また、基本テーブルのデータやビューを変更するときは、SET オプション (後で説明します) を正確に設定しなければなりません。これらの SET オプションが正しくない場合、クエリ オプティマイザはインデックス付きビューを考慮しません。

• ビューはスキーマ バインドを使用して作成します。ビューで参照されるユーザー定義関数は SCHEMABINDING オプションを使用して作成します。

• インデックス付きビューで定義されたデータを保持するためのディスク領域が必要になります。

設計ガイドライン

インデックス付きビューの設計時には、次のガイドラインを考慮します。

• 複数のクエリまたは複数の操作で使用できるインデックス付きビューを設計します。

  たとえば、ある列の SUM と COUNT_BIG を含むインデックス付きビューは、SUM、COUNT、COUNT_BIG、または AVG の各関数を含むクエリで使用できます。この場合、AVG 関数の実行に必要な計算の一部もすでに行われているため、あとは (基本テーブルのすべての行ではなく) ビューの数行のみを取得するだけで、クエリのパフォーマンスは向上します。

• インデックスのサイズが小さくなるようにします。

  できるだけ使用する列およびバイト数を少なくすることで、オプティマイザによる行データの検索効率を高めることができます。反対に、定義したクラスタ化インデックス キーのサイズが大きいと、ビューで定義されているセカンダリ非クラスタ化インデックスも、非常に大きなサイズになります。これは、非クラスタ化インデックスのエントリには、インデックスで定義されている列だけでなくクラスタ化キーも含まれるためです。

• 最終的なインデックス付きビューのサイズを検討します。

  純粋な集計の場合、ビューのサイズが元のテーブルのサイズと同じ程度のときは、インデックス付きビューを使用しても、パフォーマンスの大幅な変化は見られません。

• 処理の一部を高速化する、サイズの小さい複数のインデックス付きビューを設計します。

  必ずしも、クエリ全体に対応するインデックス付きビューを作成できるとは限りません。その場合は、それぞれがクエリの一部を実行する複数のインデックス付きビューを作成することを検討します。

  次の例について考えてみます。

  • 2 つのデータベースのデータを集計し、それぞれの結果を結合する、頻繁に実行されるクエリがあるとします。インデックス付きビューでは、複数のデータベースのテーブルを参照することができないため、すべての処理を実行する単一のビューを作成することはできません。ただし、各データベースに、それぞれのデータベースの集計を行うインデックス付きビューを作成することはできます。オプティマイザが既存のクエリに対応するインデックス付きビューを使用できる場合、少なくとも集計処理は高速になり、既存のクエリを再コード化する必要はありません。結合処理は高速にはなりませんが、インデックス付きビューに格納されている集計が使用されるため、クエリ全体は高速になります。

  • 複数のテーブルのデータを集計し、その後 UNION を使用して結果を結合する、頻繁に実行されるクエリがあるとします。UNION はインデックス付きビューでは使用できません。したがって、個々の集計を実行するビューを作成します。オプティマイザがこれらのインデックス付きビューを選択すると、クエリは高速になります。クエリを再コード化する必要はありません。UNION 処理に変化はありませんが、個々の集計処理のパフォーマンスが向上します。

インデックス チューニング ウィザードの使用

インデックス チューニング ウィザードは、基本テーブルのインデックスに加え、インデックス付きビューを推奨します。ウィザードは、インデックスおよびインデックス付きビューの組み合わせを決める際に役立ちます。これらのインデックスおよびインデックス付きビューによって、データベースに対して実行されるさまざまな組み合わせのクエリのパフォーマンスが最適化できます。

インデックス チューニング ウィザードは、結果セットが正しくなる上で必要な SET オプションをすべて要求するため、正確なインデックス付きビューが作成されます。ただし、アプリケーションによってはオプションが設定されていないと、ビューを利用できない場合があります。たとえば、インデックス付きビューの定義に使用されているテーブルに対する挿入、更新、または削除は、失敗する可能性があります。

インデックス付きビューの管理

SQL Server では、インデックス付きビューはほかのインデックスと同じように自動的に管理されます。通常のインデックスの場合、各インデックスは単一のテーブルに直接結合されます。基本テーブルに対して INSERT、UPDATE、または DELETE 操作が行われる場合は、インデックスに格納されている値がテーブルの値と常に一致するように、インデックスも更新されます。

インデックス付きビューの場合の管理も同様です。ただし、ビューが複数のテーブルを参照している場合は、いずれかのテーブルを更新したら、インデックス付きビューも更新しなければなりません。通常のインデックスとは異なり、関与しているテーブルのいずれかに 1 行挿入した場合、インデックス付きビューに複数の行を挿入することになる場合があります。これは、更新や削除の場合にも当てはまります。したがって、インデックス付きビューの管理は、テーブルのビューの管理よりもコストがかかる可能性があります。

SQL Server 2000 では、一部のビューを更新することができます。ビューを更新できる場合、基本テーブルは、INSERT、UPDATE、および DELETE を使用してビューから直接変更されます。ビューにインデックスを作成しても、ビューを更新することはできます。更新可能なビューの詳細については、『Microsoft SQL Server 2000 Books Online』の「ビューを使用したデータ変更」を参照してください。

管理コストに関する考慮事項

インデックス付きビューの作成時には、以下の点について検討します。

• データベースにインデックス付きビューのための領域が必要になります。インデックス付きビューの結果セットは、通常のテーブルの保管と同じように、データベース内に物理的に格納されます。

• ビューは SQL Server によって自動的に管理されます。したがって、ビューが定義されている基本テーブルが変更された場合は、ビューのインデックスにも変更が行われます。このため、管理の追加オーバーヘッドが発生します。

ビューによって実現される最終的なパフォーマンスの向上は、ビューによって得られるクエリ実行における節約と、ビューを格納し管理するコストの差分です。

ビューが使用するストレージを見積もるのは比較的簡単です。SQL クエリ アナライザ ツールの推定実行プランの表示を使用してビュー定義に含まれる SELECT ステートメントを評価します。このツールは、クエリによって返される行数と行のサイズを概算します。これらの 2 つの値を互いに掛けることで、ビューのおおよそのサイズを見積もることができます。ただし、これはあくまでも概数です。ビューのインデックスの実際のサイズは、ビューにインデックスを作成しなければ正確に知ることはできません。

SQL Server が行う自動管理の検討の観点からすると、推定実行プランの表示機能を使用することで、このオーバーヘッドの影響を見積もることができます。ビューを変更するステートメント (ビューに対する UPDATE や基本テーブルに対する INSERT) は、SQL クエリ アナライザにより評価され、ステートメントの管理操作は SHOWPLAN によって追加されます。このコストと実際の稼働環境でこの操作が発生する回数とを併せて考慮すると、ビュー管理で発生する可能性があるコストがわかります。

一般には、ビューや基本テーブルへの変更や更新は、できるだけ単独で行わずにバッチで行うことをお勧めします。このようにすることで、ビューの管理オーバーヘッドを削減できます。

インデックス付きビューの作成

インデックス付きビューの作成に必要な手順は、ビューを確実に実装する上で重要です。

1. ビューで参照されるすべての既存のテーブルに対する SET オプションが正確であることを確認します。

2. 新しいテーブルやビューを作成する前に、セッションの SET オプションが正確に設定されていることを確認します。

3. ビューの定義が明確であることを確認します。

4. WITH SCHEMABINDING オプションを使用してビューを作成します。

5. ビューに一意のクラスタ化インデックスを作成します。

整合性のある結果を得るための SET オプションの使用

同じ式を評価しても、クエリの実行時に異なる SET オプションがアクティブになっていると、SQL Server での結果が異なる可能性があります。たとえば、SET オプション CONCAT_NULL_YIELDS_NULL を ON に設定している場合、式 'abc' + NULL は値 NULL を返します。しかし、CONCAT_NULL_YIEDS_NULL を OFF に設定すると、同じ式でも結果は 'abc' になります。インデックス付きビューでは、ビューを正しく管理し整合性のある結果が返るようにするために、いくつかの SET オプションに対して固定値を設定する必要があります。

以下の条件に当てはまる場合、次の表の SET オプションは "必要な値" の列に示されている値に設定する必要があります。

• インデックス付きビューを作成する場合。

• インデックス付きビューで参照されるテーブルに対して INSERT、UPDATE、または DELETE 操作を行う場合。

• クエリ オプティマイザがインデックス付きビューを使用してクエリ プランを作成する場合。

SET オプション	必要な 値	既定の サーバーの 値	OLE DB および ODBC の値	DB LIB の 値
ANSI_NULLS	ON	OFF	ON	OFF
ANSI_PADDING	ON	ON	ON	OFF
ANSI_WARNING	ON	OFF	ON	OFF
ARITHABORT	ON	OFF	OFF	OFF
CONCAT_NULL_YIELDS_NULL	ON	OFF	ON	OFF
NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
QUOTED_IDENTIFIER	ON	OFF	ON	OFF

OLE DB または ODBC サーバー接続を使用している場合は、ARITHABORT 設定の値だけを変更します。すべての DB LIB 値は、サーバー レベルで sp_configure を使用するか、アプリケーションから SET コマンドを使用して正確に設定する必要があります。SET オプションの詳細については、『Microsoft SQL Server 2000 Books Online』の「SQL Server でのオプションの使用」を参照してください。

決定的関数の使用

インデックス付きビューの定義は決定的でなければなりません。選択リスト、WHERE 句、および GROUP BY 句のすべての式が決定的であれば、ビューは決定的に定義されます。特定の入力値セットを使用して評価した場合、決定的な式であれば必ず同じ結果が返されます。決定的な式には、決定的な関数のみが含まれます。たとえば、DATEADD 関数は、3 種類のパラメータの所定の引数値セットに対して、常に同じ結果を返すため決定的です。GETDATE 関数は、常に同じ引数を使用して呼び出されますが、返される値は実行した時刻によって異なるため、決定的ではありません。詳細については、『Microsoft SQL Server 2000 Books Online』の「決定的関数と非決定的関数」を参照してください。

式が決定的でも、浮動小数点式が含まれている場合は、正確な結果はプロセッサ アーキテクチャやマイクロコードのバージョンに依存します。SQL Server 2000 におけるデータの整合性を確実にするには、このような式をインデックス付きビューの非キー列でのみ使用するようにします。浮動小数点式を含まない決定的な式を、正確な式と呼びます。正確で明確な式のみを、インデックス付きビューのキー列および WHERE 句または GROUP 句に含めることができます。

ビューの列が決定的かどうかを調べるには、COLUMNPROPERTY 関数と IsDeterministic プロパティを使用します。スキーマ バインドを使用したビューの決定的な列が正確かどうかを調べるには、COLUMNPROPERTY 関数と IsPrecise プロパティを使用します。COLUMNPROPERTY は TRUE の場合は 1 を、FALSE の場合は 0 を返し、入力が無効な場合 (列が不明確な場合) は、NULL を返します。たとえば、SELECT COLUMNPROPERTY(Object_Id('Vdiscount1'),'SumDiscountPrice','IsPrecise') は、SumDiscountPrice 列が Order Details テーブルにある浮動小数点型の Discount 列を参照しているため、0 を返します。同じビューの SumPrice 列の場合は、決定的かつ正確です。

注 : この SELECT ステートメントが基づいているビューは、この後の例のビュー 1 を参照してください。

その他の要件

設計ガイドライン、「整合性のある結果を得るための SET オプションの使用」および「決定的関数の使用」に示した要件に加え、次の要件も満たす必要があります。

基本テーブルの要件

• 基本テーブルには、テーブル作成時に SET オプションを正確に設定します。正確に設定しないと、スキーマ バインドを使用しているビューから参照することができません。

• テーブルは、ビュー定義では 2 つの部分で構成される名前 (owner.tablename) を使用して参照します。

関数の要件

• ユーザー定義関数は、WITH SCHEMABINDING オプションを使用して作成します。

• ユーザー定義関数は 2 つの部分で構成される名前 (owner.function) を使用して参照します。

ビューの要件

• ビューは WITH SCHEMABINDING オプションを使用して作成します。

• ビューは、ほかのビューではなく同じデータベース内の基本テーブルしか参照できません。

構文の要件

ビュー定義の構文にはいくつかの制限があります。ビュー定義に以下を含めることはできません。

• COUNT(*)

• ROWSET 関数

• 派生テーブル

• 自己結合

• DISTINCT

• STDEV、VARIANCE、AVG

• Float*、text、ntext、image 列

• サブクエリ

• 全文述語 (CONTAIN、FREETEXT)

• NULL 値を許容する式に対する SUM

• MIN、MAX

• TOP

• 外部結合

• UNION

注 : インデックス付きビューに浮動小数点列を含めることはできますが、このような列をクラスタ化インデックス キーに含めることはできません。

GROUP BY の制約

GROUP BY を使用していない場合は、選択リストで式を使用することはできません。

GROUP BY が使用されている場合は、VIEW 定義では次の事項を考慮する必要があります。

• COUNT_BIG(*) を含める必要があります。

• HAVING、CUBE、または ROLLUP を含めることはできません。

これらの制約は、インデックス付きビューの定義にのみ適用されます。インデックス付きビューがこれらの GROUP BY 制約を満たしていなくても、クエリの実行プランで使用することはできます。

インデックスの要件

• CREATE INDEX ステートメントを実行するユーザーはビューの所有者でなければなりません。

• ビュー定義に GROUP BY 句が含まれている場合、一意のクラスタ化インデックスのキーは GROUP BY 句で指定されている列のみを参照できます。

例

ここでは、集計と結合という 2 つの主なクエリ グループを使用したインデックス付きビューの使用例を紹介します。インデックス付きビューが適用可能かどうかを調べる際に、クエリ オプティマイザが使用する条件についても説明します。この条件の詳細については、「クエリ オプティマイザによるインデックス付きビューの使用」を参照してください。

クエリは SQL Server 2000 に付属のサンプル データベース Northwind のテーブルに基づいており、このまま実行することができます。ビューの作成前でも作成後でも、SQL クエリ アナライザの [実行プランの表示] ツールを使用して、クエリ オプティマイザが選択したプランを表示できます。この例は、オプティマイザがどのように低コストな実行プランを選択するかを示すものですが、Northwind データベースはパフォーマンスの向上を示すにはサイズが小さすぎます。

次のクエリは、Order Details テーブルから合計割引率の最も大きい 5 つの製品を返す 2 とおりの方法を示しています。

クエリ 1

  SELECT TOP 5 ProductID,
SUM(UnitPrice*Quantity) - SUM(UnitPrice*Quantity*(1.00-Discount))AS Rebate
FROM [Order Details]
GROUP BY ProductID
ORDER BY Rebate DESC

クエリ 2

  SELECT TOP 5 ProductID, SUM(UnitPrice*Quantity*Discount)AS Rebate
FROM [Order Details]
GROUP BY ProductID
ORDER BY Rebate DESC

クエリ オプティマイザが選択した実行プランには以下が含まれます。

• 予想される行数が 2,155 行 である Order Details テーブルに対するクラスタ化インデックス スキャン。

• GROUP BY 列に基づいて選択した行をハッシュ テーブルに挿入し、各行に対する SUM 集計を計算するハッシュ マッチ/総計 (Hash Match/Aggregate) 演算子。

• ORDER BY 句に基づく TOP 5 ソート演算子。

ビュー 1

Rebate 列に必要な集計を含むインデックス付きビューを追加すると、クエリ 1 のクエリ実行プランは変わります。数百万行もあるようなサイズの大きいテーブルの場合は、クエリのパフォーマンスも大幅に向上します。

  CREATE VIEW Vdiscount1 WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*Quantity)AS SumPrice,
SUM(UnitPrice*Quantity*(1.00-Discount))AS SumDiscountPrice,
COUNT_BIG(*) AS Count, ProductID
FROM dbo.[Order Details]
GROUP BY ProductID
   GO
CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID)

最初のクエリの実行プランから、オプティマイザが Vdiscount1 ビューを使用していることが分かります。ただし、このビューには SUM(UnitPrice*Quantity*Discount) 集計が含まれていないため、2 番目のクエリでは使用されません。そこで、両方のクエリに対応するインデックス付きビューを別に作成します。

ビュー 2

  CREATE VIEW Vdiscount2 WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*Quantity)AS SumPrice,
SUM(UnitPrice*Quantity*(1.00-Discount))AS SumDiscountPrice,
SUM(UnitPrice*Quantity*Discount)AS SumDiscountPrice2,
COUNT_BIG(*) AS Count, ProductID
FROM dbo.[Order Details]
GROUP BY ProductID
GO
CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount2 (ProductID)

このインデックス付きビューの場合、両方のクエリに対するクエリ実行プランには以下が含まれます。

• 予想される行数が 77 行である Vdiscount2 ビューに対するクラスタ化インデックス スキャン。

• ORDER BY 句に基づく TOP 5 Sort 関数。

このビューはクエリで参照されていませんが、最も低コストであるため、クエリ オプティマイザによって選択されました。

クエリ 3

クエリ 3 は、クエリ 1 およびクエリ 2 と似ていますが、ProductID 列は OrderID 列で置き換えられています。これはビュー定義には含まれていません。これは、クエリの選択リスト内のすべての式は、ビュー定義に含まれていないテーブルのビュー選択リストから派生可能でなければならないという条件に反しています。

  SELECT TOP 3 OrderID, SUM(UnitPrice*Quantity*Discount) OrderRebate
FROM dbo.[Order Details]
GROUP BY OrderID
ORDER BY OrderRebate desc

このクエリに対応するには、別のインデックス付きビューが必要です。Vdiscount2 に OrderID を含めるように変更することもできますが、その結果生成されるビューには、基のテーブルと同じだけの行が含まれることになり、基本テーブルを使用する場合よりもパフォーマンスが向上することはありません。

クエリ 4

このクエリは、各製品の平均価格を計算します。

  SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) AS AvgPrice,
SUM(od.Quantity) AS Units
FROM [Order Details] od, Products p
WHERE od.ProductID=p.ProductID
GROUP BY ProductName, od.ProductID

複雑な集計 (STDEV、VARIANCE、AVG など) をインデックス付きビューの定義に含めることはできません。ただし、結合されたときに複雑な集計を実行するための簡単な集計関数を含めることで、インデックス付きビューを使用して AVG を含むクエリを実行することができます。

ビュー 3

このインデックス付きビューには、AVG 関数を実行するために必要な簡単な集計関数が含まれています。ビュー 3 を作成後にクエリ 4 を実行すると、実行プランには使用されているビューが示されます。オプティマイザは、ビューの簡単な集計列である Price 列と Count 列から AVG 式を導き出すことができます。

  CREATE VIEW View3 WITH SCHEMABINDING
AS
SELECT ProductID, SUM(UnitPrice*(1.00-Discount))AS Price, COUNT_BIG(*)AS Count,
SUM(Quantity)AS Units
FROM dbo.[Order Details]
GROUP BY ProductID
Go
CREATE UNIQUE CLUSTERED INDEX iv3 ON View3 (ProductID)

クエリ 5

このクエリはクエリ 4 と同じですが、検索条件が 1 つ追加されています。追加された検索条件はビュー定義に含まれていないテーブルの列のみを参照していますが、このクエリにはビュー 3 を使用できます。

  SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount))AS AvgPrice,
SUM(od.Quantity)AS Units
FROM [Order Details] AS od, Products AS p
WHERE od.ProductID=p.ProductID
AND p.ProductName like '%Tofu%'
GROUP BY ProductName, od.ProductID

クエリ 6

クエリ オプティマイザは、このクエリにはビュー 3 を使用できません。追加された検索条件 od.UnitPrice > 10 では、ビュー定義に含まれるテーブルの列を参照していますが、この列はビュー定義の GROUP BY リストおよび検索述語に使用されていません。

  SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) AS AvgPrice,
SUM(od.Quantity) AS Units
FROM [Order Details] od, Products p
WHERE od.ProductID = p.ProductID
AND od.UnitPrice > 10
GROUP BY ProductName, od.ProductID

クエリ 7

対照的に、クエリ オプティマイザはクエリ 7 に対してはビュー 3 を使用できます。新しい検索条件 od.ProductID in (1,2,13,41) は、ビュー定義の GROUP BY 句に含まれているからです。

  SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) AS AvgPrice,
SUM(od.Quantity) AS Units
FROM [Order Details] AS od, Products AS p
WHERE od.ProductID = p.ProductID
AND od.ProductID in (1,2,13,41)
GROUP BY ProductName, od.ProductID

ビュー 4

このビューは、ビュー定義に od.Discount 列を含めることでクエリ 6 の条件を満たします。

  CREATE VIEW View4 WITH SCHEMABINDING
AS
SELECT ProductName, od.ProductID, SUM(od.UnitPrice*(1.00-Discount)) AS AvgPrice,
SUM(od.Quantity) AS Units, COUNT_BIG(*) AS Count
FROM dbo.[Order Details] AS od, dbo.Products AS p
WHERE od.ProductID = p.ProductID
AND od.UnitPrice > 10
GROUP BY ProductName, od.ProductID
GO
CREATE UNIQUE CLUSTERED INDEX VdiscountInd on View4 (ProductName, ProductID)

クエリ 8

ビュー 4 のインデックスは、Orders テーブルへの結合を追加するクエリにも使用できます。このクエリは、クエリの FROM 句のテーブルは、インデックス付きビューの FROM 句のテーブルのスーパーセットであることという条件を満たしています。

  SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) AS AvgPrice,
SUM(od.Quantity) AS Units
FROM dbo.[Order Details] AS od, dbo.Products AS p, dbo.Orders AS o
WHERE od.ProductID = p.ProductID and o.OrderID = od.OrderID
AND od.UnitPrice > 10
GROUP BY ProductName, od.ProductID

最後の 2 つのクエリは、クエリ 8 を変更したものです。クエリ 8 とは異なり、これらのクエリはそれぞれオプティマイザの条件の 1 つに反しているため、ビュー 4 を使用できません。

クエリ 8a

クエリ 8a は、ビューの UnitPrice > 10 とクエリの UnitPrice > 25 の部分で WHERE 句が一致していないため、インデックス付きビューを使用できません。クエリの検索条件の述語は、ビュー定義の検索条件の述語のスーパーセットである必要があります。

  SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) AvgPrice,
SUM(od.Quantity) AS Units
FROM dbo.[Order Details] AS od, dbo.Products AS p, dbo.Orders AS o
WHERE od.ProductID = p.ProductID and o.OrderID = od.OrderID
AND od.UnitPrice > 25
GROUP BY ProductName, od.ProductID

クエリ 8b

Orders テーブルはビュー 4 の定義に含まれていないことに注意してください。それにもかかわらず、このテーブルに述語を追加すると、インデックス付きビューを使用できなくなります。追加される述語は、クエリ 8b に示すように集計で使用される行を削除する可能性があるからです。

  SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) AS AvgPrice,
SUM(od.Quantity) AS Units
FROM dbo.[Order Details] AS od, dbo.Products AS p, dbo.Orders AS o
WHERE od.ProductID = p.ProductID and o.OrderID = od.OrderID
AND od.UnitPrice > 10
AND o.OrderDate > '01/01/1998'
GROUP BY ProductName, od.ProductID

詳細情報

『Microsoft SQL Server 2000 Books Online』には、インデックス付きビューに関する詳細情報が記載されています。詳細については、以下のリソースを参照してください。

• Microsoft SQL Server Web サイト

• Microsoft SQL Server Developer Center

• SQL Server Magazine (英語)

• ニュースグループ Microsoft.public.sqlserver.server および microsoft.public.sqlserver.datawarehouse (news://news.microsoft.com/) (英語)

• Microsoft Official Curriculum の SQL Server に関するコース。最新のコース情報については、Microsoft Training and Services Web サイト をご覧ください。