Share via


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

このページはアーカイブです。記載されている内容は情報提供のみを目的としており、ページ内のリンクは有効でない可能性がありますが、これらの情報についてマイクロソフトはいかなる責任も負わないものとします。

公開日: 2004年9月8日

執筆者 : Eric Hanson

寄稿者 : Gail Erickson、Lubor Kollar、Jason Ward

概要 : この資料では、SQL Server 2005 Enterprise Edition で機能強化されたインデックス付きビューについて説明します。インデックス付きビューについて解説し、具体的なシナリオを用いて、インデックス付きビューのパフォーマンスの強化点について説明します。

トピック

インデックス付きビューとは?
インデックス付きビューを使用するメリット
SQL Server 2005 のインデックス付きビューの新機能
デザインに関する考慮事項
インデックス付きビューの作成

インデックス付きビューに関してよく寄せられる質問
関連情報

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

長年にわたり、Microsoft® SQL Server™ は、ビューという仮想テーブルの作成機能をサポートしてきました。従来、このようなビューは、主に次の目的のために利用されてきました。

  • ユーザーが、1 つ以上の基本テーブルに格納されているデータの特定のサブセットにしかアクセスできないようにする、セキュリティ メカニズムの提供

  • 基本テーブルに格納されているデータの論理的な表示方法のカスタマイズを可能にするメカニズムの提供

SQL Server 2000 で、SQL Server のビュー機能が拡張され、システム パフォーマンス上のメリットを提供できるようになりました。一意クラスタ化インデックスや非クラスタ化インデックスをビューに作成することで、最も複雑なクエリのデータ アクセス パフォーマンスを向上させることができます。SQL Server 2000 と 2005 では、一意クラスタ化インデックスが設定されたビューを、インデックス付きビューと呼びます。この資料は、SQL Server 2005 を対象としていますが、内容の多くは SQL Server 2000 にも当てはまります。

データベース管理システム (DBMS) の観点からは、ビューはデータの記述 (メタデータ) であると言えます。典型的なビューが作成されるときに、仮想テーブルとして表示される結果セットを定義する SELECT ステートメントをカプセル化することにより、メタデータが定義されます。ビューが、他のクエリの FROM 句で参照される場合は、このメタデータがシステム カタログから取得され、ビューの参照が拡張されます。ビューが拡張されると、SQL Server のクエリ オプティマイザによって、クエリを実行するための実行プランが 1 つコンパイルされます。クエリ オプティマイザは、任意のクエリに対して複数の利用できる実行プランを検索し、各クエリ プランを実行した場合に実際にかかる時間の推定に基づいて、検索した中から最も実行コストの低いプランを選択します。

インデックスが設定されていないビューの場合は、クエリの解決に必要なビューのコンテンツは、実行時に作成されます。結合や集計などの計算は、ビューを参照する各クエリの実行時に行われます1。ビューに一意クラスタ化インデックスが設定されると、ビューの結果セットが直ちに作成されて、データベース内の物理ストレージに保存され、実行時に、この実行コストがかかる操作に必要なオーバーヘッドをなくすことができます。

インデックス付きビューは、クエリを実行する際に、2 つの方法で使用できます。1 つ目は、クエリにより、インデックス付きビューを直接参照する方法です。もう 1 つ、より重要な方法は、クエリ オプティマイザによりビューを利用する方法で、クエリ オプティマイザは、最も実行コストの低いクエリ プランで、一部または全部のクエリをビューに置き換えることができると判断した場合に、クエリに代えてビューを使用します。2 つ目の方法では、基になるテーブルとそのテーブルの通常のインデックスではなく、インデックス付きビューが使われます。クエリ オプティマイザが、クエリの実行中にビューを使用する際は、クエリからビューを参照する必要はありません。このため、既存のアプリケーションは、変更なしに、新しく作成されたインデックス付きビューを利用することができます。

注意   インデックス付きビューは、SQL Server 2000 と 2005 のすべてのバージョンで提供されている機能です。SQL Server 2000 と 2005 の Developer Edition と Enterprise Edition では、クエリが構造的にビューと一致していれば、クエリ自体はビュー名を指定して参照していなくても、クエリ プロセッサは、インデックス付きビューを使用してクエリを解決できます。その他のバージョンでは、ビュー名を指定して参照する必要があります。また、インデックス付きビューのコンテンツを照会する場合は、ビューの参照に NOEXPAND ヒントを使用する必要があります。

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

インデックスを使用することでクエリのパフォーマンスを向上させるというのは、新しい概念ではありません。ただし、インデックス付きビューには、通常のインデックスでは得られないパフォーマンス上のメリットがあります。インデックス付きビューを使用すると、次の方法により、クエリのパフォーマンスを向上できます。

  • 集計を事前に計算し、結果をインデックスとして保存することで、クエリの実行時に必要になる、パフォーマンス コストの高い計算を最小限に抑えることができます。

  • テーブルを事前に結合し、結果のデータ セットを保存することができます。

  • 結合または集計の組み合わせを保存できます。

以下のグラフは、クエリ オプティマイザがインデックス付きビューを使用した場合に一般的に得られる、パフォーマンスの向上率を示すデータです。グラフ中の各クエリは、それぞれ複雑さの度合い (たとえば、集計計算の数や使用したテーブルの数、述語の数) が異なり、実際の運用環境で使われている、数百万行からなるサイズの大きいテーブルを使用しています。

IPSQL501.gif

ビューでの非クラスタ化インデックスの使用

また、ビューで非クラスタ化インデックスを使用すると、さらにクエリのパフォーマンスを向上させることができます。テーブルでの非クラスタ化インデックスと同様に、ビューでの非クラスタ化インデックスも、コンパイル プロセス中にクエリ オプティマイザが選択できるオプションがより多く提供されます。たとえば、クエリにクラスタ化インデックスに含まれていない列が指定されていた場合、オプティマイザはクエリ プランの中から代わりに利用できる 1 つ以上のインデックスを選択して、すべてのインデックス付きビューや基本テーブルをスキャンするという時間のかかる処理を省くことができます。

スキーマにインデックスを追加すると、インデックスを常に保守する必要があるので、データベースのオーバーヘッドが増加します。インデックスのメリットと保守にかかるオーバーヘッドの両方を十分に考慮して、適切な方法を見つけてください。

インデックス付きビューを使用するメリット

インデックス付きビューを実装する前に、使用しているデータベースのワークロードを分析します。クエリの知識と各種のツール (SQL プロファイラなど) を使用して、インデックス付きビューを使用することでメリットが得られるクエリを特定します。集計や結合処理が頻繁に発生するようなものは、インデックス付きビューを使用することによるメリットを最も期待できます。クエリが頻繁に使われるかどうかにかかわらず、結果が返されるまでにかなりの時間がかかるクエリで、結果がすばやく返されることでメリットが得られる場合も、インデックス付きビューの使用を検討できるでしょう。たとえば、重役が毎月末に実行するレポート作成用のクエリの結果を事前に計算し、保存するインデックス付きビューを作成することが有用な場合もあるでしょう。

すべてのクエリで、インデックス付きビューによるメリットが得られるわけではありません。通常のインデックスと同様に、インデックス付きビューは使用されなければ、何のメリットもありません。使用されない場合、パフォーマンスの向上を実現できないだけでなく、ディスク領域や保守、最適化にかかる余分なコストが発生します。しかし、インデックス付きビューが使用されると、データ アクセスのパフォーマンスでかなり (数桁規模で) の向上が期待できます。これは、クエリ オプティマイザが、インデックス付きビューに保存されている、事前に計算された結果を使用できるので、クエリの実行にかかるコストがかなり削減されるためです。

クエリ オプティマイザは、実行コストの高いクエリに対してのみ、インデックス付きビューの使用を検討します。これは、クエリを最適化するために、さまざまなインデックス付きビューから最適なビューを特定するためにかかるコストが、インデックス付きビューを使用して得られるメリットを上回ることがないようにするためです。コストが 1 未満のクエリでインデックス付きビューが使用されることは、まずありません。

インデックス付きビューを実装することでメリットが得られるアプリケーションには、次のものがあります。

  • 意思決定支援ワークロード

  • データ マート

  • データ ウェアハウス

  • オンライン分析処理 (OLAP) ストアおよびソース

  • データ マイニング ワークロード

クエリの種類やパターンという観点では、以下のようなアプリケーションが、インデックス付きビューによるメリットを期待できます。

  • 大規模なテーブルの結合および集計を行うアプリケーション

  • クエリのパターンが繰り返し使われているアプリケーション

  • 同じ列セット、または重複する列を含む列セットに対して、繰り返し集計を実行するアプリケーション

  • 同じキーを用いて同じテーブルの結合を繰り返し実行するアプリケーション

  • 上記のような特徴を複数持つアプリケーション

逆に、書き込みが頻繁に行われるオンライン トランザクション処理 (OLTP) システムや、更新が頻繁に行われるデータベース アプリケーションは、インデックス付きビューによるメリットが得られない可能性があります。これは、ビューと、ビューの基になっている基本テーブルの両方の更新にかかる保守コストが増大するためです。

クエリ オプティマイザがインデックス ビューを使用するしくみ

SQL Server のクエリ オプティマイザは、指定されたクエリを実行する代わりにインデックス付きビューを使用できるかどうかを自動的に判断します。オプティマイザが、クエリの実行プランでビューを使用する場合、クエリからビューを直接参照する必要はありません。したがって、インデックス付きビューさえ作成すれば、既存のアプリケーションを変更しなくても、インデックス付きビューを使用できます。

オプティマイザに関する考慮事項

クエリ オプティマイザが、クエリの全体または一部をインデックス付きビューで代用できるかどうかを決定する際に、検討する条件がいくつかあります。これらの条件は、クエリで指定されている 1 つの FROM 句に対応するもので、以下のような条件があります。

  • クエリの FROM 句に指定されているテーブルは、インデックス付きビューの FROM 句のテーブルのスーパーセットである必要があります。

  • クエリの結合条件は、ビューの結合条件のスーパーセットである必要があります。

  • クエリの集計列は、ビューの集計列のサブセットから派生できる必要があります。

  • クエリの SELECT リストの式は、すべて、ビューの SELECT リストまたはビューの定義に含まれていないテーブルから派生できる必要があります。

  • ある述語の行セットが、別の述語の行セットのスーパーセットとなる場合、前者が後者を "包含" します。たとえば、"T.a=10" は "T.a=10 and T.b=20" を包含します。すべての述語は、それ自体を包含します。あるテーブルの値を制限するビューの述語部分は、この同じテーブルを制限するクエリの述語部分を包含する必要があります。また、その場合は、SQL Server によって検証できるような方法で、包含する必要があります。

  • ビュー定義で指定されているテーブルに属する、クエリの検索条件述語内の列は、すべて、ビュー定義中の次の箇所の少なくとも 1 つで指定されている必要があります。

    1. GROUP BY のリスト。

    2. GROUP BY が指定されていない場合は、ビューの SELECT リスト。

    3. ビュー定義の述語と同じまたは同等の述語。

    (a) と (b) の場合は、SQL Server によって、クエリ述語がビューの行に適用され、ビューの行にさらに制限が課されます。(c) は、列がビューに表示される必要がないため、列にフィルタをかける必要がないという特殊な場合です。

クエリに FROM 句 (サブクエリ、派生テーブル、UNION) が 1 つ以上含まれている場合は、オプティマイザは、このクエリを処理するために、複数のインデックス付きビューを選択し、これらを別の FROM 句に適用する場合があります。2

上記の条件に該当するクエリの例は、この資料の終わりに記載されています。インデックスがある場合のベスト プラクティスとして、クエリ オプティマイザにより、クエリの実行プランでどのインデックスを使用するかが決定されるようにすることをお勧めします。

NOEXPAND ビュー ヒントの使用

名前を指定してビューを参照しているクエリを SQL Server が処理する場合、ビューの定義は、通常、基本テーブルのみを参照するように拡張されます。このプロセスは、"ビューの拡張" と呼ばれます。これは、マクロの拡張の一種です。

NOEXPAND ビュー ヒントは、ビューをクラスタ化インデックスが設定された通常のテーブルとして、クエリ オプティマイザで強制的に処理されるようにするものです。NOEXPAND ヒントは、ビューの拡張を阻止します。NOEXPAND ヒントを適用できるのは、インテックス付きビューが、FROM 句で直接参照されている場合のみです。以下に例を示します。

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

確実に、SQL Server が、基本テーブルのデータを読み取るのではなく、ビュー自体を読み取って、クエリを処理するようにする場合は、NOEXPAND を使用します。クエリ プランでビューが使用されるようにする場合に、何らかの理由で、基本テーブルに対してクエリが実行されるように SQL Serverによって処理されてしまう場合は、NOEXPAND の使用を検討してください。NOEXPANDヒントを使用すると、Developer Edition と Enterprise Edition を除く、すべての SQL Server のバージョンでSQL Server が直接インデックス付きビューに対してクエリを実行するようになります。SQL Server Management Studio ツールの推定実行プランの表示 (Display Estimated Execution Plan) を使用すると、SQL Serverが選択したステートメントの実行プランをグラフィカルな表示で確認できます。テキスト ベースであれば、SHOWPLAN_ALL、SHOWPLAN_TEXT または SHOWPLAN_XML を使用しても、確認できます。各 SHOWPLAN の詳細については、SQL Sever Books Online を参照してください。

EXPAND VIEWS クエリ ヒントの使用

名前を指定してビューを参照するクエリを処理する場合、SQL Server は、ビューの参照に NOEXPAND ヒントが指定されていない限り、常に、ビューを拡張します。クエリの最後に OPTION 句を使用して EXPAND VIEWS クエリ ヒントが指定されていないと、SQL Server は、拡張されたクエリに合わせて、インデックス付きビューを拡張しようとします。たとえば、データベースに View1 というインデックス付きビューがあるとします。次のクエリでは、View1 は、このクエリの論理定義 (CREATE VIEW ステートメント) に基づいて拡張されますが、その後、EXPAND VIEWS オプションにより、View1 のインデックス付きビューがこのクエリの実行プランで使用されないようにしています。

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

確実に、インデックス付きビューにアクセスするのではなく、クエリが参照する基本テーブルのデータにアクセスして、クエリが処理されるようにする場合は、EXPAND VIEWS を使用します。EXPAND VIEWS は、インデックス付きビューを使用した場合に発生する可能性があるロックの競合を防止する上で、役に立つ場合があります。NOEXPAND と EXPAND VIEWS は、共に、アプリケーションをテストする場合に、インデックス付きビューを使用した場合と使用しない場合のパフォーマンスの評価に使用できます。

SQL Server 2005 のインデックス付きビューの新機能

SQL Server 2005 では、SQL Server 2000 と比べて、インデックス付きビューにさまざまな機能強化が行われています。以下を基にしたビューにも、インデックスが設定できるよう、インデックス可能ビューのセットが拡張されました。

  • **スカラ集計。**GROUP BY が指定されていない SUM および COUNT_BIG を含みます。

  • スカラ式とユーザー定義関数 (UDF)。たとえば、T(a int, b int, c int) というテーブルと、dbo.MyUDF(@x int) というスカラ UDF がある場合、T で定義されるインデックス付きビューには、a+b や dbo.MyUDF(a) などの計算列を含むことができます。

  • 保存された不正確な列不正確な列とは、float または real の列か、float 列または real 列から派生される計算列です。SQL Server 2000 では、不正確な列がインデックス キーの一部でない場合は、これをインデックス付きビューの SELECT リストに指定できました。また、ビュー定義内では、どのような箇所 (たとえば、WHERE 句や FROM 句) であっても、不正確な列を使用できませんでした。SQL Server 2005 では、不正確な列が基本テーブルに保存されていれば、キーやビュー定義内部でその列を指定できます。保存される列は、通常の列と PERSISTED が指定されている計算列です。

  • 保存されていない不正確な列をインデックスやインデックス付きビューで使用できない主な理由は、データベースをあるコンピュータからデタッチして、別のコンピュータにアタッチできる必要があるためです。別のコンピュータへアタッチされた後も、インデックスやインデックス付きビュー内の計算列のすべての値が、ビット レベルでも、前のハードウェアとまったく同じように新しいハードウェアでも正確に導き出せる必要があります。導き出せない場合は、新しいハードウェア上では、これらのインデックス付きビューが論理的に破損します。このような破損が発生するので、新しいハードウェア上では、インデックス付きビューに対して実行されるクエリは、実行プランでインデックス付きビューが使用された場合と、基本テーブルが使用された場合では、異なる結果が返されます。さらに、新しいハードウェア上では、インデックス付きビューを適切に保守することができない可能性があります。残念ながら、浮動小数点計算を行うハードウェアは、たとえ、同じメーカーの同じアーキテクチャのプロセッサであっても、プロセッサのバージョンが異なると、2 つのコンピュータ間では異なる場合があります。ファームウェアがアップグレードされると、(a*b) の結果は、a と b に代入される浮動小数点の値によっては、新しいハードウェアと古いハードウェアでは異なる可能性があります。たとえば、きわめて近い値が結果として返されても、最下位のビットが一致していない場合があります。不正確に計算される値は、インデックスを作成するに保存することで、データベースが更新されインデックスとインデックス付きビューが保守された場合も、同じコンピュータ上ですべての式が評価されるため、データッチとアタッチで一貫性がなくなる問題が解決されます。

  • 共通言語ランタイム (CLR) の種類。SQL Server 2005 の主な新機能の 1 つとして、ユーザー定義型 (UDT) と CLR を基にした UDF のサポートが挙げられます。CLR UDT 列または CLR UDT 列から派生した式で、これらの列または式が決定的であり、正確で、保存される場合は、これらに対してインデックス付きビューを定義できます。CLR ベースのユーザー定義集計は、インデックス付きビューには使用できません。

オプティマイザがクエリとインデックス付きビューを照合する (その結果、インデックス付きビューをクエリ プランで使用できるようにする) 機能も拡張され、以下についても照合されるようになりました。

  • **新種の式。**これは、クエリやビューの SELECT リストまたは条件で使用される新しい式で、次のものがあります。

    • スカラ式。たとえば、(a+b)/2 です。

    • スカラ集計。

    • スカラ UDF。

  • **区間包含。**オプティマイザは、インデックス付きビューの定義内の区間条件が、クエリの区間条件を含む、つまり、"包含" しているかどうかを判断できます。たとえば、オプティマイザは、「a>10 and a<20」は「a>12 and a<18」を含んでいると判断します。

  • **式の等価性。**構文的には異なっていたとしても、処理内容が同じ式については、一致するものとして処理されるようになりました。たとえば、「a=b and c<>10」と「10<>c and b=a」は一致すると見なされます。

また、データベースに多数のインデックス付きビューがある場合は、ビューの基になっているテーブルをコンパイルする場合、一般に、SQL Server 2000 に比べて SQL Server 2005 の方がはるかに高速に処理されます。

デザインに関する考慮事項

あるデータベース システムにとって適切なインデックス設定はどのようなものになるかを特定するのは、複雑な作業です。通常のインデックスを作成する場合も、検討すべき事項は多数ありますが、スキーマにインデックス付きビューが加わると、デザインやその結果は大幅に複雑になります。たとえば、インデックス付きビューは、次のもので使用できます。

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

  • クエリに指定されている、このテーブルのサブセットに対する条件の任意のサブセット。

  • グループ列。

  • 集計関数 (SUM など)。

テーブルのインデックスやインデックス付きビューは、それぞれから最高の結果が得られるように、同時にデザインするようにします。インデックスもインデックス付きビューも、同じクエリに対して有用である可能性があるため、これらを個別にデザインした場合、余分な推奨事項が生成され、ストレージの使用量が増大し、保守のためのオーバーヘッドが生じる可能性があります。一方、データベースの物理的なデザインを考える場合は、さまざまなクエリに対するパフォーマンス要件と、データベースがサポートする必要のある更新の両方を考慮して、メリットとデメリットのバランスをとる必要があります。したがって、インデックス付きビューの物理的なデザインを適切に策定することは、困難な作業です。このため、可能な限り、Database Tuning Advisor を使用することをお勧めします。

クエリ オプティマイザで、ある 1 つのクエリに対して検討できるインデックス付きビューが多数ある場合は、クエリの最適化コストが非常に増大します。クエリ オプティマイザにより、そのクエリに指定されているテーブルのサブセットに定義されているすべてのインデックス付きビューが検討される可能性があります。1 つ 1 つのビューに対して、このサブセットを置き換えることができるかどうかが、検討されます。特に、任意のクエリに対してこのようなビューが数百もあるような場合は、この処理にかなりの時間がかかる可能性があります。

ビューに一意クラスタ化インデックスを作成する前に、ビューが満たしておくべき要件がいくつかあります。デザインを行う際は、以下の要件を検討してください。

  • ビューと、ビューで参照しているすべてのテーブルは、同じデータベースにあり、所有者が同じである必要があります。

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

  • 一意クラスタ化インデックスを作成しないと、ビューのその他のインデックスは作成できません。

  • 基本テーブル、ビュー、インデックスの作成時、および基本テーブルとビューのデータが変更された場合は、必ず、ある SET オプション (この資料で後述します) を適切に設定する必要があります。また、これらの SET オプションが適切に設定されていないと、そのインデックス付きビューは、クエリ オプティマイザの検討対象になりません。

  • ビューは、スキーマ バインドを使用して作成する必要があります。また、ビューで参照しているユーザー定義関数も、SCHEMABINDING オプションを設定して、作成する必要があります。

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

デザインのガイドライン

インデックス付きビューをデザインする際は、以下のガイドラインを参考にしてください。

  • インデックス付きビューは、複数のクエリまたは複数の操作で使用できるようにデザインする。

    たとえば、任意の列の SUM と任意の列の COUNT_BIG を含むインデックス付きビューは、関数 SUM、COUNT、COUNT_BIG、または AVG を含むクエリによって使用できます。既定テーブルからすべての行を取得するのではなく、ビューから少数の行を取得すればよく、AVG 関数の実行に必要な計算の一部が既に完了しているため、これらのクエリの実行速度は向上します。

  • インデックス キーはコンパクトに。

    インデックス キーに使用する列数とバイト数を可能な限り少なくすることで、インデックス付きビューの行数が抑えられ、大きいキーを使用した場合よりもキーの比較がより早く行えるため、インデックス付きビューの行に効率よくアクセスできます。また、インデックス付きビューで定義されているすべての非クラスタ化インデックスでは、クラスタ化インデックス キーが行ロケータとして使用されます。大きなインデックス キーを使用した場合のコストは、ビューの非クラスタ化インデックスの数に比例して、増大します。

  • 結果として得られるインデックス付きビューのサイズを検討する。

    純粋な集計処理の場合、インデックス付きビューを使用しても、このインデックス付きビューのサイズが元のテーブルのサイズとあまり変わらない場合は、有意なパフォーマンスの向上が見られない可能性があります。

  • 処理の部分部分を高速化する、小さいインデックス付きビューを複数デザインする。

    クエリ全体を解決できるインデックス付きビューをデザインできない場合もあります。このような場合は、そのクエリの一部に対応できるインデックス付きビューを複数作成することをお勧めします。

    次の例を参考にしてください。

    • 頻繁に実行されるクエリで、あるデータベースのデータを集計し、次に別のデータベースのデータを集計し、それぞれの結果を結合するクエリがあるとします。インデックス付きビューでは、複数のデータベースに対してテーブルの参照を行うことはできないため、この処理全体を実行できるビューをデザインすることはできません。しかし、それぞれのデータベースごとに、そのデータベースの集計を実行するインデックス付きビューを作成する事はできます。既存のクエリのコードを書き直さなくても、オプティマイザにより、これらのインデックス付きビューと既存のクエリを照合できれば、少なくとも集計処理の速度は向上します。結合処理の速度は向上しないとしても、インデックス付きビューに保持されている集計を使用できるため、クエリ全体の処理速度は向上します。

    • 頻繁に実行されるクエリで、複数のテーブルのデータを集計し、その結果を UNION を使用して結合するクエリがあるとします。UNION は、インデックス付きビューでは使用できません。この場合は、各集計の操作ごとに対応するビューをデザインします。クエリのコードを書き直さなくても、オプティマイザにより、これらのインデックス付きビューが使用されて、クエリの処理速度が向上します。UNION の処理パフォーマンスは変わらないとしても、各集計処理のパフォーマンスは向上します。

インデックス付きビューの選択を支援するツールの有無

Database Tuning Advisor (DTA3) は、データベースの物理的なデザインのチューニングを支援する SQL Server 2005 の機能です。DTA は、インデックス付きビューのほか、基本テーブルのインデックス、およびテーブルやインデックスのパーティション分割方式に関する推奨を提示します。DTA を使用すると、任意のデータベースに対して通常実行される一連のクエリの処理を最適化できる、インデックス、インデックス付きビュー、パーティション分割方式の組み合わせをより効果的に特定できます。DTA は、さまざまなインデックス付きビューを推奨できます。これらには、「SQL Server 2005 のインデックス付きビューの新機能」で説明する SQL Server 2005 のインデックス付きビューの新機能を利用するインデックス付きビューも含まれます。物理的なストレージ構造をデザインする場合、DTA を使用しても、データベース管理者による判断が完全に必要なくなるわけではありません。ただし、DTA を使用することで、データベースの物理的なデザイン プロセスを簡素化することはできます。DTA は、仮のインデックス、インデックス付きビュー、パーティション構造のセットを作成して、コスト ベースのクエリ オプティマイザと連携して動作することで、機能します。DTA は、オプティマイザを使用して、ワークロードにこれらの構造を使用した場合と使用しなかった場合のコストを予測し、全体のコストが低い構造を推奨します。

Database Tuning Advisor は、インデックス付きビューの作成に必要なすべての SET オプション(正しい結果セットが得られるようにする)を設定します。ただし、DTA のオプション設定が適切でない場合は、アプリケーションは作成されたビューを利用できない可能性があります。必要な SET オプションが指定されていない場合、インデックス付きビューの定義に使用されているテーブルでの挿入、更新または削除操作が失敗する可能性があります。

データが更新された場合のインデックス付きビューの処理

他のインデックスと同様に、基本テーブルのデータが変更された場合は、インデックス付きビューも SQL Server によって自動的に保守されます。通常のインデックスの場合、各インデックスは 1 つのテーブルに直接関連付けられています。基本テーブルに対して、INSERT、UPDATE、または DELETE 操作が実行された場合、インデックスも合わせて更新され、インデックスが保持している値が常に基本テーブルの値と一致しているようにします。

インデックス付きビューも同様に保守されますが、ビューが複数のテーブルを参照している場合は、参照先のテーブルのいずれかが更新されただけで、インデックス付きビューの更新が必要になる可能性があります。通常のインデックスと異なり、参照先のテーブルのいずれかに行が 1 行挿入されただけでも、インデックス付きビューでは複数の行が変更される可能性があります。これは、この挿入された 1 行が、別のテーブルの複数の行と結合される可能性があるためです。更新や削除操作でも、同じことが言えます。その結果、インデックス付きビューの保守は、テーブルのインデックス保守よりも、コストがかかる可能性があります。逆に、かなり厳しい選択条件が設定されているインデックス付きビューの保守は、ビューが参照しているテーブルに挿入、削除、更新操作が行われたとしても、ビューには影響がないと考えられるため、テーブルのインデックスの保守よりも、かなりコストが低くなる可能性があります。この様なインデックス付きビューに関しては、メンテナンスコストを低くする事ができます。

SQL Server では、更新操作が可能なビューがあります。ビューが更新可能なビューである場合、基になっている基本テーブルも、INSERT、UPDATE、DELETE ステートメントを使用して、ビューから直接変更されます。ビューのインデックスが作成されていても、ビューから更新ができなくなることはありません。インデックス付きビューを更新した場合は、ビューの基になっている基本テーブルも実際に更新されます。さらに、基本テーブルの更新結果については、インデックス付きビューの保守の一環として、自動的にインデックス付きビューに反映されます。更新操作が可能なビューの詳細については、SQL Server 2005 の SQL Server Books Online の「ビューを使用したデータ変更」を参照してください。

保守コストに関する考慮事項

インデックス付きビューをデザインする際は、以下の事項に留意してください。

  • インデックス付きビューを作成する場合は、データベースには、さらに多くのストレージ容量が必要になります。インデックス付きビューの結果セットは、通常のテーブルの保存と同様の方法で、物理的にデータベースに保存されます。

  • SQL Server では、ビューの保守は自動的に行われます。したがって、ビューが定義されている基本テーブルが変更された場合は、ビューのインデックスには 1 つ以上の変更が加えられます。したがって、さらに多くの保守オーバーヘッドが発生します。

インデックス付きビューにより、クエリの実行処理の検索パフォーマンスは上がりますが、そのトレードオフとして、ビューを格納する領域の必要性とビューのメンテナンス処理による更新パフォーマンスへの影響が発生します。

ビューに必要になるストレージ容量は、比較的簡単に予測できます。SQL Server Management Studio ツールの推定実行プランの表示 (Display Estimated Execution Plan) を使用して、ビュー定義内にカプセル化されている SELECT ステートメントを評価してください。このツールにより、クエリによって返される行の数と、行のサイズの概算値が算出されます。この行の数と行のサイズを掛け合わせることで、ビューのサイズを予測できます。ただし、これはあくまでも概算値に過ぎません。実際のビューのインデックスのサイズは、ビューの定義に指定されているクエリを実行するか、ビューのインデックスを作成することでしか、正確には特定できません。

SQL Server により実行される自動的な保守に関して言えば、推定実行プランの表示 (Display Estimated Execution Plan) 機能により、このオーバーヘッドの影響をある程度知ることができる可能性があります。ビューを変更するステートメント (ビューに対する UPDATE や基本テーブルに対する INSERT) を SQL Server Management Studio で評価する場合は、表示されているそのステートメントの実行プランには、そのステートメントで発生する保守操作も含まれています。運用環境でのこの操作の予想される実行回数と合わせて、このコストを考慮することで、ビューの保守にかかるコストを予想できます。

一般的には、ビューや、ビューの基になっている基本テーブルの変更や更新は、可能であれば、個別の操作としてではなく、バッチ処理での実行が推奨されます。これにより、ビューの保守にかかるオーバーヘッドをいくらか削減できる可能性があります。

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

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

  1. ビューで参照する既存のテーブルすべてで、ANSI_NULLS が正しく設定されていることを確認します。

  2. 新しいテーブルを作成する場合は、事前に、現在のセッションに ANSI_NULLS が、下の表のように正しく設定されていることを確認します。

  3. ビューを作成する前に、現在のセッションに ANSI_NULLS および QUOTED_IDENTIFIER が、下の表のように正しく設定されていることを確認します。

  4. ビュー定義が決定的であることを確認します。

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

  6. ビューに一意クラスタ化インデックスを作成する前に、セッションに SET オプションが、下の表のように正しく設定されていることを確認します。

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

  8. OBJECTPROPERTY 関数を使用して、既存のテーブルまたはビューの ANSI_NULLS および QUOTED_IDENTIFIER の値を確認できます。

SET オプションを使用して一貫性のある結果を得る

同じ式を評価したとしても、クエリの実行時点のセッションで有効になっていた SET オプションが異なる場合、SQL Server 2005 では異なる結果が返される可能性があります。たとえば、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_WARNINGS

    ON

    OFF

    ON

    OFF

    CONCAT_NULL_YIELDS_NULL

    ON

    OFF

    ON

    OFF

    NUMERIC_ROUNDABORT

    OFF

    OFF

    OFF

    OFF

    QUOTED_IDENTIFIER

    ON

    OFF

    ON

    OFF

インデックス付きビューの作成時は、現行セッションの ARITHABORT4 オプションに ON を設定する必要はありません。SQL Server 2005 では、ANSI_WARNINGS が ON に設定されると、このオプションは暗黙的に ON になるので、明示的に設定する必要はありません。OLE DB または ODBC サーバー接続を使用している場合は、変更する必要があるのは、ARITHABORT 設定の値のみです。DB LIB の値は、すべて、sp_configure を使用してサーバー レベルで、または SET コマンドを使用してアプリケーションから、正しく設定する必要があります。SET オプションの詳細については、SQL Server Books Online の「SQL Server でのオプションの使用」を参照してください。

決定的関数の使用

インデックス付きビューの定義は、決定的である必要があります。SELECT リストと、WHERE 句、GROUP BY 句の式がすべて決定的である場合は、ビューも決定的になります。決定的な式は、同じ入力値を使用して評価された場合は、常に、同じ結果を返します。決定的な式に使用できるのは、決定的関数のみです。たとえば、DATEADD 関数は、この関数の 3 つの引数にどのような引数値が設定されても、常に同じ結果を返すので、決定的関数です。GETDATE は、常に同じ引数を使用して呼び出されますが、戻り値は実行されるたびに異なるため、これは決定的関数ではありません。詳細については、SQL Server 2005 の SQL Server Books Online の「決定的関数と非決定的関数」を参照してください。

式が決定的であったとしても、これに浮動小数点式が含まれていた場合は、実際の結果は、プロセッサのアーキテクチャやマイクロコードのバージョンにより、異なる可能性があります。データベースをあるコンピュータから別のコンピュータに移動する場合に、SQL Server 2005 でデータの一貫性を確保したい場合は、このような式は、インデックス付きビューのキーではない列としてのみ使用できます。浮動小数点式を含む決定的式は、正確な式と呼ばれます。インデックス付きビューのキー列、および、WHERE または GROUP BY 句には、保存されるか、正確であるか、あるいはその両方の特徴を備えた決定的式しか、使用できません。保存される式は、通常の列と PERSISTED が指定されている計算列など、保存されている列を参照する式です。

列が決定的であるかどうかを特定するには、COLUMNPROPERTY 関数と IsDeterministic プロパティを使用します。SCHEMABINDING が設定されているビューの決定的列が、正確であるかどうかを特定するには、COLUMNPROPERTY 関数と IsPrecise プロパティを使用します。COLUMNPROPERTY は、TRUE の場合は 1 、FALSE の場合は 0、無効な入力に対しては NULL を返します。たとえば、次のスクリプトを考えます。

CREATE TABLE T(a int, b real, c as getdate(), d as a+b) 
CREATE VIEW VT WITH SCHEMABINDING AS SELECT a, b, c, d FROM dbo.T 
SELECT object_id('VT'), COLUMNPROPERTY(object_id('VT'),'b','IsPrecise')

この場合、b 列は real 型であるため、SELECT から IsPrecise に 0 が返されます。試しに、COLUMNPROPERTY を使って、T の他の列が決定的であるかどうかや、正確であるかあるかどうかを確認してみるとよいでしょう。

その他の要件

インデックスを設定できるビューのセットは、作成することができるビューのセットのサブセットになります。インデックスを設定できるビューは、インデックスが設定されているかどうかに関わらず、存在します。

ビューの一意クラスタ化インデックスを作成する場合は、「デザインのガイドライン」「SET オプションを使用して一貫した結果を得る」「決定的関数の使用」の各セクションに記載されている要件のほかに、次の要件も満たす必要があります。

基本テーブルの要件
  • ビューが参照する基本テーブルは、テーブルの作成時に、ANSI_NULL SET オプションに適切な値が設定されている必要があります。OBJECTPROPERTY 関数を使用すると、既存のテーブルの ANSI_NULLS の値を確認できます。
関数の要件
  • ビューが参照するユーザー定義関数は、WITH SCHEMABINDING オプションを指定して、作成されている必要があります。
ビューの要件
  • ビューは、WITH SCHEMABINDING オプションを指定して、作成されている必要があります。

  • ビューでテーブルを参照する場合は、2 部構成の名前 (schemaname.tablename) を使用します。

  • ビューでユーザー定義関数を参照する場合は、2 部構成の名前 (schemaname.functionname) を使用します。

  • ANSI_NULLS および QUOTED_IDENTIFIER SET オプションを正しく設定する必要があります。

ビューの制限

SQL Server 2005 でビューにインデックスを設定する場合は、ビュー定義には以下のものを使用できません。

ANY, NOT ANY

OPENROWSET, OPENQUERY, OPENDATASOURCE

不正確 (float、real) な値での算術

OPENXML

COMPUTE, COMPUTE BY

ORDER BY

不正確な値を返す CONVERT

OUTER join

COUNT(*)

無効になったクラスタ化インデックスを使用した基本テーブルの参照

GROUP BY ALL

他のデータベースのテーブルまたは関数の参照

派生テーブル (FROM リスト内のサブクエリ)

他のビューの参照

DISTINCT

ROWSET 関数

EXISTS, NOT EXISTS

自己結合

集計結果に対する式 (例 : SUM(x)+SUM(x))

STDEV, STDEVP, VAR, VARP, AVG

フルテキスト述語 (CONTAINS、FREETEXT、CONTAINSTABLE、FREETEXTTABLE)

サブクエリ

不正確な定数 (例 : 2.34e5)

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

インライン関数またはテーブル値関数

テーブル ヒント (例 : NOLOCK)

MIN, MAX

text、ntext、image、filestream 型の列または XML 列

非決定的式

TOP

Unicode 以外の照合順序

UNION

SQL Server 2005 で検出される矛盾 (つまりビューが空になる) (例 : where 0=1 and ...)

 

注意   インデックス付きビューに、float 型や real 型の列を含めることができますが、これらの列が保存されない計算列である場合は、クラスタ化インデックスのキーに指定することはできません。

GROUP BY の制限

GROUP BY が指定されている場合は、ビューの定義には以下の制限が課されます。

  • COUNT_BIG(*) を指定する必要があります。

  • HAVING、CUBE、ROLLUP または GROUPING() を指定することはできません。

これらの制限が当てはまるのは、インデックス付きビューの定義に限ります。クエリは、この GROUP BY に関する制限が守られていない場合でも、実行プランでインデックス付きビューを使用できます。

インデックスの要件
  • CREATE INDEX ステートメントを実行するユーザーは、ビューの所有者である必要があります。

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

  • インデックスは、IGNORE_DUP_KEY オプションを有効にして、作成されている必要があります。

このセクションの例は、集計と結合を含むインデックス付きビューの使用方法を例示するためのものです。また、クエリ オプティマイザが、インデックス付きビューを使用できるかどうかを検証する場合に使用される条件も例示しています。すべての条件については、「クエリ オプティマイザがインデックス ビューを使用するしくみ」を参照してください。

ここに記載されているクエリは、そのまま、SQL Server 2005 に用意されているサンプル データベース AdventureWorksのテーブルに対して実行できます。SQL Server Management Studio の推定実行プランの表示 (Display Estimated Execution Plan) を使用すると、クエリ オプティマイザにより選択された実行プランで、ビューの作成前と後の両方の実行プランを確認できます。ここの例によって、オプティマイザがコストの低い実行プランを選択するしくみを知ることはできますが、パフォーマンスの向上を確認するには、AdventureWorks サンプル データベースは、小さ過ぎます。

以下の例を利用する前に、次のコマンドを実行して、セッションにオプションが正しく設定されているようにしてください。

セットアップ

SET ANSI_NULLS ON 
SET ANSI_PADDING ON 
SET ANSI_WARNINGS ON 
SET CONCAT_NULL_YIELDS_NULL ON 
SET NUMERIC_ROUNDABORT OFF 
SET QUOTED_IDENTIFIER ON 
SET ARITHABORT ON

以下の 2 つのクエリは、テーブル Sales.SalesOrderDetail から合計の割引額が最も大きい製品を 5 つ取得する方法を示しています。

クエリ 1

SELECT TOP 5 ProductID, Sum(UnitPrice*OrderQty) –  
     Sum(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rebate 
FROM Sales.SalesOrderDetail  
GROUP BY ProductID 
ORDER BY Rebate DESC

クエリ 2

SELECT TOP 5 ProductID,  
SUM(UnitPrice*OrderQty*UnitPriceDiscount) AS Rebate 
FROM Sales.SalesOrderDetail  
GROUP BY ProductID 
ORDER BY Rebate DESC

クエリ オプティマイザにより選択された実行プランには、以下のものが含まれます。

  • 予想行数が 121,317 行の Sales.SalesOrderDetail テーブルに対するクラスタ化インデックス スキャン。

  • GROUP BY 列に基づくハッシュ テーブルに選択した行を格納し、各行に対して SUM 集計計算を実行する HASH Match/Aggregate 演算子。

  • ORDER BY 句に基づく TOP 5 Sort 演算子。

ビュー 1

Rebate 列に必要な集計操作を含むインデックス付きビューを追加すると、クエリ 1 のクエリ実行プランは変更されます。これを大きい (数百万行からなる) テーブルに対して実行した場合は、クエリのパフォーマンスもかなり向上します。

CREATE VIEW Vdiscount1 WITH SCHEMABINDING AS  
SELECT SUM(UnitPrice*OrderQty) AS SumPrice,  
SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS SumDiscountPrice,  
COUNT_BIG(*) AS Count, ProductID 
FROM Sales.SalesOrderDetail  
GROUP BY ProductID 
GO 
CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID)

最初のクエリの実行プランでは、オプティマイザにより Vdiscount1 ビューが使用されるようになっています。しかし、このビューは、2 番目のクエリでは使用されません。これは、クエリに集計 SUM(UnitPrice*OrderQty*UnitPriceDiscount) が含まれていないためです。どちらのクエリにも使える、別のインデックス付きビューを作成することもできます。

ビュー 2

CREATE VIEW Vdiscount2 WITH SCHEMABINDING AS 
SELECT SUM(UnitPrice*OrderQty)AS SumPrice,  
SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount))AS SumDiscountPrice,  
SUM(UnitPrice*OrderQty*UnitPriceDiscount)AS SumDiscountPrice2,  
COUNT_BIG(*) AS Count, ProductID 
FROM Sales.SalesOrderDetail  
GROUP BY ProductID 
GO 
CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount2 (ProductID)

Vdiscount1 を削除し、このインデックス付きビューを作成すると、2 つのクエリの実行プランには以下のものが含まれます。

  • 予想行数が 266 行の Vdiscount2 ビューに対するクラスタ化インデックス スキャン。

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

クエリ オプティマイザにより、Vdiscount2 ビューが選択されました。これは、クエリではこのビューが参照されていませんが、このビューを使用することで、実行コストが最も低くなるためです。

クエリ 3

クエリ 3 は、前の 2 つのクエリと似ていますが、ProductID の代わりに、ビューの定義には含まれていない列 SalesOrderID が指定されています。これは、インデックス付きビューをクエリ プランで使用するためには、ビュー定義に指定されているテーブルに対するクエリの SELECT リストのすべての式が、ビューの SELECT リストから派生できる必要があるという条件に違反します。

SELECT TOP 3 SalesOrderID,  
SUM(UnitPrice*OrderQty*UnitPriceDiscount) OrderRebate 
FROM Sales.SalesOrderDetail  
GROUP BY SalesOrderID 
ORDER BY OrderRebate DESC

このクエリを解決するには、別のインデックス付きビューが必要になります。Vdiscount2 を変更して SalesOrderID を含むようにすることもできます。ただし、生成されるビューには、元のテーブルと変わらない数の行が含まれ、基本テーブルを使用した場合とパフォーマンスも変わらないでしょう。

クエリ 4

このクエリは、製品ごとの平均価格を返します。

SELECT p.Name, od.ProductID,  
AVG(od.UnitPrice*(1.00-od.UnitPriceDiscount)) AS AvgPrice,  
SUM(od.OrderQty) AS Units  
FROM Sales.SalesOrderDetail AS od, Production.Product AS p  
WHERE od.ProductID=p.ProductID  
GROUP BY p.Name, od.ProductID

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

ビュー 3

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

CREATE VIEW View3 WITH SCHEMABINDING AS  
SELECT ProductID, SUM(UnitPrice*(1.00-UnitPriceDiscount)) AS Price,  
COUNT_BIG(*) AS Count, SUM(OrderQty) AS Units  
FROM Sales.SalesOrderDetail  
GROUP BY ProductID  
GO 
CREATE UNIQUE CLUSTERED INDEX iv3 ON View3 (ProductID)

クエリ 5

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

SELECT p.Name, od.ProductID,  
AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,  
SUM(od.OrderQty) AS Units  
FROM Sales.SalesOrderDetail AS od, Production.Product AS p  
WHERE od.ProductID=p.ProductID AND p.Name like '%Red%'  
GROUP BY p.Name, od.ProductID

クエリ 6

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

SELECT p.Name, od.ProductID,  
AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,  
SUM(od.OrderQty) AS Units  
FROM Sales.SalesOrderDetail AS od, Production.Product AS p  
WHERE od.ProductID = p.ProductID AND od.UnitPrice > 10  
GROUP BY p.Name, od.ProductID

クエリ 7

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

SELECT p.Name, od.ProductID,  
AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,  
SUM(od.OrderQty) AS Units 
FROM Sales.SalesOrderDetail AS od, Production.Product AS p 
WHERE od.ProductID = p.ProductID AND od.ProductID in (1,2,13,41) 
GROUP BY p.Name, od.ProductID

ビュー 4

このビューは、SumPrice および Count 列をビュー定義に含めることで、クエリ内の AVG を計算できるようにしているため、クエリ 6 の条件を満たします。

CREATE VIEW View4 WITH SCHEMABINDING AS  
SELECT p.Name, od.ProductID,  
SUM(od.UnitPrice*(1.00-UnitPriceDiscount)) AS SumPrice,  
SUM(od.OrderQty) AS Units, COUNT_BIG(*) AS Count  
FROM Sales.SalesOrderDetail AS od, Production.Product AS p  
WHERE od.ProductID = p.ProductID AND od.UnitPrice > 10 
GROUP BY p.Name, od.ProductID 
GO 
CREATE UNIQUE CLUSTERED INDEX VdiscountInd on View4 (Name, ProductID)

クエリ 8

クエリに Sales.SalesOrderHeader テーブルに対する結合が追加されても、同じビュー 4 のインデックスが使用されます。このクエリは、クエリの FROM 句に指定されているテーブルが、インデックス ビューの FROM 句のテーブルのスーパーセットであるという条件を満たすからです。

SELECT p.Name, od.ProductID,  
AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,  
SUM(od.OrderQty) AS Units  
FROM Sales.SalesOrderDetail AS od, Production.Product AS p,  
     Sales.SalesOrderHeader AS o  
WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID  
     AND od.UnitPrice > 10  
GROUP BY p.Name, od.ProductID

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

クエリ 8a

Q8a は、インデックス付きビューを使用できません。これは、ビュー定義内の「UnitPrice > 10」とクエリ内の「UnitPrice > 25」が一致せず、UnitPrice がビューに含まれていないからです。クエリの結合条件述語は、ビュー定義の結合条件述語のスーパーセットである必要があります。

SELECT p.Name, od.ProductID, AVG(od.UnitPrice*(1.00-UnitPriceDiscount))  
     AvgPrice, SUM(od.OrderQty) AS Units  
FROM Sales.SalesOrderDetail AS od, Production.Product AS p,  
     Sales.SalesOrderHeader AS o  
WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID  
     AND od.UnitPrice > 25  
GROUP BY p.Name, od.ProductID

クエリ 8b

テーブル Sales.SalesOrderHeader がインデックス付きビュー V4 の定義に含まれていないことを確認してください。これにもかかわらず、このテーブルの述語をテーブルに追加した場合は、インデックス付きビューを使用できません。これは、この追加された述語が、以下のクエリ 8b 内にある集計に使われる追加の業を変更または削除する可能性があるためです。

SELECT p.Name, od.ProductID, AVG(od.UnitPrice*(1.00-UnitPriceDiscount))  
     AS AvgPrice, SUM(od.OrderQty) AS Units  
FROM Sales.SalesOrderDetail AS od, Production.Product AS p, 
     Sales.SalesOrderHeader AS o  
WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID  
     AND od.UnitPrice > 10 AND o.OrderDate > '07/28/2004'  
GROUP BY p.Name, od.ProductID

ビュー 4a

ビュー 4a は、ビュー 4 のSELECT リストと GROUP BY 句に UnitPrice 列を指定したものです。UnitPrice 列の値に対する条件「UnitPrice > 10」が、さらに「UnitPrice > 25」の条件によって制約されるようになるので、クエリ 8a は、ビュー 4a を使用できます。これは、区間包含の例です。

CREATE VIEW View4a WITH SCHEMABINDING AS  
SELECT p.Name, od.ProductID, od.UnitPrice, 
SUM(od.UnitPrice*(1.00-UnitPriceDiscount)) AS SumPrice,  
SUM(od.OrderQty) AS Units, COUNT_BIG(*) AS Count  
FROM Sales.SalesOrderDetail AS od, Production.Product AS p  
WHERE od.ProductID = p.ProductID AND od.UnitPrice > 10  
GROUP BY p.Name, od.ProductID, od.UnitPrice 
GO 
CREATE UNIQUE CLUSTERED INDEX VdiscountInd  
     ON View4a (Name, ProductID, UnitPrice)

ビュー 5

ビュー 5 は、SELECT リストおよび GROUP BY リストにある式を含んでいます。LineTotal は計算列であるため、これ自体が式となることに注意してください。さらに、この式は、FLOOR 関数への呼び出し内に入れ子にされます。

CREATE VIEW View5 WITH SCHEMABINDING AS 
SELECT FLOOR(LineTotal) FloorTotal, COUNT_BIG(*) C 
FROM Sales.SalesOrderDetail 
GROUP BY FLOOR(LineTotal) 
GO 
CREATE UNIQUE CLUSTERED INDEX iView5 ON View5(FloorTotal)

クエリ 9

クエリ 9 は、SELECT リストおよび GROUP BY リスト内の式 FLOOR(LineTotal) を含んでいます。SQL Server 2005 で新たに拡張されたビューの式の照合機能により、このクエリには、ビュー 5 のインデックスを使用できます。

SELECT TOP 5 FLOOR(LineTotal), Count(*) 
FROM Sales.SalesOrderDetail 
GROUP BY FLOOR(LineTotal) 
ORDER BY COUNT(*) DESC

ビュー 6

ビュー 6 には、月末の 3 日間についての製品情報が含まれます。これは、少数のページからこれらの行を取得するため、この 3 日間の Sales.SalesOrderDetail に対するクエリは、すぐに実行が完了します。

CREATE VIEW View6 WITH SCHEMABINDING AS 
SELECT SalesOrderID, LineNumber, CarrierTrackingNumber, OrderQty,  
     ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid,  
     ModifiedDate 
FROM Sales.SalesOrderDetail 
WHERE ModifiedDate IN ('2004-07-31', '2004-07-30', '2004-07-29') 
GO 
CREATE UNIQUE CLUSTERED INDEX VEndJulyO4Ind  
     ON View6(SalesOrderID, LineNumber)

クエリ 10

以下のクエリは、ビュー 6 に一致するため、Sales.SalesOrderDetail テーブル全体をスキャンするのではなく、ビューの VendJuly04Ind インデックスをスキャンするプランを生成できます。このクエリでは、式の等価性 (日時の順番がクエリとビューでは異なっていて、データ形式も異なるため) と、述語の包含 (クエリが、ビューにある結果のサブセットを要求しているため) 機能も使用されます。

SELECT h.*, LineNumber, CarrierTrackingNumber, OrderQty, ProductID,  
     SpecialOfferID, UnitPrice, UnitPriceDiscount, d.rowguid,  
     d.ModifiedDate 
FROM Sales.SalesOrderHeader as h, Sales.SalesOrderDetail as d 
WHERE (d.ModifiedDate = '7/29/2004' OR d.ModifiedDate = '7/30/2004') 
and d.SalesOrderID=h.SalesOrderID

ビュー 7

インデックス付きビューを使用して特殊な参照整合性制約を適用することが便利な場合もあります。たとえば、インデックス付きビューに適用できる制限として、「テーブル T の列 a の値は一意になる。ただし、値が 0 の場合は、複数存在できる。」という制限があります。下のインデックス ビュー View7 は、この制限を適用します。以下のスクリプトを実行した場合、最後の挿入までは正常に実行されます。このステートメントにより、0 以外で重複する値が追加されるため、このステートメントの実行は許可されません。

USE tempdb 
GO 
CREATE TABLE T(a int) 
GO 
CREATE VIEW View7 WITH SCHEMABINDING 
AS SELECT a 
FROM dbo.T 
WHERE a <> 0 
GO 
CREATE UNIQUE CLUSTERED INDEX IV on View7(a) 
GO 
-- 正しい :
INSERT INTO T VALUES(1) 
INSERT INTO T VALUES(2) 
INSERT INTO T VALUES(0) 
INSERT INTO T VALUES(0) -- 0 が重複
  
-- 許可されない :
INSERT INTO T VALUES(2)

インデックス付きビューに関してよく寄せられる質問

Q. インデックスを作成できるビューの種類に制限があるのはなぜですか ?

A. 確実に、ビューの増分保守を論理的に可能にし、保守コストの高いビューの作成を制限し、SQL Server 実装の複雑さを制限するためです。ビューの多くは、非特定的であり、コンテキスト依存です。ビュー内の値は、DML 操作とは別に "変更" されます。このようなビューにインデックスを付けることはできません。例としては、ビュー定義で GETDATE や SUSER_SNAME が呼び出されるビューが挙げられます。

Q. ビューの最初のインデックスは、クラスタ化され、一意でなければならないのはなぜですか ?

A. インデックス付きビューの保守時に、キー値を使用してビューのレコードの照合を簡単に行えるようにし、保守に特殊なロジックが必要になる重複のあるビューの作成を防ぐためには、このインデックスは一意である必要があります。また、クラスタ化インデックスのみが、一意性を実現し、行の保存を同時にできるので、クラスタ化されている必要があります。

Q. 作成したインデックス付きビューが、クエリオプティマイザによってクエリプランで使用されるように選択されないのはなぜですか ?

A. クエリ オプティマイザによって、インデックス付きビューが選択されない主な理由は 3 つあります。

(1) Enterprise Edition または Developer Edition 以外の SQL Server を使用している。クエリとインデックス付きビューの自動照合機能をサポートしているのは、Enterprise Edition と Developer Edition のみです。その他の Edition でクエリ プロセッサがそのインデックス付きビューを使用するようにするには、このインデックス付きビューをビュー名を指定して参照し、NOEXPAND ヒントを使用します。

(2) インデックス付きビューを使用した場合の実行コストが、基本テーブルからデータを取得した場合の実行コストを上回っているか、クエリが非常に簡単なため、基本テーブルに対してクエリを実行しても、検索が迅速かつ簡単に実行できる場合。これは、インデックス付きビューが、小さいテーブルに対して定義されている場合に、頻繁に発生します。どうしてもクエリ プロセッサがインデックス付きビューを使用するようにする場合は、NOEXPAND ヒントを使用できます。クエリで明示的にビューを参照していなかった場合は、クエリを書き直す必要がある可能性があります。NOEXPAND を使用しているクエリの実際の実行コストを確認し、これをビューを参照しないクエリ プランの実際の実行コストと比較することもできます。この 2 つの値に差がない場合は、インデックス付きビューが使用されてもされなくても、影響がないということが確認できます。

(3) クエリ オプティマイザが、クエリをインデックス付きビューに照合していない。ビューの定義とクエリの定義をダブル クリックし、この 2 つが構造的に照合可能であることを確認してください。CAST や CONVERT などの式で、論理的にクエリの結果を変更できない場合は、照合はできません。また、式の正規化と、SQL Server が実行する等価性および包含の検証にも制限があります。等価である式が等価であると解析できなかったり、論理的に別の式に包含される式が実際に包含されていることが解析できない可能性があり、その結果、照合ができないことがあります。

Q. データウェアハウスを週に 1 度更新しています。 1 週間の間、インデックス付きビューによってクエリの実行速度が上がっても、毎週行う更新の実行速度は遅くなりますか ? どうしたらよいでしょうか ?

A. 毎週の更新を実行する前にインデックス付きビューを削除し、更新後に再度インデックス付きビューを作成することを検討してください。

Q. **ビューに重複がありますが、どうしてもこれを保守したいと考えています。**どうしたらよいでしょうか?

A. 保守するビューの列または式のすべてによってグループ化し、COUNT_BIG(*) 列を追加するビューを作成し、このグループ化列に対して一意クラスタ化インデックスを作成します。このグループ化処理により、一意性が確保されます。これは、元のビューとまったく同じではありませんが、ご要望をかなえることはできるでしょう。

Q. 別のビューに対して定義しているビューがあります。 SQL Server **では、最上位レベルのビューに対するインデックスを作成できません。**どうしたらよいでしょうか?

A. 入れ子になっているビューの定義を手動で拡張して、最上位レベルのビューの定義に合わせて、その後、これに対してインデックスを付けるか、一番内側のビューにインデックスを付けるか、ビューにインデックスを付けないことを検討してください。

Q. **インデックス付きビューの定義に、**WITH SCHEMABINDING を指定する必要があるのはなぜですか ?

A. 次の理由によります。

  • ビューが参照するすべてのオブジェクトが、このビューがどんなユーザーからアクセスされたとしても、schemaname.objectname を使用して、明確に特定できるようにするため。

  • ビューの定義で参照されているオブジェクトが、ビューの定義が無効になる、または SQL Server によりビューのインデックスが自動的に再作成されるような形で変更されないようにするため。

Q. OUTER JOIN をインデックス付きビューで使用できないのはなぜですか ?

A. OUTER JOIN を使用して作成されたインデックス付きビューは、基本テーブルにデータが挿入された場合、論理的に行を失う可能性があるためです。このため、OUTER JOIN ビューの増分更新は、実装が比較的複雑です。これを実装した場合、そのパフォーマンスは、通常の (INNER) JOIN を使用したビューよりも遅くなるでしょう。

関連情報

Microsoft SQL Server 2005 Books Online には、インデックス付きビューについてさらに詳しい情報が含まれています。追加情報については、以下のリソースを参照してください。

1 ビューは、常に、完全に作成される必要はありません。クエリはビュー(ここでのビューは事前に実体化されていないビュー)が参照するテーブルやビューに対して、追加の述語、結合、または集計を適用する事ができます。
2 オプティマイザにより、2 つの FROM 句が 1 つにまとめられるという特殊なケースがあります (結合を行うサブクエリや、変換を結合する派生テーブル)。このような場合は、インデックス ビューの置き換えにより、元のクエリにある複数の FROM 句が処理される可能性があります。
3 DTA は、SQL Server 2000 で提供されていたインデックス チューニング ウィザードを強化したものです。
4 SQL Server 2000 の場合は、インデックス付きビューを作成する前に、ARITHABORT を明示的に ON に設定してください。