8 データ・ウェアハウスのリフレッシュ

データ・ウェアハウスを定期的に更新し、現在の情報を導出します。データの更新プロセスをリフレッシュ処理と呼び、この章では次の内容について説明します。

データ・ウェアハウスのリフレッシュについて
ローリング・ウィンドウを使用したデータのオフロード

データ・ウェアハウスのリフレッシュについて

抽出、変換およびロード(ETL)は、スケジュールに基づいて実行され、元のソース・システムへの変更が反映されます。この手順の間、新規の更新されたデータを本番データのウェアハウス・スキーマに物理的に挿入し、ユーザーがこのデータを使用できるようにするために、他のすべての必要な手順(索引の作成、制限の検証、バックアップ・コピーの作成)に従います。このデータをすべてデータ・ウェアハウスにロードしたら、マテリアライズド・ビューを更新して最新のデータを反映する必要があります。

データ・ウェアハウスのパーティショニング・スキームは、データ・ウェアハウスのロード・プロセスでの操作のリフレッシュの効率性を決定する際に重要です。ロード・プロセスは、データ・ウェアハウス表のパーティショニング・スキームの選択時に考慮されます。

多くのデータ・ウェアハウスでは、定期的に新規データがロードされます。たとえば、日、週または月ごとに新規データがデータ・ウェアハウスに取り込まれます。週末または月末にロードされたデータは通常、その週またはその月のトランザクションに対応します。この一般的なシナリオでは、データ・ウェアハウスは時間ごとにロードされます。そのため、データ・ウェアハウス表をデータ列上でパーティション化することをお薦めします。データ・ウェアハウスの例として、新規データが毎月、売上表にロードされると想定します。さらに、sales表は毎月、パーティション化されるものとします。これらの手順では、ロード・プロセスによる新しい月(2006年第1四半期)のsales表へのデータの追加処理方法を示します。

例: データ・ウェアハウスのリフレッシュ

多くの問合せでは、日付で問合せが制限されているproducts、customersおよびsales表の一部の列が要求されます。マテリアライズド・ビューを使用すると、3つの表に対する問合せの多くが高速化されます。上部にマテリアライズド・ビューが作成される事前作成表を使用します。sales表のパーティション計画を持つ同期のマテリアライズド・ビューのパーティション計画を選択します。

次に、マテリアライズド・ビューのリフレッシュに関する例を示します。ここでは、パーティション交換ロード操作を使用します。この例はshスキーマのsales表に基づいています。

マテリアライズド・ビューをリフレッシュする手順

マテリアライズド・ビューに基づいた表を作成します。

CREATE TABLE sales_prod_cust_mv
( time_id DATE
, prod_id NUMBER
, prod_name VARCHAR2(50)
, cust_id NUMBER
, cust_first_name VARCHAR2(20)
, cust_last_name VARCHAR2(40)
, amount_sold NUMBER
, quantity_sold NUMBER
)
PARTITION BY RANGE (time_id)
( PARTITION p1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
, PARTITION p2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY'))
, PARTITION p2001h1 VALUES LESS THAN (TO_DATE('01-JUL-2001','DD-MON-YYYY'))
, PARTITION p2001h2 VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY'))
, PARTITION p2001q1 VALUES LESS THAN (TO_DATE('01-APR-2002','DD-MON-YYYY'))
, PARTITION p2002q2 VALUES LESS THAN (TO_DATE('01-JUL-2002','DD-MON-YYYY'))
, PARTITION p2002q3 VALUES LESS THAN (TO_DATE('01-OCT-2002','DD-MON-YYYY'))
, PARTITION p2002q4 VALUES LESS THAN (TO_DATE('01-JAN-2003','DD-MON-YYYY'))
, PARTITION p2003q1 VALUES LESS THAN (TO_DATE('01-APR-2003','DD-MON-YYYY'))
, PARTITION p2003q2 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY'))
, PARTITION p2003q3 VALUES LESS THAN (TO_DATE('01-OCT-2003','DD-MON-YYYY'))
, PARTITION p2003q4 VALUES LESS THAN (TO_DATE('01-JAN-2004','DD-MON-YYYY'))
, PARTITION p2004q1 VALUES LESS THAN (TO_DATE('01-APR-2004','DD-MON-YYYY'))
, PARTITION p2004q2 VALUES LESS THAN (TO_DATE('01-JUL-2004','DD-MON-YYYY'))
, PARTITION p2004q3 VALUES LESS THAN (TO_DATE('01-OCT-2004','DD-MON-YYYY'))
, PARTITION p2004q4 VALUES LESS THAN (TO_DATE('01-JAN-2005','DD-MON-YYYY'))
, PARTITION p2005q1 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY'))
, PARTITION p2005q2 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY'))
, PARTITION p2005q3 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY'))
, PARTITION p2005q4 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
, PARTITION p2006q1 VALUES LESS THAN (TO_DATE('01-APR-2006','DD-MON-YYYY'))
) PARALLEL COMPRESS;

sales表の初期表をロードします。

ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL smv */ INTO sales_prod_cust_mv smv
SELECT /*+ PARALLEL s PARALLEL c */ s.time_id
, s.prod_id
, p.prod_name
, s.cust_id
, cust_first_name
, c.cust_last_name
, SUM(s.amount_sold)
, SUM(s.quantity_sold)
FROM sales s
, products p
, customers c
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
GROUP BY s.time_id
, s.prod_id
, p.prod_name
, s.cust_id
, c.cust_first_name
, c.cust_last_name;
COMMIT;

マテリアライズド・ビューを作成します。

CREATE MATERIALIZED VIEW sales_prod_cust_mv
ON PREBUILT TABLE
ENABLE QUERY REWRITE
AS SELECT s.time_id
, s.prod_id
, p.prod_name
, s.cust_id
, c.cust_first_name
, c.cust_last_name
, SUM(s.amount_sold) amount_sold
, SUM(s.quantity_sold) quantity_sold
FROM sales s
, products p
, customers c
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
GROUP BY s.time_id
, s.prod_id
, p.prod_name
, s.cust_id
, c.cust_first_name
, c.cust_last_name;

新規パーティションで交換される個別の表をロードします。

CREATE TABLE sales_q1_2006 PARALLEL COMPRESS
AS SELECT * FROM sales
WHERE 0 = 1;
 
/* This would be the regular ETL job */
 
ALTER SESSION ENABLE PARALLEL DML;
 
INSERT /* PARALLEL qs */ INTO sales_q1_2006 qs
SELECT /* PARALLEL s */ prod_id
, cust_id
, add_months(time_id,3)
, channel_id
, promo_id
, quantity_sold
, amount_sold
FROM sales PARTITION(sales_q4_2005) s;
 
COMMIT;
 
CREATE BITMAP INDEX bmp_indx_prod_id ON sales_q1_2006 (prod_id);
CREATE BITMAP INDEX bmp_indx_cust_id ON sales_q1_2006 (cust_id);
CREATE BITMAP INDEX bmp_indx_time_id ON sales_q1_2006 (time_id);
CREATE BITMAP INDEX bmp_indx_channel_id ON sales_q1_2006 (channel_id);
CREATE BITMAP INDEX bmp_indx_promo_id ON sales_q1_2006 (promo_id);
 
ALTER TABLE sales_q1_2006 ADD CONSTRAINT sales_q_prod_fk
FOREIGN KEY (prod_id) REFERENCES products(prod_id) ENABLE NOVALIDATE;
 
ALTER TABLE sales_q1_2006 ADD CONSTRAINT sales_q_cust_fk
FOREIGN KEY (cust_id) REFERENCES customers(cust_id) ENABLE NOVALIDATE;
 
ALTER TABLE sales_q1_2006 ADD CONSTRAINT sales_q_time_fk
FOREIGN KEY (time_id) REFERENCES times(time_id) ENABLE NOVALIDATE;
 
ALTER table sales_q1_2006 ADD CONSTRAINT sales_q_channel_fk
FOREIGN KEY (channel_id) REFERENCES channels(channel_id) ENABLE NOVALIDATE;
 
ALTER table sales_q1_2006 ADD CONSTRAINT sales_q_promo_fk
FOREIGN KEY (promo_id) REFERENCES promotions(promo_id) ENABLE NOVALIDATE;
 
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('SH','SALES_Q1_2006');
END;
/

マテリアライズド・ビューのパーティションで交換される個別の表を作成およびロードします。

CREATE TABLE sales_mv_q1_2006 PARALLEL COMPRESS
AS SELECT * FROM sales_prod_cust_mv
WHERE 1 = 0;

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ PARALLEL smv */ INTO sales_mv_q1_2006 smv
SELECT /*+ PARALLEL s PARALLEL c */ s.time_id
, s.prod_id
, p.prod_name
, s.cust_id
, cust_first_name
, c.cust_last_name
, SUM(s.amount_sold)
, SUM(s.quantity_sold)
FROM sales_q1_2006 s
, products p
, customers c 
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
GROUP BY s.time_id
, s.prod_id
, p.prod_name
, s.cust_id 
, c.cust_first_name
, c.cust_last_name;

COMMIT;

統計を収集します。

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('SH','SALES_MV_Q1_2006');
END;

パーティションを交換します。

ALTER TABLE sales
EXCHANGE PARTITION sales_q1_2006
WITH TABLE sales_q1_2006
INCLUDING INDEXES WITHOUT VALIDATION;
 
ALTER TABLE sales_prod_cust_mv
EXCHANGE PARTITION p2006q1
WITH TABLE sales_mv_q1_2006
INCLUDING INDEXES WITHOUT VALIDATION;

マテリアライズド・ビューが再度フレッシュになったことがデータベースに通知されます。
```
ALTER MATERIALIZED VIEW sales_prod_cust_mv CONSIDER FRESH;
```

このシナリオでは、事前作成表を使用し、この制約はRELY制約ではないため、クエリー・リライト機能はquery_rewrite_integrityパラメータをSTALE_TOLERATEDに設定した場合にのみ有効です。

ローリング・ウィンドウを使用したデータのオフロード

データを削除したりアーカイブする際にローリング・ウィンドウを使用すると特に効果的です。たとえば、最新の36か月の売上データをデータ・ウェアハウスに格納する場合にローリング・ウィンドウを使用します。新規パーティションは、新しい月のsales表にそれぞれ追加され、古いパーティションはsales表から削除される場合があります。この方法では、ウェアハウスに常に36か月分のデータが保持されます。

例: ローリング・ウィンドウの使用

次にshスキーマのsales表のローリング・ウィンドウの例を示します。

ローリング・ウィンドウを使用する手順

2005年12月の売上を追加します。
```
ALTER TABLE sales
ADD PARTITION sales_12_2005 VALUES LESS THAN ('01-JAN-2006');
```
既存の索引を再作成する必要があります。
1999年のパーティションを削除します。
```
ALTER TABLE sales
DROP PARTITION sales_1999;
```