フラッタンテーブルとは

1 はじめに
2 フラッタンテーブルとは
3 フラッタンテーブルの効果
4 フラッタンテーブルの作成方法
- 4.1 構文
- 4.2 作成例
5 検証バージョンについて
6 更新履歴

はじめに

一般的に、正規化されたデータベース設計ではスタースキーマまたはスノーフレーク・スキーマ・モデルを使用することが多く、複数の大きなファクト表と多数の小さなディメンション表で構成されます。
また、検索時には、大きなファクト表と複数のディメンション表間の結合処理が行われます。
テーブルの数と結合されるデータの量によっては、クエリ実行時に大きなオーバーヘッドが発生する可能性があります。

このオーバーヘッドを回避するために、クエリによって必要とされる全てのファクト表とディメンション表の列を結合する非正規化テーブルを作成する場合があります。
このテーブルはクエリの実行を大幅に高速化しますが、正規化されたデータと非正規化されたデータの冗長なセット（テーブル）を維持することは、独自の管理コストを伴います。

Vertica 8.1から利用できるフラッタンテーブルは、上述した結合処理のオーバーヘッドと管理コストを削減するための機能です。
本記事ではフラッタンテーブルの基本的な使用方法をご紹介します。

フラッタンテーブルとは、従来の正規化されたファクト表に非正規化列を追加したテーブルです。
正規化されたデータベース設計はそのまま利用できます。

フラッタンテーブルの効果

フラッタンテーブルには、以下の2つのメリットがあります。

正規化されたテーブルを結合する検索処理と比較して、
　　フラッタンテーブルに対する検索処理はファクト表とディメンジョン表の列を事前に結合して
　　保持しているため、結合のオーバーヘッドが無くなる分、高速になる

手動で非正規化テーブルを作成した場合と比較して、
　　メンテナンス工数を削減できる

フラッタンテーブルの検索性能

フラッタンテーブルの検索性能については、以下の記事をご参照ください。
http://vertica-tech.ashisuto.co.jp/flattened-table-query/

メンテナンス工数の削減

手動で非正規化テーブルを作成した場合と、フラッタンテーブルを使用した場合の処理内容を以下で比較します。
図に記載しているテーブルの内容は以下のとおりです。

図中のテーブル名称	テーブルの役割
Wテーブル	ファクト表（明細表）
Iテーブル	ディメンジョン表（マスタ表）
Dテーブル	ディメンジョン表（マスタ表）
Eテーブル	ディメンジョン表（マスタ表）
Rテーブル	ディメンジョン表（マスタ表）
WIDERテーブル	非正規化したファクト表（明細表）

手動で非正規化テーブルを作成した場合

手動で非正規化テーブルを作成した場合、ファクト表、各ディメンジョン表とは別に非正規化したファクト表を作成する必要があります。
また、非正規化したファクト表へデータをロードするためには、事前にファクト表へデータをロードすることが一般的なため、処理が2ステップになります。
将来的にファクト表の列追加や列変更が発生した場合、非正規化したファクト表自体とそれに対するデータロード処理の2つに対して変更作業を実施しなければならないデメリットがあります。

フラッタンテーブルの場合

フラッタンテーブルを使用した場合、Verticaの機能でファクト表を非正規化するため、個別に非正規化したファクト表を作成する必要がありません。
このため、手動で非正規化テーブルを作成した場合と比較して、ファクト表に対する列追加や列変更のメンテナンス工数が削減されます。
なお、非正規化したファクト表への追加／更新データを反映するためには、refresh_columnコマンドを実行します。

フラッタンテーブルの留意点

フラッタンテーブルは事前に結合結果を保持する特性上、以下の2点に留意する必要があります。

非正規化テーブルのため、通常のテーブルと比較して使用ディスク量が多い

フラッタンテーブルの情報を最新に更新するために、リフレッシュ処理を行う必要がある

フラッタンテーブルの作成方法

フラッタンテーブルには2種類のデータ更新（リフレッシュ）方法があり、それぞれ「DEFAULT」句と「SET USING」句を使用します。
DEFAULT句とSET USING句は以下のように使い分けます。

DEFAULT句　…　非正規化列にデフォルト値としてデータを反映する場合
　　　　　　　　　　　　　　（定常的なデータ更新が必要ない場合）

SET USING句　… 非正規化列に対するデータ更新が定常的に必要な場合

構文

CREATE TABLE <ファクト表の表名> (
  <列名１>       ……
  <非正規化列の列名２>  <データ型>  DEFAULT ( SELECT <ディメンジョン表の列名> FROM <ディメンジョン表の表名> WHERE (<ファクト表の結合列> = <ディメンジョン表の結合列>) )  /* DEFAULT句の場合 */
  <非正規化列の列名３>  <データ型>  SET USING ( SELECT <ディメンジョン表の列名> FROM <ディメンジョン表の表名> WHERE (<ファクト表の結合列> = <ディメンジョン表の結合列>) )  /* SET USING句の場合 */
  <列名４>       ……
  ……
);

CREATE TABLE <ファクト表の表名> (

<列名１> ……

<非正規化列の列名２> <データ型> DEFAULT ( SELECT <ディメンジョン表の列名> FROM <ディメンジョン表の表名> WHERE (<ファクト表の結合列> = <ディメンジョン表の結合列>) ) /* DEFAULT句の場合 */

<非正規化列の列名３> <データ型> SET USING ( SELECT <ディメンジョン表の列名> FROM <ディメンジョン表の表名> WHERE (<ファクト表の結合列> = <ディメンジョン表の結合列>) ) /* SET USING句の場合 */

<列名４> ……

……

);

作成例

フラッタンテーブルではない元のCREATE TABLE文、DEFAULT句を使用したフラッタンテーブル、SET USING句を使用したフラッタンテーブルの作成例を記載しています。

元のCREATE TABLE文

CREATE TABLE ssbm.lineorder (
  lo_orderkey         NUMERIC(10),
  /* 中略 */
  lo_shipmode         CHAR(10)
);

CREATE TABLE ssbm.lineorder (

lo_orderkey NUMERIC(10),

/* 中略 */

lo_shipmode CHAR(10)

);

DEFAULT句を使用したフラッタンテーブル

CREATE TABLE ssbm.lineorder (
  lo_orderkey         NUMERIC(10),
  /* 中略 */
  lo_shipmode         CHAR(10),
  lo_region           CHAR(12) DEFAULT (
                        SELECT ssbm.supplier.s_region
                        FROM ssbm.supplier
                        WHERE ssbm.lineorder.lo_suppkey = ssbm.supplier.s_suppkey),
  lo_category         CHAR(7) DEFAULT (
                        SELECT ssbm.part.p_category
                        FROM ssbm.part
                        WHERE ssbm.lineorder.lo_partkey = ssbm.part.p_partkey)
);

CREATE TABLE ssbm.lineorder (

lo_orderkey NUMERIC(10),

/* 中略 */

lo_shipmode CHAR(10),

lo_region CHAR(12) DEFAULT (

SELECT ssbm.supplier.s_region

FROM ssbm.supplier

WHERE ssbm.lineorder.lo_suppkey = ssbm.supplier.s_suppkey),

lo_category CHAR(7) DEFAULT (

SELECT ssbm.part.p_category

FROM ssbm.part

WHERE ssbm.lineorder.lo_partkey = ssbm.part.p_partkey)

);

SET USING句を使用したフラッタンテーブル

CREATE TABLE ssbm.lineorder (
  lo_orderkey         NUMERIC(10),
  /* 中略 */
  lo_shipmode         CHAR(10),
  lo_region           CHAR(12) SET USING (
                        SELECT ssbm.supplier.s_region
                        FROM ssbm.supplier
                        WHERE ssbm.lineorder.lo_suppkey = ssbm.supplier.s_suppkey),
  lo_category         CHAR(7) SET USING (
                        SELECT ssbm.part.p_category
                        FROM ssbm.part
                        WHERE ssbm.lineorder.lo_partkey = ssbm.part.p_partkey)
);

CREATE TABLE ssbm.lineorder (

lo_orderkey NUMERIC(10),

/* 中略 */

lo_shipmode CHAR(10),

lo_region CHAR(12) SET USING (

SELECT ssbm.supplier.s_region

FROM ssbm.supplier

WHERE ssbm.lineorder.lo_suppkey = ssbm.supplier.s_suppkey),

lo_category CHAR(7) SET USING (

SELECT ssbm.part.p_category

FROM ssbm.part

WHERE ssbm.lineorder.lo_partkey = ssbm.part.p_partkey)

);

検証バージョンについて

この記事の内容はVertica 8.1で確認しています。

更新履歴

2017/11/07　本記事を公開

投稿タグ: フラッタンテーブル, 非正規化, Flattened Table, 結合, 正規化, 大福帳

MENU

フラッタンテーブルとは

はじめに