マテリアライズド・ビューは、大規模データベースでの問い合わせをスピードアップするためにデータ・ウェアハウスで使用されます。
大規模データベースの問い合わせでは、表間の結合やSUMなどの集計がしばしば影響してきます。これらの操作は時間とCPUの処理能力の点でコストが非常に高いものになります。作成されるマテリアライズド・ビューの種類により、クエリー・リライトによるマテリアライズド・ビューのリフレッシュ方法と使用方法が決定されます。
マテリアライズド・ビューはさまざまな方法で使用することができ、ほぼ同一の文を使用して多くのことを実行できます。たとえば、マテリアライズド・ビューはこれまでCREATE
SNAPSHOT文で行われていたデータのレプリケートに使用できます。現在、CREATE
MATERIALIZED VIEWはCREATE SNAPSHOTのシノニムです。
マテリアライズド・ビューは、負荷の大きいデータベースの結合と集計操作を事前に計算し、データベースの表に結果を格納しておくことで問い合わせのパフォーマンスを向上させます。
クエリー・リライトを使用するときは、問い合わせの最大数に見合ったマテリアライズド・ビューを作成してください。たとえば、ディテール表またはファクト表を参照する20個程度の問い合わせがあるとき、5、6個の適切なマテリアライズド・ビューで十分である場合などが考えられます。マテリアライズド・ビュー定義には、任意の数の集計（SUM、COUNT（x
）、COUNT（*）、COUNT（DISTINCT x ）、AVG、VARIANCE、STDDEV、MIN、MAX）や結合を含めることができます。どのようなマテリアライズド・ビューを作成すればよいかわからない場合のために、DBMS_OLAPパッケージによって、クエリー・リライトのマテリアライズド・ビューの設計と評価に役立つ一連のアドバイザ機能が提供されます。
Oracle9i では、マテリアライズド・ビューの作成前後にEXPLAIN_MV文を使用できます。このSQL文が与えられているので、このマテリアライズド・ビューを作成した場合に実行できることとできないことを測定できます。実行できないことに対しては、その理由と修正方法が指示されます。つまり、マテリアライズド・ビューの作成前に、設計を変更することや、目的に合っているかどうかを確認することが可能です。または、既存のマテリアライズド・ビュー名を指定すると、実行できることとできないこと、またその理由を指示されます。どちらの方法でも、結果は表MV_CAPABILITIES_TABLEに格納されます。
クエリー・リライトでマテリアライズド・ビューを使用する場合は、マテリアライズド・ビューをそのファクト表またはディテール表と同じデータベースに格納する必要があります。マテリアライズド・ビューはパーティション化可能で、パーティション表にマテリアライズド・ビューを定義し、マテリアライズド・ビューに1つ以上の索引を定義できます。
マテリアライズド・ビューはいくつかの点で索引と似ています。両方とも記憶領域を消費する、マスター表のデータが変更された場合とクエリー・リライトに使用された場合はリフレッシュする必要がある、SQL実行のパフォーマンスを改善する、存在がSQLアプリケーションとユーザーに透過的である、などの点です。索引と異なり、マテリアライズド・ビューは直接SELECT文によってアクセスできます。必要とされるリフレッシュの種類によっては、INSERT、UPDATE、DELETE文でも直接アクセスできます。
| 5.3.4 英語設定でのOracle Top Sessionsの利用 |
|
本演習ではOracle Top Sessionsというアプリケーションを使用しますが、トライアル版のOracle
Top Sessionsでは完全には日本語対応されていません（製品版では対応しています）。ここではトラブルを避けるために、OSの地域設定とレジストリを変更して、英語表示に切り替えて使用します。
|
1.
|
Windows 2000のスタートボタンをクリックして、[設定]→[コントロールパネル]→[地域のオプション]メニューを選択します。[地域のオプション]ダイアログが表示されるので、[ロケール（国または地域）]のドロップ・ダウン・ボックスで[英語（U.S.）]を選択し、[OK]ボタンをクリックします（図5.3.1を参照）。
|
図5.3.1
画像をクリックすると拡大図をご覧いただけます
|
|
2.
|
再びスタートボタンをクリックして、[ファイル名を指定して実行]メニューを選択し、REGEDT32と入力して[OK]ボタンをクリックします。
|
|
3.
|
レジストリエディタが起動します。左側のツリーのフォルダを順に開き、以下に示すレジストリキーの現在の値を表示させます。
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\NLS_LANG
|
|
4.
|
このレジストリキーの値が[AMERICAN_AMERICA.US7ASCII]でなければ（日本語の場合たいてい、JAPANESE_JAPAN.JA16SJISになっています）、値を変更する必要があります。まず現在の値を記録してください。これは元の設定に戻すために必要です。
|
|
5.
|
このレジストリキーの値をダブルクリックし、[文字列]フィールドの値をAMERICAN_AMERICA.US7ASCIIに変更して[OK]ボタンをクリックします（図5.3.2を参照）。
注意：
レジストリの変更を誤ると、OSに深刻な影響を与えることがあります。細心の注意を払って操作してください。
|
図5.3.2
画像をクリックすると拡大図をご覧いただけます
|
|
6.
|
レジストリエディタを終了します。
|
|
7.
|
OSを再起動します。
|
この演習中、他のプログラムも英語メニュー、メッセージで表示される場合がありますが異常ではありません。 |
初めに、この節の演習で使用するサンプル・スクリプトの圧縮ファイルmv.zipをダウンロードし、解凍して実行できる状態にしておいてください。
パフォーマンスをさらに向上させるため、ビットマップ索引をマテリアライズド・ビューで使用します。このデータ・ウェアハウスに対して実行される通常の問い合わせの知識に基づいて、役に立つマテリアライズド・ビューを作成できます。
SH.TIMES表、SH.SALES表に、処理時間を節約するために事前に要約されたデータがロードされています。これをサマリーとして使用するため、これらの集計表にCAL_MONTH_SALES_MV、FWEEK_PSCAT_SALES_MVという名前でマテリアライズド・ビューが作成されています。
sh_cremv.sqlというスクリプトがSales Historyスキーマのインストール中に実行され、マテリアライズド・ビューとビットマップ索引が作成されました。スクリプトの詳細は、5.2.9「データ・ウェアハウス・スキーマの調査」のマテリアライズド・ビューの項目を参照してください。
これらのマテリアライズド・ビューは、Oracle Enterprise Managerを使っても作成することができます。ここで、Oracle
Enterprise Managerを使用してマテリアライズド・ビューの属性を検査します。
|
1.
|
スタートボタンをクリックして、[プログラム]→[Oracle - OraHome90]→[Enterprise Manager Console]メニューを選択します。
[OK]ボタンをクリックして、Oracle Enterprise Managerをスタンドアロンで起動します（なお、コマンドプロンプトからoemapp dbastudioと入力しても起動します）。ツリー・リストの[ORCL.WORLD]のとなりにある[+]をクリックしてログインします（図5.3.3を参照）。必要なら[Username]フィールドにsystem、[Password]フィールドにmanagerと入力して、[OK]ボタンをクリックします。
|
図5.3.3
画像をクリックすると拡大図をご覧いただけます
|
|
2.
|
[Schema]→[Materialized View（Snapshot）]→[SH]スキーマの順に開いて、[CAL_MONTH_SALES_MV]をクリックします。
|
|
3.
|
作成されたCAL_MONTH_SALES_MVとFWEEK_PSCAT_SALES_MVマテリアライズド・ビューの属性を確認し必要に応じて調整できます。適切なタブをクリックして、[Refresh]、[Storage]、[Index Storage]、[Options]、[Master Info]の表属性をそれぞれ検査してください（図5.3.4を参照）。
|
図5.3.4
画像をクリックすると拡大図をご覧いただけます
|
|
4.
|
Oracle Enterprise Managerを終了します。
|
| 5.3.6 マテリアライズド・ビュー使用時/非使用時の問い合わせの実行 |
|
マテリアライズド・ビューによるパフォーマンスの改善方法を示す、2つの問い合わせをここで実行します。
問い合わせを行うスクリプトには、次の2つの初期化パラメータが設定されています。
query_rewrite_enabled = true
query_rewrite_integrity = trusted
サマリーのパフォーマンス利点を調べるため、これらのパラメータを動的に設定して例の問い合わせに対する実行時間の違いを示します。
問い合わせ1 ― 1998年12月20日で終わる週の、販売チャンネル、製品サブカテゴリ、週単位の総売上高
|
1.
|
問い合わせの実行前に、スタートボタンを押して、[プログラム]→[Oracle - OraHome90]→[Enterprise
Management Packs]→[Diagnostic]→[TopSessions]メニューを選択してOracle
Top Sessionsを開きます。
|
|
2.
|
Database Logon]ウィンドウで、[Username]フィールドにsystem、[Password]フィールドにmanager、[Service]フィールドにorcl.worldと入力して、[OK]ボタンをクリックします（図5.3.5を参照）。ここで実行計画を表示して、マテリアライズド・ビューが使用されているかどうかを確認できます。
|
図5.3.5
画像をクリックすると拡大図をご覧いただけます
|
|
3.
|
データ・ウェアハウス・データベースに接続されているSQL*Plusで、@query_without_mv1.sqlと入力して次のスクリプトを実行します（図5.3.6を参照）。
Connect sh/sh@orcl.world
set timing on
ALTER SESSION SET query_rewrite_enabled=FALSE;
ALTER SESSION SET query_rewrite_integrity=TRUSTED;
SELECT /*+ NOREWRITE */ t.week_ending_day
, c.channel_desc
, p.prod_subcategory
, sum(s.amount_sold) AS sum_dollars
FROM sales s
, times t
, products p
, channels c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.channel_id = c.channel_id
AND t.week_ending_day='12-Dec-1998'
GROUP BY t.week_ending_day
, c.channel_desc
, p.prod_subcategory;
この問い合わせは多くの行を返します。
|
図5.3.6
画像をクリックすると拡大図をご覧いただけます |
|
4.
|
問い合わせの実行中にOracle Top Sessionsに切り替えて、この問い合わせの実行計画を確認します。リフレッシュ間隔が長いとアクティブな[SH]セッションが確認できないかもしれません。その場合、メニューから[Collection]→[Refresh Rate]を選択し、1秒単位でリフレッシュするよう設定した上で、上記の問い合わせを再実行してください（図5.3.7を参照）。
|
図5.3.7
Oracle Top Sessionsのリフレッシュ間隔を
変更する
|
|
5.
|
アクティブな[SH]セッションをダブルクリックします。環境によって、Oracle Top SessionsのSHセッションをダブルクリックするタイミングが難しい場合もあります（図5.3.8を参照）。
|
図5.3.8
画像をクリックすると拡大図をご覧いただけます
|
|
6.
|
[Session Details]ウィンドウが表示されます。[Session Details]タブの左下にある[Current SQL]フィールドの右にある[→]ボタンをクリックします。表示されるメニューから[Explain Plan]を選択します。（図5.3.9を参照）。
|
図5.3.9
画像をクリックすると拡大図をご覧いただけます
|
|
7.
|
query_without_mv1.sqlの実行計画が表示されます。予測どおり、実表にアクセスしていることに注意してください（図5.3.10を参照）。
|
図5.3.10
画像をクリックすると拡大図をご覧いただけます
|
|
8.
|
今回は、クエリー・リライトのセッションを有効にして同じ問い合わせを再実行します。
データ・ウェアハウス・データベースに接続されているSQL*Plusで、@query_with_mv1.sqlと入力して、次のスクリプトを実行します（図5.3.11を参照）。
CONNECT sh/sh@orcl.world;
alter session set query_rewrite_integrity = TRUSTED;
alter session set query_rewrite_enabled = TRUE;
set timing on
SELECT t.week_ending_day
, c.channel_desc
, p.prod_subcategory
, sum(s.amount_sold) AS sum_dollars
FROM sales s
, times t
, products p
, channels c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.channel_id = c.channel_id
AND t.week_ending_day='20-Dec-1998'
GROUP BY t.week_ending_day
, c.channel_desc
, p.prod_subcategory;
|
図5.3.11
画像をクリックすると拡大図をご覧いただけます
|
|
この問い合わせは多くの行を返します。この問い合わせは、前の例よりも迅速に行を返します。
|
|
9.
|
問い合わせが迅速なのでOracle Top Sessionsで実行計画を表示するのは難しくなります。したがって、SQL*Plusで実行計画を表示します。Sales Historyデータ・ウェアハウスのデータベースに接続されているSQL*Plusセッションで、@query_with_mv_explain1.sqlと入力して、次のスクリプトを実行します（図5.3.12を参照）。
CONNECT sh/sh@orcl.world;
alter session set query_rewrite_integrity = trusted;
alter session set query_rewrite_enabled = true;
set timing on
explain plan for
SELECT t.week_ending_day
, c.channel_desc
, p.prod_subcategory
, sum(s.amount_sold) AS sum_dollars
FROM sales s
, times t
, products p
, channels c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.channel_id = c.channel_id
AND t.week_ending_day='20-Dec-1998'
GROUP BY t.week_ending_day
, c.channel_desc
, p.prod_subcategory;
@c:\oracle\ora90\rdbms\admin\utlxplp
|
図5.3.12
画像をクリックすると拡大図をご覧いただけます
|
問い合わせ2 ― 1998年12月20日で終わる週の、販売チャンネル、製品サブカテゴリ、週単位の総売上高
2番目の問い合わせはマテリアライズド・ビューFWEEK_PSCAT_SALES_MVを使用します。この問い合わせは、非常にシンプルなクエリー・リライトなしの例を使用しています。
|
1.
|
データ・ウェアハウス・データベースに接続されているSQL*Plusセッションで、@query_without_mv2_join.sqlと入力して、次のスクリプトを実行します（図5.3.13を参照）。
CONNECT sh/sh@orcl.world;
alter session set query_rewrite_integrity = trusted;
alter session set query_rewrite_enabled = false;
set timing on
SELECT /*+ NOREWRITE */ t.week_ending_day
, c.channel_desc
, p.prod_subcategory
, sum(s.amount_sold) AS sum_dollars
FROM sales s
, times t
, products p
, channels c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.channel_id = c.channel_id
AND t.week_ending_day='20-Dec-1998'
GROUP BY t.week_ending_day
, c.channel_desc
, p.prod_subcategory;
|
図5.3.13
画像をクリックすると拡大図をご覧いただけます
|
|
2.
|
Oracle Top Sessionsを使用して、query_without_mv1.sqlの場合と同様に、この問い合わせの実行計画を確認します（図5.3.14を参照）。
経過時間に注目してください。予測どおり、実表に再びアクセスしています。
|
図5.3.14
画像をクリックすると拡大図をご覧いただけます
|
|
3.
|
今回は、クエリー・リライトのセッションを有効にして同じ問い合わせを再実行します。
データ・ウェアハウス・データベースに接続されているSQL*Plusセッションで、@query_with_mv2_join.sqlと入力して次のスクリプトを実行します（図5.3.15を参照）。
CONNECT sh/sh@orcl.world;
alter session set query_rewrite_integrity = trusted;
alter session set query_rewrite_enabled = true;
set timing on
REM query delta join; join in the query but not in the mv
SELECT t.week_ending_day
, c.channel_desc
, p.prod_subcategory
, sum(s.amount_sold) AS sum_dollars
FROM sales s
, times t
, products p
, channels c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.channel_id = c.channel_id
AND t.week_ending_day='20-Dec-1998'
GROUP BY t.week_ending_day
, c.channel_desc
, p.prod_subcategory;
|
図5.3.15
画像をクリックすると拡大図をご覧いただけます
|
|
同じ問い合わせの時間の違いに注目してください。パフォーマンスが向上しています。
|
|
4.
|
データ・ウェアハウス・データベースに接続されているSQL*Plusセッションで、@query_with_mv2_join_explain.sqlと入力して、次のスクリプトを実行します（図5.3.16を参照）。
CONNECT sh/sh@orcl.world;
alter session set query_rewrite_integrity = trusted;
alter session set query_rewrite_enabled = true;
set timing on
explain plan for
SELECT t.week_ending_day
, c.channel_desc
, p.prod_subcategory
, sum(s.amount_sold) AS sum_dollars
FROM sales s
, times t
, products p
, channels c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.channel_id = c.channel_id
AND t.week_ending_day='20-Dec-1998'
GROUP BY t.week_ending_day
, c.channel_desc
, p.prod_subcategory;
@c:\oracle\ora90\rdbms\admin\utlxplp
truncate table plan_table;
|
図5.3.16
画像をクリックすると拡大図をご覧いただけます
|
|
2番目の問い合わせはクエリー・リライトにマテリアライズド・ビューFWEEK_PSCAT_SALES_MVを使用していますが、CHANNELS表に結合しなおして、マテリアライズド・ビューで欠落しているCHANNEL_DESC情報を取得します。
|
マテリアライズド・ビューを最適に利用するために、インストールの一部として、TIMES_DIM、CUSTOMERS_DIM、PRODUCTS_DIM、PROMO_DIMの各ディメンション表が作成されています。これはクエリー・リライトをより行いやすくします。
sh_hiera.sqlというスクリプトがSales Historyスキーマのインストール中に実行され、ディメンションとロールアップが作成されました。スクリプトの詳細は、 5.2.9「データ・ウェアハウス・スキーマの調査」のディメンションの項目を参照してください。
これらのディメンションとロールアップは、Oracle Enterprise Managerを使っても作成することができます。ここで、Oracle
Enterprise Managerを使用してディメンション、階層、ディメンションの属性を検査します。
- スタートボタンを押して、[プログラム]→[Oracle - OraHome90]→[Enterprise
Manager Console]を選択します。[OK]ボタンをクリックして、スタンドアロンで起動します。ツリー・リストの[ORCL.WORLD]のとなりにある[+]をクリックしてログインします。
[Username]フィールドにsystem、[Password]フィールドにmanagerと入力して[OK]ボタンをクリックします。
- [Schema]→[Dimension]→[SH]スキーマの順に開いて、[TIMES_DIM]ディメンションをクリックします。
- 作成されたロールアップを検査するために[階層]タブを、ディメンションを構成する属性を検査するために[属性]タブをそれぞれクリックして確認してください。
- Oracle Enterprise Managerを終了します。
より効果的にリライトを行うには、ディメンションが必要です。たとえば、TIMESディメンションを使用すれば、Oracle9 i
は日が月にロールされ、月が四半期にロールされることを認識できます。データベースはこれらのディメンションを活用して、よりよいパフォーマンスを得ることができます。
スター・スキーマの場合は、ディメンションをどこに作るべきかたいてい明らかで、これはSales
Historyのスター・スキーマも例外ではありません。しかしスター・スキーマに限らず、ディメンションはすべてのスキーマに作成できます。適切な階層関係を持つ列が存在する限り、ディメンションを作成できます。
問い合わせが再作成されたときに正しい結果を保証するために、新しく作成されたディメンションをすべて検証する必要があります。この検証は、ディメンション作成時のスクリプトによってTIMES、CUSTOMERS、PRODUCTS、PROMOの各ディメンションに対してはすでに行われています。
| 5.3.8 ディメンション使用時/非使用時の問い合わせの実行 |
|
問い合わせ3 ― 1999年度の四半期別総売上高
この問い合わせは、ディメンション情報に基づくTIMEロールアップと、リライトなしのときのTIMES問い合わせにおける述語解決のループバックを使用します。この問い合わせは、非常にシンプルなクエリー・リライトなしの例を使用しています。
|
1.
|
データ・ウェアハウス・データベースに接続されているSQL*Plusで、@query_without_dim_rollup.sqlと入力して次のスクリプトを実行します（図5.3.17を参照）。
CONNECT sh/sh@orcl.world;
alter session set query_rewrite_integrity = trusted;
alter session set query_rewrite_enabled = false;
set timing on
SELECT /*+ NOREWRITE */ t.calendar_quarter_desc
, sum(s.amount_sold) AS sum_amount
FROM sales s
, times t
WHERE s.time_id=t.time_id
AND t.calendar_year=1999
GROUP BY t.calendar_quarter_desc;
|
図5.3.17
画像をクリックすると拡大図をご覧いただけます
|
|
2.
|
経過時間に注目してください。再びOracle Top Sessionsを使用して、この問い合わせの実行計画を確認します（図5.3.18を参照）。
予測どおり、実表にアクセスしています。
|
図5.3.18
画像をクリックすると拡大図をご覧いただけます
|
|
3.
|
今回は、クエリー・リライトのセッションを有効にして同じ問い合わせを再実行します。
データ・ウェアハウス・データベースに接続されているSQL*Plusで、@query_with_dim_rollup.sqlと入力して次のスクリプトを実行します（図5.3.19を参照）。
CONNECT sh/sh@orcl.world;
alter session set query_rewrite_integrity = trusted;
alter session set query_rewrite_enabled = true;
set timing on
SELECT t.calendar_quarter_desc
,sum(s.amount_sold) AS sum_amount
FROM sales s
,times t
WHERE s.time_id=t.time_id
AND t.calendar_year=1999
GROUP BY t.calendar_quarter_desc;
|
図5.3.19
画像をクリックすると拡大図をご覧いただけます
|
|
4.
|
データ・ウェアハウス・データベースに接続されているSQL*Plusで、@query_with_dim_rollup_explain.sqlと入力して次のスクリプトを実行します（図5.3.20を参照）。
CONNECT sh/sh@orcl.world;
alter session set query_rewrite_integrity = trusted;
alter session set query_rewrite_enabled = true;
set timing on
explain plan for
SELECT t.calendar_quarter_desc
, sum(s.amount_sold) AS sum_amount
FROM sales s
, times t
WHERE s.time_id=t.time_id
AND t.calendar_year=1999
GROUP BY t.calendar_quarter_desc;
@c:\oracle\ora90\rdbms\admin\utlxplp
|
図5.3.20
画像をクリックすると拡大図をご覧いただけます
|
|
この問い合わせは、ディメンション情報に基づくTIMEロールアップと、リライトが有効なときのTIMES問い合わせにおける述語解決のループバックを使用します。
|
トライアル版のOracle Top Sessionsを使うために表示設定を英語に変更しましたが、これを元に戻す必要があります。しかし、次節の 5.4「ビジネス情報の分析」でもOracle Top Sessionsを使用するので、続けて演習を行う場合は、今すぐ行わず次節の演習が終わってから戻すようにしたほうがよいでしょう。
今すぐ表示設定を日本語に戻すためには、次節の 5.4.8「節のクリーンアップ」を参照してください。
この節では、次の方法を説明しました。
- マテリアライズド・ビューの作成方法とOracle Enterprise Managerによるマテリアライズド・ビューの表示方法
- マテリアライズド・ビュー使用時/非使用時の問い合わせのパフォーマンスを比較する実行方法
- ディメンションの作成方法とOracle Enterprise Managerによるディメンションの表示方法
- ディメンション使用時/非使用時の問い合わせのパフォーマンスを比較する実行方法
|
