ヘッダーをスキップ
Oracle Databaseデータ・ウェアハウス・ガイド
10gリリース2(10.2)
B19217-02
  目次へ
目次
索引へ
索引

前へ
前へ
次へ
次へ
 

19 スキーマのモデリング化技法

この章では、データ・ウェアハウスのスキーマについて説明します。内容は次のとおりです。

データ・ウェアハウスのスキーマ

スキーマとは、表、ビュー、索引およびシノニムを含むデータベース・オブジェクトのコレクションです。

データ・ウェアハウス用に設計されたスキーマ・モデルにスキーマ・オブジェクトを配置するためには、様々な方法があります。データ・ウェアハウス・スキーマの1つは、スター・スキーマです。shサンプル・スキーマ(このマニュアルに記載するほとんどの例の基本)では、スター・スキーマを使用します。ただし、データ・ウェアハウスに一般的に使用されるその他のスキーマ・モデルもあります。そのようなスキーマ・モデルのうち最も一般的なのは、第3正規形(3NF)スキーマです。さらに、スター・スキーマでも3NFスキーマでもなく、かわりにこの2つのスキーマの特性を共有するハイブリッド・スキーマ・モデルと呼ばれるスキーマもあります。

Oracle Databaseは、すべてのデータ・ウェアハウス・スキーマをサポートするよう設計されています。機能によっては、1つのスキーマ・モデルに固有のものもあります(たとえば、スター変換機能はスター・スキーマに固有です。これは、「スター型変換の使用」に記載されています)。ただし、Oracleのデータ・ウェアハウス機能の大多数は、スター・スキーマ、3NFスキーマおよびハイブリッド・スキーマに同じように適用できます。パーティション化(ローリング・ウィンドウのロード手法を含む)、パラレル化、マテリアライズド・ビュー、分析SQLなどの主なデータ・ウェアハウス機能は、すべてのスキーマ・モデルに実装されています。

データ・ウェアハウスにどのスキーマ・モデルを使用するかの判断は、要件とデータ・ウェアハウス・プロジェクト・チームの選択によって決まります。代替スキーマ・モデルの利点の比較は、本書の範囲外です。かわりに、この章では、各スキーマ・モデルを簡単に紹介し、それらの環境に対してOracleを最適化する方法を提示します。

第3正規形

本書では、例として主にスター・スキーマを使用していますが、データ・ウェアハウスの実装に第3正規形を使用することもできます。

第3正規形モデルは、正規化を介してデータの冗長性を最小限に抑える古典的なリレーショナル・データベース・モデリング手法です。3NFスキーマでは、通常、スター・スキーマと比べると正規化処理のために表の数が多くなります。たとえば、図19-1で、orders表およびorder items表には、図19-2のスター・スキーマのsales表と同じ情報が含まれています。

通常、3NFスキーマは、大規模なデータ・ウェアハウス、特に、データのロード要求が多く、データ・マートへのデータの入力および長時間実行問合せの実行に使用される環境用として選択されます。

3NFスキーマの主なメリットは、次のとおりです。

図19-1に、第3正規形を示します。

図19-1 第3正規形

図19-1の説明は図の下のリンクをクリックしてください。
「図19-1 第3正規形」の説明

第3正規形の問合せの最適化

3NFスキーマでは、問合せが非常に複雑になり多数の表を必要とする場合がよくあります。このため、大規模な表の結合のパフォーマンスが、3NFスキーマを使用する際の主な考慮事項になります。

3NFスキーマの特に重要な機能の1つは、パーティション・ワイズ結合です。3NFスキーマの最大の表は、パーティション・ワイズ結合を使用可能にするためにパーティション化する必要があります。これらの環境で最も一般的なパーティション化手法は、最大の表に対するコンポジット・レンジ-ハッシュ・パーティション化で、最も一般的な結合キーがハッシュ・パーティション化キーとして選択されます。

3NF環境ではパラレル化が非常によく利用されるため、これらの環境では一般にパラレル化を使用可能にする必要があります。

スター・スキーマ

スター・スキーマは、おそらく最も単純なデータ・ウェアハウス・スキーマです。スター・スキーマをエンティティ関連図で表すと、星(スター)のように中央の表から点が放射状に広がっているため、スター・スキーマと呼ばれます。スターの中心は1つの大規模なファクト表で構成されており、スターの先端はディメンション表になっています。

スター・クエリーは、ファクト表といくつかのディメンション表を結合するものです。各ディメンション表は、主キーから外部キーへの結合を使用してファクト表に結合されますが、ディメンション表同士は結合されません。オプティマイザによってスター・クエリーが認識されると、スター・クエリーのための効率的な実行計画が生成されます。

一般的なファクト表には、キーおよびメジャーが含まれます。たとえばshサンプル・スキーマの場合、ファクト表salesには、メジャーとしてquantity_soldamountおよびcostが含まれ、キーとしてcust_idtime_idprod_idchannel_idおよびpromo_idが含まれます。ディメンション表は、customerstimesproductschannelsおよびpromotionsです。たとえば、productsディメンション表には、ファクト表に表示される各製品番号に関する情報があります。

スター型結合とは、ディメンション表とファクト表の主キーと外部キーの結合です。

スター・スキーマの主なメリットは、次のとおりです。

スター・スキーマは、単純なデータ・マートと非常に大規模なデータ・ウェアハウスの両方に使用されます。

図19-2に、スター・スキーマを示します。

図19-2 スター・スキーマ

図19-2の説明は図の下のリンクをクリックしてください。
「図19-2 スター・スキーマ」の説明

スノーフレーク・スキーマ

スノーフレーク・スキーマは、スター・スキーマより複雑なデータ・ウェアハウス・モデルであり、スター・スキーマの一種です。このスキーマの図がスノーフレーク(雪片)に似ているため、スノーフレーク・スキーマと呼ばれます。

スノーフレーク・スキーマでは、ディメンションが正規化され、冗長性が排除されます。つまり、ディメンション・データは1つの大規模な表ではなく複数の表にグルーピングされます。たとえば、スター・スキーマのproductディメンション表は、スノーフレーク・スキーマのproducts表、product_category表およびproduct_manufacturer表に正規化できます。これによって、領域が節約されますが、ディメンション表の数が増加し、より多くの外部キー結合が必要になります。その結果、問合せがより複雑になり、問合せのパフォーマンスが低下します。図19-3に、スノーフレーク・スキーマを示します。

図19-3 スノーフレーク・スキーマ

図19-3の説明は図の下のリンクをクリックしてください。
「図19-3 スノーフレーク・スキーマ」の説明


注意:

特に理由がなければ、スノーフレーク・スキーマではなくスター・スキーマを選択することをお薦めします。

スター・クエリーの最適化

スター・クエリーを使用する場合は、次の考慮点があります。

スター・クエリーのチューニング

スター・クエリーのパフォーマンスを最大限に向上させるためには、次の基本的なガイドラインに従う必要があります。

  • ビットマップ索引をファクト表の各外部キー列上に作成する必要があります。

  • 初期化パラメータSTAR_TRANSFORMATION_ENABLEDTRUEに設定する必要があります。これにより、スター・クエリーのための重要なオプティマイザ機能が使用可能になります。この機能は、下位互換性のためにデフォルトでFALSEに設定されています。

データ・ウェアハウスがこれらの条件を満たす場合、そのデータ・ウェアハウスで実行しているほとんどのスター・クエリーは、スター型変換と呼ばれる問合せ実行計画を使用します。スター型変換によって、スター・クエリーの問合せパフォーマンスが向上します。

スター型変換の使用

スター型変換は、元のスター・クエリーのSQLを暗黙的にリライト(または変換)することによる、強力な最適化テクニックです。エンド・ユーザーがスター型変換の詳細を知る必要はありません。Oracleのクエリー・オプティマイザでは、該当する場合にスター型変換が自動的に選択されます。

スター型変換は、スター・クエリーを効率的に実行することを目的とした問合せ変換です。Oracleでは、2つの基本フェーズを使用してスター・クエリーが処理されます。第1フェーズでは、ファクト表から必要な行(結果セット)のみを取り出します。この取出しにはビットマップ索引が使用されるため、非常に効率的です。第2フェーズでは、この結果セットをディメンション表に結合します。この例では、「西部および南西部販売地域における過去3四半期の食料品部門の売上および利益はどうであったか」について問い合せています。これは、1つの単純なスター・クエリーです。

ビットマップ索引を使用したスター型変換

スター型変換の前提条件は、ファクト表の各結合列に単一列のビットマップ索引が存在することです。これらの結合列には、すべての外部キー列が含まれます。

たとえば、shサンプル・スキーマのsales表の場合、time_id列、channel_id列、cust_id列、prod_id列およびpromo_id列にビットマップ索引が定義されています。

次のスター・クエリーを考えてみます。

SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
   SUM(s.amount_sold) sales_amount
FROM sales s, times t, customers c, channels ch
WHERE s.time_id = t.time_id
AND   s.cust_id = c.cust_id
AND   s.channel_id = ch.channel_id
AND   c.cust_state_province = 'CA'
AND   ch.channel_desc in ('Internet','Catalog')
AND   t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;

この問合せは、2つのフェーズで処理されます。第1フェーズでは、Oracle Databaseは、ファクト表の外部キー列上のビットマップ索引を使用して、ファクト表から必要な行のみを識別し、取り出します。つまり、Oracle Databaseは、主に次の問合せを使用して、ファクト表から結果セットを取り出します。

SELECT ... FROM sales
WHERE time_id IN
  (SELECT time_id FROM times
   WHERE calendar_quarter_desc IN('1999-Q1','1999-Q2'))
   AND cust_id IN
  (SELECT cust_id FROM customers WHERE cust_state_province='CA')
   AND channel_id IN
  (SELECT channel_id FROM channels WHERE channel_desc IN('Internet','Catalog'));

元のスター・クエリーがこの副問合せ表現に変換されたことから、これがアルゴリズムの変換ステップになります。ファクト表にアクセスするこの方法は、ビットマップ索引の効果を高めます。ビットマップ索引は、リレーショナル・データベース内に集合ベースの処理方法を提供します。Oracleでは、AND(標準的な集合についての用語で共通部分の意味)、OR(集合の用語で和集合)、MINUSCOUNTなどの集合演算を実行するための非常に高速な方法が実装されています。

このスター・クエリーでは、time_idのビットマップ索引を使用して、1999年第1四半期(1999-Q1)の売上(sales)に対応するファクト表内のすべての行集合が識別されます。この集合は、ビットマップ(ファクト表のどの行がこの集合のメンバーであるかを示す1および0(ゼロ)の文字列)として表されます。

同様のビットマップが、1999年第2四半期(1999-Q2)の売上に対応するファクト表の行に取り出されます。ビットマップOR演算を使用して、このQ1の売上集合をQ2の売上集合と組み合せます。

追加の集合演算が、customerディメンションおよびproductディメンションに対して実行されます。この時点で、スター・クエリー処理には3つのビットマップがあります。各ビットマップは、別々のディメンション表に対応し、それぞれ、個々のディメンションの絞込み条件を満たすファクト表の行集合を表します。

これらの3つのビットマップは、ビットマップAND演算を使用して単一ビットマップに結合されます。この最後のビットマップは、ファクト表のうちディメンション表上のすべての絞込み条件を満たす行集合を表します。これは結果セットであり、問合せの評価に必要なファクト表からの正確な行集合です。ここまでのところでは、ファクト表の実際のデータには、まだアクセスしていないことに注意してください。これらの演算はすべて、ビットマップ索引とディメンション表のみを基にしています。ビットマップ索引は、データを圧縮した形で表すため、ビットマップの集合ベース演算は非常に効率的です。

結果セットが識別されると、ビットマップはSALES表から実データへのアクセスに使用されます。エンド・ユーザーの問合せに必要な行のみが、ファクト表から取り出されます。この時点で、すべてのディメンション表が、ビットマップ索引を使用してファクト表に効率的に結合されています。Oracleでは、各ディメンション表をファクト表に個別に結合するのではなく、すべてのディメンション表を単一の論理結合演算でファクト表に結合しているため、この技法を使用すると優れたパフォーマンスが得られます。

この問合せの第2フェーズでは、ファクト表の行(結果セット)をディメンション表に結合します。Oracleは、最も効率的な方法を使用して、ディメンション表にアクセスおよび結合します。ほとんどのディメンションは非常に小規模なため、これらのディメンション表への最も効率的なアクセス方法は、通常、表スキャンです。大規模なディメンション表については、表スキャンは最も効率的なアクセス方法ではない場合があります。前述の例では、product.departmentのビットマップ索引を使用して、食料品部門のすべての製品が高速で識別されます。Oracleでは、各ディメンション表のサイズおよびデータ分散に関するオプティマイザの知識に基づいて、オプティマイザが、特定のディメンション表に最適のアクセス方法を自動的に判断します。

同様に、各ディメンション表用の特定の結合方法(および索引付け方法)も、オプティマイザによってインテリジェントに判断されます。ディメンション表を結合するための最も効率的なアルゴリズムがハッシュ結合である場合がよくあります。すべてのディメンション表が結合されると、最終結果がユーザーに戻されます。1つの表から一致する行のみを取り出してから、別の表に結合する問合せテクニックは、一般にセミ結合と呼ばれます。

ビットマップ索引を使用したスター型変換の実行計画

「ビットマップ索引を使用したスター型変換」から得られる典型的な実行計画は次のようになります。

SELECT STATEMENT
 SORT GROUP BY
  HASH JOIN
   TABLE ACCESS FULL                          CHANNELS
   HASH JOIN
    TABLE ACCESS FULL                         CUSTOMERS
    HASH JOIN
     TABLE ACCESS FULL                        TIMES
     PARTITION RANGE ITERATOR
      TABLE ACCESS BY LOCAL INDEX ROWID       SALES
       BITMAP CONVERSION TO ROWIDS
        BITMAP AND
         BITMAP MERGE
          BITMAP KEY ITERATION
           BUFFER SORT
            TABLE ACCESS FULL                 CUSTOMERS
           BITMAP INDEX RANGE SCAN            SALES_CUST_BIX
         BITMAP MERGE
          BITMAP KEY ITERATION
           BUFFER SORT
            TABLE ACCESS FULL                 CHANNELS
           BITMAP INDEX RANGE SCAN            SALES_CHANNEL_BIX
         BITMAP MERGE
          BITMAP KEY ITERATION
           BUFFER SORT
            TABLE ACCESS FULL                 TIMES
           BITMAP INDEX RANGE SCAN            SALES_TIME_BIX

この計画では、ファクト表は、ビットマップ・アクセス・パスを介してアクセスされます。このパスは、3つのマージされたビットマップのビットマップANDに基づきます。この3つのビットマップは、下位の行ソース・ツリーからビットマップが提供されているBITMAP MERGE行ソースによって生成されます。このような各行ソース・ツリーは、副問合せ行ソース・ツリーの値をフェッチするBITMAP KEY ITERATION行ソースで構成されています。この例では、副問合せ行ソース・ツリーは、1つの全表アクセスです。このような各値については、BITMAP KEY ITERATION行ソースがビットマップをビットマップ索引から取り出します。関係するファクト表の行は、このアクセス・パスを使用して取り出された後に、問合せ結果を生成するためにディメンション表および一時表と結合されます。

ビットマップ結合索引を使用したスター型変換

スター型変換において、ビットマップ索引の他にビットマップ結合索引も使用できます。次の追加の索引構造があるとします。

CREATE BITMAP INDEX sales_c_state_bjix
ON sales(customers.cust_state_province)
FROM sales, customers
WHERE sales.cust_id = customers.cust_id
LOCAL NOLOGGING COMPUTE STATISTICS;

同じスター・クエリーでビットマップ結合索引を使用した場合の処理は、前述の例に似ています。唯一の違いは、Oracleでは単一表のビットマップ索引が使用されるかわりに、結合インデックスを使用してスター・クエリーの第1フェーズで顧客データにアクセスすることです。

ビットマップ結合索引を使用したスター型変換の実行計画

「ビットマップ結合索引を使用したスター型変換」から得られる典型的な実行計画は次のようになります。

SELECT STATEMENT
 SORT GROUP BY
  HASH JOIN
   TABLE ACCESS FULL                          CHANNELS
   HASH JOIN
    TABLE ACCESS FULL                         CUSTOMERS
    HASH JOIN
     TABLE ACCESS FULL                        TIMES
     PARTITION RANGE ALL
      TABLE ACCESS BY LOCAL INDEX ROWID       SALES
       BITMAP CONVERSION TO ROWIDS
        BITMAP AND
         BITMAP INDEX SINGLE VALUE            SALES_C_STATE_BJIX
         BITMAP MERGE
          BITMAP KEY ITERATION
           BUFFER SORT
            TABLE ACCESS FULL                 CHANNELS
           BITMAP INDEX RANGE SCAN            SALES_CHANNEL_BIX
         BITMAP MERGE
          BITMAP KEY ITERATION
           BUFFER SORT
            TABLE ACCESS FULL                 TIMES
           BITMAP INDEX RANGE SCAN            SALES_TIME_BIX

この計画と前述の計画との唯一の違いは、customerディメンションのビットマップ索引スキャン内の選択のための処理がないことです。これは、customer.cust_state_provinceの結合述語情報を、ビットマップ結合索引sales_c_state_bjixで満たすことができるためです。

Oracleによるスター型変換の使用の選択

オプティマイザは、変換なしでも生成できる最適な計画を生成して保存します。変換が使用可能な場合、オプティマイザは、変換が問合せに適用可能であれば、変換された問合せを使用して最適な計画を生成します。オプティマイザは、この2つの問合せに対する最適な計画のコスト概算を比較して、変換または未変換の最適な計画のどちらを使用するかを決定します。

問合せがファクト表の行の大部分にアクセスする必要がある場合は、変換ではなく、全表スキャンを使用する方がよい場合があります。ただし、ディメンション表について、選択による絞込み度合いが高く、ファクト表のわずかな部分のみを取り出す必要がある場合は、変換に基づく計画の方が適していることもあります。

オプティマイザは、多くの基準に基づいて適切であると判断した場合にのみ、ディメンション表に対して副問合せを生成します。副問合せがすべてのディメンション表に対して生成されるわけではありません。また、オプティマイザが、表および問合せの性質に基づいて、ある問合せに変換を適用するメリットがないと判断する場合もあります。このような場合は、最適な通常の計画が使用されます。

スター型変換の制限

スター型変換は、次の特性が1つでもある表ではサポートされません。

  • ビットマップ・アクセス・パスと非互換の表ヒントがある問合せ。

  • バインド変数を含む問合せ。

  • ビットマップ索引が少なすぎる表。オプティマイザが副問合せを生成するためには、ファクト表の列にビットマップ索引がある必要があります。

  • リモート・ファクト表。ただし、リモート・ディメンション表は、生成された副問合せでは有効です。

  • アンチ結合された表。

  • 副問合せでディメンション表として使用済の表。

  • ビュー・パーティションではなく、実際はマージされていないビューである表。

次の場合には、オプティマイザではスター型変換が選択されない場合があります。

  • 効率的な単一表アクセス・パスを持つ表。

  • 小さすぎて変換によるメリットがない表。

さらに、次の条件下では、スター型変換で一時表は使用されません。

  • データベースが読取り専用モードの場合。

  • スター・クエリーがシリアル化可能モードでのトランザクションの一部である場合。