スター・スキーマ・アクセス

Db2® は、スター型結合やペアワイズ結合などの特殊な結合方式を使用して、スター・スキーマを形成する表を効率よく結合することができます。

プログラム固有のプログラミング・インターフェース情報の開始
スター・スキーマ は、 意思決定支援アプリケーションに組み込まれる論理データベース設計です。スター・スキーマは、 ファクト表 と、それに接続された複数のディメンション表 で構成されます。ディメンション表には、すべての値ではなく、 ファクト表で使用される、ID が指定された複数の列が含まれます。

ディメンション表よりはるかに大きいファクト表は、中心に位置していると考えることができます。 ファクト表は、ディメンション表に囲まれています。 結果は星形のようになります。 次の図は、スター・スキーマによって作成される星形を示しています。

図 1. 1 つのファクト表と複数のディメンション表からなるスター・スキーマ
図の説明の開始。5 つの頂点がある星形とファクト表が中央で重なり合い、5 つのディメンション表がそれぞれ頂点で重なり合う図。
図の説明の終わり。

各ステップで 2 つの表のみを結合する他の結合方式 (ネストされたループ結合、マージ・スキャン結合、およびハイブリッド結合など) とは異なり、スター・スキーマ方式では、単一ステップに 3 つ以上の表を含めることができます。 必要な索引が存在する場合、Db2 は、スター型結合やペアワイズ結合などの特殊な方式を選択して、スター・スキーマ上の照会をより効率よく処理する場合があります。

ファクト表の索引には、以下のデータ・タイプの列のみを含める必要があります。

スター・スキーマの例

次の標準的な例では、スター・スキーマは、ファクト表 SALES と、 それに接続された複数のディメンション表 (期間、商品、および地理上のロケーションに関する) で構成されます。

TIME 表には、月、四半期、および年ごとに列があります。PRODUCT 表には、商品項目、そのクラス、およびその在庫ごとに列があります。 LOCATION 表には、市区町村と国を含む、地理的データの列があります。

図 2. 3 つのディメンション表を使用するスター・スキーマの例
図の説明の開始。3 つのディメンション表 (TIME、PRODUCT、および LOCATION) の中央にあるファクト表 SALES を示す図。図の説明の終わり。

このシナリオの SALES 表には、期間データに関する 3 つの列、商品データに関する 3 つの列、ロケーション・データに関する 2 つの列の代わりに、ディメンション表 TIME、PRODUCT、および LOCATION からの ID を示す 3 つの列のみがあります。このようにして、 ファクト表のサイズが大幅に削減されるわけです。さらに、項目を変更する必要がある場合は、ファクト表に多数の変更を加える代わりに、ディメンション表で 1 つの変更のみを加えます。

ディメンション表を正規化して複数の表にすることにより、 さらに複雑なスター・スキーマを作成できます。 正規化されたディメンション表は、スノーフレーク と呼ばれます。 スノーフレーク内のいずれか 1 つの表のみがファクト表と直接結合します。

3 つのディメンション表からなるスター型結合照会例

プログラム固有のプログラミング・インターフェース情報の開始
San Jose に店舗があって、同店の 2005 年のオーディオ機器の売上情報が必要であると仮定します。 この例の場合、以下の表を結合します。

  • SALES (S) のファクト表
  • TIME (T) のディメンション表。これには、ID、月、四半期、および年の列があります。
  • 地理上 LOCATION (L) のディメンション表。これには、ID、都市、地域、および国の列があります。
  • PRODUCT (P) のディメンション表。これには、ID、商品アイテム、クラス、および在庫の列があります。

次のような照会を作成すると、これらの表を結合できます。

SELECT * 
  FROM SALES S, TIME T, PRODUCT P, LOCATION L
  WHERE S.TIME = T.ID      AND
        S.PRODUCT = P.ID   AND
        S.LOCATION = L.ID  AND
        T.YEAR = 2005      AND
        P.CLASS = 'AUDIO'  AND
        L.LOCATION = 'SAN JOSE';

次の索引を使用します。

CREATE INDEX  XSALES_TPL ON SALES (TIME, PRODUCT, LOCATION);

EXPLAIN 出力は、次の表のようになります。

表 1. TIME、PRODUCT、および LOCATION を使用するスター型結合のプラン表出力の例
QUERYNO QBLOCKNO METHOD TNAME
JOIN_
TYPE
SORTN_
JOIN
ACCESS
TYPE
1 1 0 TIME S Y R
1 1 1 PRODUCT S Y R
1 1 1 LOCATION S Y R
1 1 1 SALES S   I

すべてのスノーフレークは、スター型結合の中央部の前に、個別の照会ブロックとして処理 され、マテリアライズされて作業ファイルになります。 スノーフレークごとに作業ファイルが存在し ます。EXPLAIN 出力では、これらの作業ファイルに DSN_DIM_TBLX(nn) の名前を付けて 表示します。ここで nn は、スノーフレークの対応する QBLOCKNO を示します。

次の例は、2 つのスノーフレークを含むスター型結合のプランを示しています。 前の例の表に 2 つの新規の表 MANUFACTURER (M) と COUNTRY (C) を追加し、ディメンション表 PRODUCT (P) と LOCATION (L) をスノーフレークに 分割とします。

  • PRODUCT 表は、製造メーカーを表す新規の列 MID を持っています。
  • 表 MANUFACTURER (M) は、製造メーカーの情報を含めるために、MID と名前の列を持って います。
  • LOCATION 表は、国を表す新規の列 CID を持っています。
  • 表 COUNTRY (C) は、国の情報を含めるために、CID と名前の列を持っています。

次のような照会を作成すると、すべての表を結合できます。

SELECT *
  FROM SALES S, TIME T, PRODUCT P, MANUFACTURER M, 
       LOCATION L, COUNTRY C
  WHERE S.TIME = T.ID      AND
        S.PRODUCT = P.ID   AND
        P.MID = M.MID      AND
        S.LOCATION = L.ID  AND
        L.CID = C.CID      AND
        T.YEAR = 2005      AND
        M.NAME = 'some_company';

結合された対の表 (PRODUCT、MANUFACTURER) と (LOCATION、COUNTRY) は、スノーフレー クです。 この照会の EXPLAIN 出力は、次の表のようになります。

表 2. スノーフレークを持つスター型結合例のプラン表の出力
QUERYNO QBLOCKNO METHOD TNAME
JOIN
TYPE
SORTN
JOIN
ACCESS
TYPE
1 1 0 TIME S Y R
1 1 1 DSN_DIM_TBLX(02) S Y R
1 1 1 SALES S   I
1 1 1 DSN_DIM_TBLX(03)   Y T
1 2 0 PRODUCT     R
1 2 1 MANUFACTURER     I
1 3 0 LOCATION     R
1 3 4 COUNTRY     I
注: この照会は、次の 3 つの照会ブロックから構成されます。
  • QBLOCKNO=1: メインのスター型結合ブロック
  • QBLOCKNO=2: 作業ファイル DSN_DIM_TBLX(02) にマテリアライズされるスノーフ レーク (PRODUCT、MANUFACTURER)
  • QBLOCKNO=3: 作業ファイル DSN_DIM_TBLX(03) にマテリアライズされるスノーフレーク (LOCATION、COUNTRY)

スノーフレーク内の結合が最初に処理され、各スノーフレークがマテリアライズされて作業ファイルになります。 したがって、メインのスター型結合ブロック (QBLOCKNO=1) が処理された時点では、4 つ の表が含まれています。つまり、SALES (ファクト表)、TIME (ディメンション表)、および 2 つのスノーフレーク作業ファイルです。

この例で、メインのスター型結合ブロック内では、最初の 3 つの表に対してスター型結合 方式が使用され (プラン表の JOIN_TYPE 列に S で表示)、残りの作業ファイルは、作業ファ イルに対する疎索引アクセスを使用して (DSN_DIM_TBLX(3) の ACCESSTYPE 列に T で表示) ネスト・ループ結合によって結合されます。

プログラム固有のプログラミング・インターフェース情報の終了

Db2 がスター・スキーマ・アクセスを使用する場合

スター・スキーマのデータにアクセスするために、 ファクト表とディメンション表との間の結合操作を組み込んだ SELECT ステートメントを しばしば作成しますが、ディメンション表間の結合操作は組み込みま せん。Db2 は、以下の条件が該当する場合は、 スター・スキーマ処理を照会の結合タイプとして使用します。

  • ファクト表、ディメンション表、およびスノーフレーク表を含む、 スター・スキーマ照会ブロック内の表の数が、SJTABLES サブシステム・パラメーターの値より大きいか、等しい。
  • サブシステム・パラメーター STARJOIN の値が 1 か、あるいは、最大のディメンション表に対するファクト表の カーディナリティーが、サブシステム・パラメーターの値によって指定された要件を満たしている。 STARJOIN の値と カーディナリティーの値の要件は次のとおりです。
    DISABLED
    スター・スキーマ処理は使用不可。これがデフォルトです。
    ENABLED
    ファクト表のカーディナリティーが、少なくとも、そのファクト表に結合されている基本表である最大ディメンションの カーディナリティーの 25 倍であれば、スター・スキーマ処理は使用可能。
    1
    スター・スキーマ処理は使用可能。カーディナリティーが最大の表がファクト表です。ただし、このカーディナリティーに複数の表がある場合、スター型結合は使用不能となります。
    n (2 ≤ n ≤ 32768)。
    ファクト表のカーディナリティーが、少なくとも、そのファクト表に結合されている基本表である最大ディメンションの カーディナリティーの n 倍であれば、スター・スキーマ処理は使用可能。

SJTABLES および STARJOIN の設定に加えて、以下の条件も満たす必要があります。

  • 照会で少なくとも 2 つのディメンションを参照している。
  • すべての結合述部が、ファクト表とディメンション表の間、 または同じスノーフレークの表内に存在する。 スノーフレークがファクト表に接続されている場合に、ファクト表に結合できる スノーフレーク内の表は 1 つのみです (中央のディメンション表)。
  • ファクト表とディメンション表との間にあるすべての結合述部は、等価結合の述部である。
  • ファクト表とディメンション表との間にあるすべての結合述部は、ブール項の述部である。
  • OR 論理演算子で結合された、ディメンション表のローカル述部と異なる表のローカル述部からなる述部がない。
  • 相関副照会でディメンションを越えない。
  • ファクト表列が結合述部内で異なるディメンション表の列に 1 つも結合されていない。 例えば、 ファクト表列 F1 は、ディメンション表 T1 の列 D1 に結合することも、ディメンション表 T2 の 列 D2 に結合することもできません。
  • Db2 が結合操作を単純化した後に存在する外部結合操作はない。
  • 結合述部の両サイドのデータ・タイプと長さが同じである。
  • ファクト表の索引には、以下のデータ・タイプの列のみが含まれます。
    • CHARACTER
    • DATE
    • DECIMAL
    • DOUBLE
    • INTEGER
    • SMALLINT
    • REAL
    • TIME
    • TIMESTAMP
    • VARCHAR

スター型結合は、バインド時間を大きく削減できますが、どのような状況でも最適な パフォーマンスが得られるわけではありません。 スター型結合のパフォーマンスは、ファクト表に対する索引の可用性、索引のクラスター比率、ローカル述部や結合述部の選択度など、さまざまな要因に依存します。 SJTABLES の値の設定は、次の一般ガイドラインに従ってください。

  • スター・スキーマ・データベース内で 10 個未満の表を参照する照会があり、 スター型結合方式をすべての条件付き照会に適用可能にしたい場合は、 スター型結合用に考慮する照会に使用する表の最小数になるように SJTABLES の値を設定します。

    例えば、ファクト表を 1 つ、ディメンション表を 3 つ持つスター・スキーマ・データベースを照会するとします。SJTABLES を 4 に設定します。

  • 比較的大きい照会にスター型結合を使用する場合で、その照会がスター・スキーマ・データベースを 参照するが、必ずしもスター型結合に適さない場合、デフォルトを使用してくだ さい。10 個以上の表を持つすべての条件付き照会に対して、スター型結合方式が考慮されます。
  • スター・スキーマ・データベースを参照する照会があるものの、通常はスター型結合を使用しない場合、大規模照会のバインド時間を大幅に削減し、大規模な条件付き照会に対するバインド時に -101 SQL 戻りコードが生じる可能性を回避するには、SJTABLES を高めの数値 (例えば、15) に設定することを検討してください。