Comments
Transcript
Sun Oracle Database Machineを使用したデータウェアハウス実装の
Oracleホワイト・ペーパー 2010年4月 Sun Oracle Database Machineを 使用したデータウェアハウス実装の ベスト・プラクティス Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス はじめに.............................................................................................................................. 1 データウェアハウス向けのデータ・モデル ....................................................................... 2 物理モデル - 論理モデルの実装 ........................................................................................ 3 ステージング・レイヤー ............................................................................................... 4 基盤レイヤー – 第3正規形 ......................................................................................... 10 アクセス・レイヤー - スター・スキーマ .................................................................. 15 まとめ ............................................................................................................................... 18 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス はじめに エンタープライズ・データウェアハウス（EDW）の価値を認識する企業が増えています。真のEDW は、ビジネスに対する360度の視野と強力なプラットフォームを提供することで、予測分析からほぼ リアルタイムの戦略的かつ戦術的な意思決定支援までに至る、組織全体での幅広いビジネス・イン テリジェンス・タスクを支援します。EDWで適切なパフォーマンスを実現し、データの増加に合わ せたスケールアウトを実行するには、ハードウェア構成、物理データ・モデル、データ・ロード・ プロセスという3つの基本要素を正しく構築する必要があります。この3つの基盤を正しく設計して おくと、頻繁にシステムを調整しなくてもシームレスに拡張できるEDWを構築できます。 データウェアハウス・プラットフォームとしてSun Oracle Database Machineを利用すると、パ フォーマンスに優れ、バランスの取れたハードウェア構成を実現できます。このホワイト・ペーパー では、残りの2つの基盤要素であるデータ・モデリングとデータ・ローディングに焦点を合わせ、Sun Oracle Database Machineを使用したデータウェアハウス実装の例とベスト・プラクティスについて 説明します。 このホワイト・ペーパーは、次の4つの項に分かれています。 最初の項では、データベース・ウェアハウスで使用される2つの基本的なデータ・モデルについて説 明します。 残り3つの項では、Oracleデータベースでこれらのモデルを実装するための最善の方法について説明 するとともに、最適なデータ・ロード・パフォーマンスを実現するための詳細情報を提供します。 1 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス データウェアハウス向けのデータ・モデル データ・モデルは、ビジネス上の質問に回答するためにデータウェアハウスに必要とされる情報の 種類や、さまざまな情報要素間の論理的関係を定義するものです。論理モデルは、物理的なデータ ベースやシステム実行に使用されるハードウェア、またはエンドユーザーがデータ・アクセスに使 用するツールに依存することなく、シンプルでわかりやすいものにする必要があります。データウェ アハウスには、第3正規形とスター・スキーマ（もしくはディメンション・スキーマまたはスノーフ レーク・スキーマ）という2つの典型的なモデルがあります。 第3正規形（3NF）は従来型のリレーショナル・データベースのモデリング手法であり、正規化を通 じてデータの冗長性を最小化します。3NFスキーマはアプリケーションに依存しない中立的なスキー マ設計であり、通常は多数の表から構成されています。またデータの冗長性を発生させることなく 各トランザクションの詳細なレコードを保持し、データ要素間のすべての関係と属性に対する高度 なエンコードを実現します。より複雑な構造を確実にナビゲートするために、通常はユーザーがデー タをしっかりと把握している必要があります。 スター・スキーマは、ダイアグラムが中心から頂点に向かって放射状に広がる星形をしていること からそう呼ばれています。星の中心は1つ以上のファクト表から構成されていて、星の頂点はディメ ンション表から構成されています。 図1：スター・スキーマ - 複数のディメンション表に囲まれた1つ以上のファクト表 ファクト表は業務上の測定値が格納された大型の表であり、通常はディメンション表への外部キー が含まれています。ディメンション表は参照表とも呼ばれ、データウェアハウス内では比較的静的 なデータや記述的なデータが保存されています。スター・スキーマは、物理モデルに近似していま す。これは、ドリル・パス、階層、問合せプロファイルが、データではなくデータ・モデル自体に 組み込まれているためです。エンドユーザーにとってモデルのナビゲートが非常に簡単な理由の1つ は、少なくともここにあります。スノーフレーク・スキーマはシンプルなスター・スキーマをわず かに変形したものであり、ディメンション表がさらに正規化されて複数の表に分かれています。ス ノーフレークの特徴はディメンションにのみ影響を与え、ファクト表には影響しないため、概念上 はスター・スキーマと同等であるとみなされています。したがって、このホワイト・ペーパーでは スノーフレーク・スキーマを個別に取り上げることはしません。 2 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス 各スタイルのデータウェアハウスにとって最適なモデリング手法についてはさまざまな議論があり ますが、従来の3NFとスター・スキーマ（ディメンション・スキーマ）にはそれぞれの長所と短所が あります。データウェアハウスでは1つのモデルに依存するのではなく、各モデルの長所を包含する ためのさらなる取組みが必要となるでしょう。これは、オラクルがデータウェアハウスのリファレ ンス・アーキテクチャで採用している方法です1。さらにこれは、両方のモデルを組み合わせて使用 している大半のOracleユーザーにもあてはまります。もっとも大切なことは、具体的なビジネス・ニー ズに合わせてモデルを設計することです。 物理モデル - 論理モデルの実装 物理モデルの出発点となるのは、論理モデルです。物理モデルはできる限り論理モデルを反映した 構造にする必要がありますが、表や列の構造にいくらかの変更が必要となる場合があります。さら に物理モデルには、通常は論理モデルには含まれないステージング表やメンテナンス表が含まれて います。以下の図2に、オラクルのDWリファレンス・アーキテクチャに定義されており、多くのデー タウェアハウス環境で使用されている物理レイヤーのブルー・プリントを示します。このように明 確に定義されたレイヤーがない環境でも、データベースの各レイヤーの要素をある程度設定して、 規模や複雑さの増加に合わせて拡張できるようにしておく必要があります。 図2：データウェアハウスの物理レイヤー 1 http://www.oracle.com/technology/products/bi/db/11g/pdf/twp_bidw_enabling_pervasive_bi_through_practical_dw_refere nce_architecture.pdf 3 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス ステージング・レイヤー ステージング・レイヤーを使用すると、業務システムからデータウェアハウスへのデータの抽出、 変換、ロード（ETL）を迅速に実行することができ、ビジネス・ユーザーに影響を与えることはあり ません。複雑なデータ変換やデータ品質処理の大半は、このレイヤーで実行されます。ステージン グ・レイヤー内の表は通常、データウェアハウスの問合せ部分からは分離されています。ステージ ング・レイヤーを構築する際のもっとも簡単なアプローチは、ソースとなる業務システムに含まれ るスキーマと同一のスキーマを作成し、表の構造の一部を変更（レンジ・パーティション化など） して使用する方法です。また、ステージング・レイヤーを必要としない実装も可能です。この場合、 ソース・システムから抽出されたデータが基盤レイヤーに直接挿入される前に、データ変換処理が まとめて実行されます。 効率的なデータのロード データをステージング・レイヤーにロードする場合でも、基盤レイヤーに直接ロードする場合でも、 目標となるのはもっとも目的に合った方法でデータをウェアハウスにロードすることです。優れた ロード・パフォーマンスを実現するには、ロードするデータが現在どこにあり、どのような方法で データベースにロードするのかという点に注目するところから始める必要があります。たとえば、 シリアル・データベース・リンクや単一JDBC接続を使用して大量データを移動するべきではありま せん。大量データのメカニズムとしてもっとも一般的であり、かつ好ましいのは、フラット・ファ イルです。 ステージング領域 データウェアハウス・システムのステージング・レイヤーにロードされる前のフラット・ファイル が保管されている領域は、一般にステージング領域として知られています。全般的なロード速度を 左右するのは、（A）ステージング領域から生データを読み取る速度と（B）データを処理してデー タベースに挿入する速度です。読取りがロードのボトルネックとならないようにするため、できる だけ多くの物理ディスクに対して生データをステージングすることを強く推奨します。 Sun Oracle Database Machineを使用してデータをステージングするためのもっとも適切な場所は、 Exadataストレージ・セルに格納されたOracle Database File System（DBFS）内です。DBFSによってマ ウント可能なクラスタ・ファイル・システムが作成され、これを使用してデータベースに格納され たファイルにアクセスできます。DBFSは、Database Machine上の独立したデータベース内に作成する ことが推奨されています。こうすることで、データウェアハウスとは別にDBFSを管理および保守す ることが可能になります。またファイル・システムに対して生データファイルをやり取りする際、 システム・ページ・キャッシュにスラッシングが発生しないようにするため、direct_ioオプションを 使用してファイル・システムをマウントします。DBFSの設定方法について、詳しくは『Oracle® Database SecureFilesおよびラージ・オブジェクト開発者ガイド』を参照してください。 生データファイルの準備 データ・ロードを並列化するには、生データファイルをグラニュルと呼ばれるチャンクに論理分割 する必要があります。バランスの取れたパラレル処理を実現するため、グラニュルの数は通常パラ レル・サーバー・プロセス数よりもずっと大きい数にします。1つのパラレル・サーバー・プロセス が一度に処理を実行できるのは、1つのグラニュルに対してのみです。パラレル・サーバー・プロセ スがこのグラニュルの処理を終えると、次のグラニュルが割り当てられ、すべてのグラニュルが処 理されてデータ・ロードが完了するまで割当てが続けられます。 4 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス 1つのファイル内に複数のグラニュルを作成するには、システムが生データファイルの内部を確認し て各データ行の開始位置と終了位置を特定する必要があります。これは、各行が既知の文字（改行 やセミコロンなど）によって明確に区切られている場合に限って可能になります。 圧縮（.zip）ファイルなどのようにファイル内の探索や位置合わせが実行できない場合、ファイルを 複数のグラニュルに分割できないため、ファイル全体が1つのグラニュルとして処理されます。この 場合、ファイル全体に対して処理を実行できるパラレル・サーバー・プロセスは1つのみになります。 圧縮データファイルのロードを並列化するには、複数の圧縮データファイルを使用する必要があり ます。使用する圧縮データファイルの数で、ロードで使用される最大並列度が決まります。 1つの外部表を使用して複数のデータファイル（圧縮または非圧縮）をロードする場合、ファイル・ サイズをほぼ同等に揃え、そのサイズは数10MBにすることが推奨されています。サイズの異なる ファイルを使用する必要がある場合、サイズの大きいファイルから小さいファイルへと並べること が推奨されています。 デフォルトでは、フラット・ファイルがデータベースと同じキャラクタ・セットを使用していると みなされます。そうでない場合、外部表の定義にフラット・ファイルのキャラクタ・セットを指定 して、適切なキャラクタ・セット変換が実施されるようにする必要があります。 外部表 オラクルは、次のデータ・ロード・オプションを提供しています。 外部表またはSQL*Loader Oracle Data Pump（インポートおよびエクスポート） チェンジ・データ・キャプチャ・メカニズムとトリクル・フィード・メカニズム（Oracle GoldenGateなど） Oracle Database Gateways（オープン・システムおよびメインフレーム） Oracle Generic Connectivity（ODBCおよびJDBC） このうちのどの方法を使用すれば良いのでしょうか。それはソースと受信データの形式によって決 まります。前述のとおり、大量データのロードにもっともよく使用されるメカニズムはフラット・ ファイルであるため、本書ではフラット・ファイルを使用したデータ・ロードのみに焦点を合わせ て説明します。 ファイルからOracleデータベースにデータをロードする場合、SQL*Loaderと外部表という2つの選択 肢があります。オラクルが強く推奨する方法は、SQL*Loaderではなく外部表を使用してロードする 方法です。 SQL*Loaderとは異なり、外部表を使用するとデータベース内で透過的に並列化が実行できます。 5 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス 外部表にアクセスする際に任意のSQL構文やPL/SQL構文を使用することで、ステージング・ データを使用せずにファイル上で直接変換を適用することができます。SQL*Loaderを使用す る場合は、最初にデータをそのままの状態でデータベースにロードする必要があります。 個々のパラレル・ローダーは固有のトランザクションを持つ独立したデータベース・セッショ ンであるため、外部表を使用してロードを並列化すると、SQL*Loaderを使用した場合よりも 効率的な領域管理を実現できます。高度にパーティション化された表では、無駄な領域が多 くなる可能性があります。 外部表は標準のCREATE TABLE構文を使用して作成されますが、データベース外部のフラット・ファ イ ル に 関 す る 追 加 情 報 を 指 定 す る 必 要 が あ り ま す 。 次 の SQL コ マ ン ド を 実 行 す る と 、 'sales_data_for_january.dat'フラット・ファイルの外部表が作成されます。 外部表からデータをロードする際のもっとも一般的な方法は、CREATE TABLE AS SELECT（CTAS） 文を実行するか、または既存表に対してINSERT AS SELECT（IAS）文を実行する方法です。たとえ ば、次の簡単なSQL文を実行すると、フラット・ファイル内のすべての行がSalesファクト表のパー ティションp2に挿入されます。 6 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス パラレル・ダイレクト・パス・ロード ロード・パフォーマンス向上の秘訣は、可能な限りダイレクト・パス・ロードを使用することです。 ダイレクト・パス・ロードでは、外部表定義の記述に従って入力データが解析され、各入力フィー ルドのデータが該当するOracleデータ型に変換されてから、列の配列構造が構築されます。この列の 配列構造を使用してOracleデータ・ブロックがフォーマットされ、索引キーが作成されます。次に、 標準のSQL処理エンジンやデータベース・バッファ・キャッシュがバイパスされ、新しくフォーマッ トされたデータベース・ブロックがデータベースに直接書き込まれます。 CTASでは常にダイレクト・パス・ロードが使用されますが、IAS文では使用されません。IAS文でダ イレクト・パス・ロードを実行するには、コマンドにAPPENDヒントを追加する必要があります。 ダイレクト・パス・ロードはパラレルで実行することもできます。ダイレクト・パス・ロードの並 列度の設定は、CTAS文やIAS文にPARALLELヒントを追加するか、または外部表とデータをロード する表の両方にPARALLEL句を設定することにより実行できます。並列度を設定すると、CTASでは ダイレクト・パス・ロードが自動的にパラレル実行されますが、IASでは自動的にパラレル実行され ません。IASでダイレクト・パス・ロードをパラレル実行するには、セッションを変更してパラレル DMLを有効にする必要があります。 パーティション交換ロード データウェアハウスの大型の表やファクト表は、パーティション化することを強くお勧めします。 パーティション化の利点の1つとして、EXCHANGE PARTITIONコマンドを使用することにより、ビ ジネス・ユーザーへの影響を最小限に抑えてデータを迅速かつ簡単にロードできるという点があり ます。EXCHANGE PARTITIONコマンドを使用すると、パーティション化されていない表のデータ をパーティション化された表の特定のパーティションに置き換えることができます。このコマンド は、データを物理的に移動するかわりに、データ・ディクショナリを更新してパーティションから 表への（その逆も含む）ポインタを交換します。データの物理的な移動が発生せず、交換時にREDO やUNDOが生成されないことから、処理が1秒未満で完了し、INSERTなどの従来のデータ移動方法よ りもパフォーマンスへの影響がはるかに少なくなります。 ここでは、Salesという名前の大型の表があり、それに日次レンジ・パーティションが設定されてい ると仮定します。各営業日の最後に、オンライン販売システムからウェアハウスのSales表にデータ がロードされます。図3に示した5つの簡単な手順を実行するだけで、データウェアハウスのビジネ ス・ユーザーへの影響を最小限に抑えつつ、最適な速度で日次データを正しいパーティションにロー ドできます。 7 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス 図3：パーティション交換ロード パーティション交換ロードの手順 1. オンライン・システムからフラット・ファイル・データをロードするための外部表を作成し ます。 2. CTAS文を使用して、Sales表と同じ列構造を持つ、tmp_salesというパーティション化されてい ない表を作成します。 3. Sales表にあるすべての索引をtmp_sales表に作成します。 4. EXCHANGE PARTITIONコマンドを実行します。 1. 増分統計を使用して、新たに交換したパーティションのオプティマイザ統計情報を収集しま す。詳しくは、本シリーズ2番目のホワイト・ペーパー『Best Practices for Workload management in a Data Warehouse on the Sun Oracle Database Machine』を参照してください。 8 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス 上記の4番目のステップでEXCHANGE PARTITIONコマンドを実行することにより、指定されたパー ティションとtmp_sales表の定義が置き換わり、パーティション化された表の正しい場所にデータが 即座に移ります。さらに、2つの句を任意で追加することによって索引定義が置き換わります。デー タがパーティション内に実際に存在するかどうかの確認が行われないため、交換はすばやく完了し ます。ここでは、データ抽出時にデータの整合性が検証されていることを前提にしています。デー タの整合性に不安がある場合はWITHOUT VALIDATION句を使用しないでください。そうすること で、データベースによってデータの妥当性がチェックされます。 データ圧縮 ロード・フェーズでもう1つ決定する必要がある重要なことは、データ圧縮の要否です。言うまでも なく、表圧縮機能を使用することでディスクとメモリの使用率が低下するため、通常は読取り専用 処理のパフォーマンスが向上します。また、表圧縮機能により、ディスクからデータを取得する際 のラウンドトリップ数が最小限で済むため、問合せの実行が高速化されます。データ圧縮によって データ・ロード速度のパフォーマンスは低下しますが、通常は総合的なパフォーマンス向上が、圧 縮によるデメリットを上回ります。 Sun Oracle Database Machineでは、基本圧縮、OLTP圧縮（Advanced Compressionオプションに含まれ るコンポーネント）、Exadata Hybrid Columnar Compression（EHCC）という3種類の圧縮機能が提供 されています。 オラクルの標準圧縮では、データベース・ブロック内の重複値を排除することでデータが圧縮され ます。標準圧縮はダイレクト・パス処理（前述のCTASまたはIAS）でのみ使用できます。何らかの 通常DML処理（UPDATEなど）を使用してデータが変更されると、データベース・ブロック内のデー タが解凍されて変更され、圧縮されてない状態でディスクに書き込まれます。 OLTP圧縮の場合も標準圧縮と同様に、データベース・ブロック内の重複値を排除することでデータ が圧縮されます。しかし、OLTP圧縮では標準圧縮と違って、INSERTやUPDATEなどの標準DMLを 含むあらゆるデータ処理がデータを圧縮したままで実行されます。OLTP表圧縮機能について、詳し くは『Oracle® Database管理者ガイド11g』を参照してください。 Exadata Hybrid Columnar Compression（EHCC）では、異なる圧縮テクニックを使用して圧縮が実行さ れます。Exadata Hybrid Columnar Compressionでは、圧縮単位と呼ばれる論理的な構成体を使用して 一連の行が格納されます。データのロード時に一連の行が列表現に置き換えられ、圧縮が実行され ます。行セットの列データは、圧縮されてから圧縮単位に格納されます。EHCCを使用した表に対し て標準DMLが発行されると、必要なデータが解凍されて変更が実施され、ブロック・レベルの圧縮 アルゴリズムを使用してディスクに書き込まれます。データセットに対して標準DMLを使用した変 更が頻繁に実行される場合、EHCCではなくOLTP圧縮を使用することを推奨します。 EHCCでは、問合せパフォーマンスと圧縮率のどちらを重視するかによって異なる圧縮レベルが提供 されています。問合せ向けに最適化されたEHCCでは、パフォーマンスへの影響がほとんどない優れ た圧縮を達成するため、限られた圧縮アルゴリズムが適用されます。その一方で、アーカイブ向け の圧縮が目指すのはディスク上での最大限の圧縮であり、問合せパフォーマンスへの潜在的影響に ついては考慮しません。詳しくは、Oracle Exadataドキュメントを参照してください。 9 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス 圧縮を使用する場合、可能な限り最大の圧縮率を達成するため、ロード前のデータ・ソートを検討 してください。受信データをソートする際のもっとも簡単な方法は、CTAS文かIAS文のいずれかで ORDER BY句を使用してロードする方法です。ORDER BYは、null以外（理想的には数値以外）で多 数の固有値（1,000～10,000）を持つ列に対して実行することを推奨します。 基盤レイヤー – 第3正規形 データは、ステージング・レイヤーから、別のETLプロセスを介して基盤レイヤーまたは統合レイヤー に移行されます。データの形成はこのレイヤーで開始され、ディメンションまたはパフォーマンス・ レイヤーに変換される前にここで利用可能となるため、エンドユーザー・アプリケーションが（特 に時間依存型の場合）このレイヤーのデータにアクセスすることも珍しくありません。伝統的に、 このレイヤーは第3正規形（3NF）で実装されます。 3NFの最適化 Oracleデータベースで3NFスキーマを最適化するには、Power（パワー）、Partitioning（パーティショ ン化）、Parallel Execution（パラレル実行）という3つのPが必要になります。パワーとは、バランス の取れたハードウェア構成を意味しており、Sun Oracle Database Machineがこれに当てはまります。 また大型の表は、コンポジット・パーティション化（レンジ・ハッシュやリスト・ハッシュ）を使 用してパーティション化することを推奨します。その理由としては、次の3つが挙げられます。 1. テラバイト単位のデータを簡単に管理可能 2. 必要なデータに迅速にアクセス可能 3. 効率性とパフォーマンスに優れた表結合が可能 また、パラレル実行により、データベース・タスクが小さい作業単位に並列化/分割されるため、複 数のプロセスを同時に処理できるようになります。並列処理を使用することで、1テラバイトのデー タをスキャンおよび処理する時間が、数時間または数日から数分以内へと短縮されます。パラレル 実行について、詳しくはシステム管理の項で後述します。 パーティション化 パーティション化によって、表、索引、または索引構成表をさらに細かく分割できます。分割され たそれぞれのデータベース・オブジェクトがパーティションと呼ばれます。各パーティションには 固有の名前があり、独自のストレージ特性を持つ場合もあります。データベース管理者の視点から 見ると、パーティション化されたオブジェクトには複数の区分があり、まとめて管理することも、 個別に管理することもできます。 これによって、管理者はパーティション化されたオブジェクトをかなり柔軟に管理できるようにな ります。しかしアプリケーションの視点から見ると、パーティション化された表はパーティション 化されていない表と変わらないため、SQL DMLコマンドを使用してパーティション化した表にアク セスする場合でも一切変更の必要がありません。パーティション化によって管理性、可用性、およ びパフォーマンスが向上するため、さまざまなアプリケーションに大きな利点がもたらされます。 10 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス パーティション化による管理性の向上 レンジ・パーティション化は、大量のデータの管理性と可用性を向上します。 たとえば、2年間の売上データ（100TB）が表に保存されているとします。毎日終業時に新しいバッ チ・データを表にロードして、もっとも古い日付のデータを削除する必要があります。売上表が1日 ごとにレンジ・パーティション化されている場合は、先ほど説明したパーティション交換ロードを 使用して、新しいデータをロードできます。この処理は1秒未満で済むため、エンドユーザーの問合 せにはほとんど影響がありません。また、もっとも古い日付のデータの削除は、次のコマンドを発 行するだけで実行できます。 パーティション化によるデータ・アクセスの簡素化 さらに、レンジ・パーティション化すれば、問合せに対する回答に必要なデータのみをスキャンで きます。たとえば、ビジネス・ユーザーは1週間の総売上を確認するため、おもに週次で売上データ にアクセスするとします。その場合、この表を1日ごとにレンジ・パーティション化すれば、もっと も効率的なデータ・アクセスを実現できます。これは、表全体でなく7つのパーティションのみをス キャンするだけで、ビジネス・ユーザーの問合せに回答できるためです。関係のないパーティショ ンをスキャンしない機能のことを、パーティション・プルーニングと呼びます。 図4：パーティション・プルーニング：関連するパーティションのみにアクセス 11 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス パーティション化による結合パフォーマンスの向上 ハッシュを使用したサブパーティション化は、おもにパフォーマンス上の理由から使用されます。 オラクルでは、線形ハッシュ・アルゴリズムを使用してサブパーティションを作成します。デー タを複数のハッシュ・パーティションに対して均等に分散するため、ハッシュ・パーティション 数を2の累乗値（2、4、8など）に設定することを強くお勧めします。表のハッシュ・パーティショ ン数決定時の目安として、CPU数を2倍した値をもっとも近い2の累乗値に切り上げます。12基の CPUがある場合は、適切なハッシュ・パーティション数は32となります。クラスタ・システムにも 同様のルールが適用されます。3つのノードにそれぞれ4基のCPUが搭載されている場合、適切な ハッシュ・パーティション数はやはり32となります。ただし、各ハッシュ・パーティションのサ イズは16MB以上にする必要があります。サイズがこれより小さいと、パラレル問合せで効率的な スキャン速度を実現できなくなります。CPU数を使用するとハッシュ・パーティションのサイズが 小さくなりすぎる場合は、同一環境内のOracle RACノード数をもっとも近い2の累乗値に切り上げ た数を使用してください。 ハッシュ・パーティション化でのパフォーマンス上のおもなメリットの1つは、パーティションワイ ズ結合です。パーティションワイズ結合は、結合がパラレル実行される場合にパラレル実行サーバー 間で交換されるデータの量を最小化することで、問合せの応答時間を短縮します。これにより、応 答時間が大幅に短縮されるだけでなく、CPUとメモリのリソース使用率が向上します。クラスタ化 されたデータウェアハウスでは、インターコネクト（IPC）経由のデータ・トラフィックを制限する ことで、応答時間が大幅に短縮されます。これは、大規模な結合処理で優れたスケーラビリティを 実現する際のカギになります。パーティションワイズ結合は、結合する表のパーティション化スキー ムに応じて、全体または一部に対して実行できます。 フル・パーティションワイズ結合では、2つの大型の表の結合が複数の小さい結合に分割されます。 それぞれの小さい結合で、結合対象表ごとに1つずつ選ばれたパーティションに対する結合が実行さ れます。オプティマイザがフル・パーティションワイズ結合を選択できるようにするには、両方の 表がその結合キーに対して同一レベルでパーティション化されている必要があります。つまり、両 表の同じ列に対して、同じパーティション化手法を使用して、パーティション化を実行する必要が あります。フル・パーティションワイズ結合のパラレル実行はシリアル実行に似ていますが、一度 に1組のパーティションが結合される代わりに、複数のパラレル問合せサーバーによって複数組の パーティションがパラレルで結合されます。パラレル結合されるパーティションの数は、並列度 （DOP）によって決まります。 12 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス 図5：フル・パーティションワイズ結合 図5は、Sales表とCustomer表間のフル・パーティションワイズ結合をパラレル実行した場合を示して います。両表には同じ並列度が設定され、同じ数のパーティション数が含まれています。これらの 表は日付フィールドでレンジ・パーティション化され、cust_idフィールドでハッシュによりサブパー ティション化されています。図に示したように、データベースからパーティションが1組ずつ読み込 まれ、直接結合されます。データの再分散が不要のため、特にノード間のIPC通信を最小限に抑える ことができます。図6は、この結合の実行計画を示しています。 図6：フル・パーティションワイズ結合の実行計画 13 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス パーティションワイズ結合のパラレル実行時に最適なパフォーマンスを実現するには、各表のパー ティション数を結合時の並列度より大きくする必要があります。パラレル・サーバー数よりパーティ ション数が多い場合、各パラレル・サーバーで1組のパーティションが結合され、結合が完了すると もう1組のパーティションの結合が要求されます。このプロセスは、パーティションの全組が処理さ れるまで反復されます。この方法では、動的に負荷が分散されます（パーティション数が128で並列 度が32の場合など）。 それでは、結合対象表のうち1つのみがパーティション化されている場合はどうなるのでしょうか。 この場合は、オプティマイザによりパーシャル・パーティションワイズ結合が選択されます。フル・ パーティションワイズ結合とは異なり、パーシャル・パーティションワイズ結合は、1つの表のみが 結合キーでパーティション化されている場合に適用されます。したがって、パーシャル・パーティ ションワイズ結合は、フル・パーティションワイズ結合よりもよく使用されます。パーシャル・パー ティションワイズ結合の実行では、パーティション化された表のパーティション化戦略に基づいて もう一方の表が動的に再パーティション化されます。もう一方の表が再パーティション化されると、 その後の実行はフル・パーティションワイズ結合の場合と同様になります。この再分散処理には、 パラレル実行サーバー間での行の交換も含まれます。この処理により、Oracle RAC環境でインター コネクト・トラフィックが発生します。これは、ノード境界を越えてデータを再パーティション化 する必要があるためです。 図7：パーシャル・パーティションワイズ結合 図7は、パーシャル・パーティションワイズ結合を示しています。ここでは 図5と同じ例が使用され ていますが、Customer表はパーティション化されていません。結合処理の実行前に、Customer表の行 が結合キーに対して動的に再分散されます。 14 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス アクセス・レイヤー - スター・スキーマ アクセス・レイヤーでは、ユーザーやアプリケーションの大半が理解できる形式でデータが表現さ れます。通常スター・スキーマが確認できるのは、このレイヤーです。 図8：スター・スキーマ - 複数のディメンション表に囲まれた1つ以上のファクト表 アクセス・レイヤーでの一般的な問合せは、ファクト表と一部のディメンション表に対する結合で あり、通常はスター・クエリーと呼ばれます。スター・クエリーでは、主キーと外部キーの結合を 使用して、各ディメンション表がファクト表に結合されます。通常、ディメンション表同士が結合 されることはありません。図8のスター・スキーマに対しては、"2008年5月のボストンでの傘の販売 総数はいくつか"というビジネス上の質問を投げかけることができます。この質問に対するSQL問合 せは、図9のようになります。 図9：すべてのWHERE句条件がディメンション表に対して指定された、一般的なスター・クエリー 15 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス ご覧のように、すべてのWHERE句条件がディメンション表に対して指定されており、外部キーと主 キーの関係を利用してファクト表（Sales表）が各ディメンション表に結合されています。それでは、 このような問合せを最適化するにはどうすれば良いのでしょうか。 スター・クエリーの最適化 スター・クエリーのチューニングは非常に簡単です。もっとも重要な条件は、次の2つです。 ファクト表の各外部キー列にビットマップ索引を作成します。 STAR_TRANSFORMATION_ENABLED初期化パラメータをtrueに設定します。これにより、 スター・クエリーのオプティマイザ機能が有効になります。デフォルトでは、下位互換性を 維持するためこの機能は無効に設定されています。 この2つの条件が満たされている環境でスター・クエリーを実行すると、スター変換と呼ばれる強力 な最適化手法を使用して、SQLの書換えや変換が実行されます。スター変換では、問合せが2段階で 実行されます。第1段階ではファクト表から必要な行（行セット）が取得され、第2段階でこの行セッ トがディメンション表に結合されます。ファクト表からの行の取得は、すべての外部キー列に作成 されたビットマップ索引間のビットマップ結合を使用して実行されます。オプティマイザが必要に 応じて自動的にスター変換を選択するため、エンドユーザーがスター変換の詳細を把握する必要は ありません。 しかし、図 9のスター・クエリーは、スター変換によってどの程度の影響を受け、どのように書き 換えられるのでしょうか。前述のとおり、問合せは2段階で処理されます。第1段階では、問合せ の変換や書換えが実行され、ファクト表への各結合が副問合せとして書き換えられます。書き換 えられた問合せがどのようになるかを 図 10に示します。このようにして問合せを書き換えること により、ビットマップ索引の長所を活用できるようになります。ビットマップ索引では、データ ベース内でセット単位の処理が実行されるため、AND、OR、MINUS、COUNTなどのセット処理 を実際的な方法で実行できます。そのため、time_idに対するビットマップ索引を使用して、2008 年5月の売上に該当するファクト表の行セットを特定できます。ビットマップでは、行セットは1 や0の文字列で表示されます。同様のビットマップが、傘の売上に該当するファクト表の行に対し て取得され、もう1つのビットマップがボストンでの売上に対して取得されます。この時点では3 つのビットマップが存在しており、各ビットマップが個々のディメンション制約を満たすファク ト表の行セットを表しています。その後、この3つのビットマップがビットマップAND処理を使用 して結合されます。この新たに作成された最終ビットマップを使用して、問合せの評価に必要な 行がファクト表から抽出されます。 16 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス 図10：スター変換は2段階のプロセス 第2段階では、ファクト表から取得した行がディメンション表に対して結合されます。通常、ディメ ンション表への再結合はハッシュ結合を使用して実行されますが、Oracle Optimizerはディメンショ ン表のサイズに応じてもっとも効率的な結合方法を選択します。 図11に、スター変換が実行されたスター・クエリーに対する一般的な実行計画を示します。この実 行計画は、想像していたものとは少し違っているかもしれません。Sales表から行が取得された後、 Customer表への再結合が実行されていないことにお気づきでしょう。選択リストをよく見ると、実際 Customer表からは何も選択されていません。そのため、ディメンション表への再結合は必要ないとオ プティマイザが判断しています。さらに一部の問合せでは、スター変換が実行されても、ファクト 表に対するすべてのビットマップ索引が使用されているわけではないことにお気づきでしょう。オ プティマイザは、ファクト表から必要な行を取得するために必要なビットマップ索引の数を決定し ます。追加のビットマップ索引を使用しても選択性が改善されない場合、オプティマイザはこの索 引を使用しません。除外されたビットマップに該当するディメンション表が実行計画に表示される のは、第2段階や再結合段階のみとなります。 17 Sun Oracle Database Machineを使用したデータウェアハウス実装のベスト・プラクティス 図11：一般的なスター・クエリーの実行計画 まとめ データウェアハウスでの最適なパフォーマンスを実現し、データの増加に合わせたスケールアウト を実行するには、次の3つの基本要素を正しく設定する必要があります。 ハードウェア構成。バランスのとれた構成とし、システムのピーク・ロードに対応できるI/O スループットを実現する必要があります。 データ・モデル。3NFの場合は常にパーティションワイズ結合を実行し、スター・スキーマ の場合はスター変換を使用することを推奨します。 データのロード・プロセス。できる限り高速なロードを実現し、ビジネス・ユーザーに影響 を及ぼさないようにすることが重要です。 Sun Oracle Database Machineを選択すれば、上記を満たすハードウェア構成が得られます。また、こ のホワイト・ペーパーに概説したデータウェアハウス配置のベスト・プラクティスに従うことで、 頻繁にシステムをチューニングしなくてもEDWのシームレスなスケールアウトが実現できます。 18 Sun Oracle Database Machineを 使用したデータウェアハウス実装の ベスト・プラクティス 2010年4月 著者：Maria Colgan Copyright © 2010, Oracle and/or its affiliates.All rights reserved.本文書は情報提供のみを目的として提供されており、ここに記 載される内容は予告なく変更されることがあります。本文書は一切間違いがないことを保証するものではなく、さらに、口述に Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. 海外からのお問い合わせ窓口： 電話：+1.650.506.7000 ファクシミリ：+1.650.506.7200 www.oracle.com よる明示または法律による黙示を問わず、特定の目的に対する商品性もしくは適合性についての黙示的な保証を含み、いかなる 他の保証や条件も提供するものではありません。オラクル社は本文書に関するいかなる法的責任も明確に否認し、本文書によっ て直接的または間接的に確立される契約義務はないものとします。本文書はオラクル社の書面による許可を前もって得ることな く、いかなる目的のためにも、電子または印刷を含むいかなる形式や手段によっても再作成または送信することはできません。 Oracleは米国Oracle Corporationおよびその子会社、関連会社の登録商標です。その他の名称はそれぞれの会社の商標です。