本記事では、MySQLモードにおける ステップバイステップSQL演習（orders 入門、tbl* クイックスニペット、外部テーブル を含むフルMVなど）を提供しており、セクションの順に操作できます。マテリアライズドビューによるクエリの高速化 の sales / items メインストーリーの例と併用してください：業務テーブルを通じた完全な演習が必要な場合は、まずこのドキュメントを読んでください。
説明
構文や制限条件などの詳細情報については、次のドキュメントを参照してください：マテリアライズドビューの作成、マテリアライズドビューの更新、マテリアライズドビューによるクエリのリライト、マテリアライズドビューのログ、マテリアライズドビューのログの自動管理。
マテリアライズドビューを活用したリアルタイムデータウェアハウスの構築：シナリオによる選定
リアルタイムデータウェアハウス構築における迅速な意思決定を容易にするため、「データの鮮度要件 → 計算複雑さ → データソースタイプ」の順序で選定します。
ターゲットシナリオ |
推奨機能構成 |
典型的な特徴 |
対応する章 |
|---|---|---|---|
| 週次集計ダッシュボード、分単位更新 | REFRESH FAST + Mlog + 定時スケジューリング |
増分メンテナンスコストが低く、安定した集計口径に適している | 入門実践；シナリオ2：リアルタイム指標加工パイプライン |
| 詳細ワイドテーブルのスキャン、レポートのドリルダウン | カラムストアMV + クエリのリライト（オプション） | 全列をスキャンするよりも列数が少ない場合の効果が顕著 | シナリオ1：運用分析とレポートのドリルダウン |
| 低遅延オンラインクエリ（クエリトリガーによる補算） | ENABLE ON QUERY COMPUTATION |
読み取り時の計算により更新時の処理を簡略化し、ホットクエリモデルに適している | シナリオ2：リアルタイム指標加工パイプライン |
| SQLの透過的な高速化（業務SQLの変更を最小限に抑える） | ENABLE QUERY REWRITE + インデックス/集計口径の整合性 |
リライトのマッチングルールに依存するため、固定の分析テンプレートに適している | シナリオ2：リアルタイム指標加工パイプライン |
| ファイル取り込み後の定期ロード | 外部テーブル + REFRESH COMPLETE |
データソースはファイルから取得し、オフライン同期後にクエリを実行する | シナリオ3：レイクハウスへのデータ取り込みと定期ロード |
推奨される実践的な手順は以下の通りです：
- まず、集計基準と更新ポリシーを定義する：「分単位の増分」か「クエリ時のリアルタイム計算」かを明確にします。
- 次に、更新メカニズムを決定する：FAST（Mlog）が条件を満たしているか優先的に評価し、満たない場合にCOMPLETEを使用します。
- 最後に、クエリ側の最適化を行う：必要に応じて、カラムストア、インデックス、およびQUERY REWRITEを段階的に導入し、一度に多くの変数を導入することを避けます。
注意
リアルタイムデータウェアハウスは「すべてのクエリがリアルタイムで更新される」という意味ではありません。業務上、分単位の遅延が許容される場合は、FAST + スケジューリングを優先的に採用します。低遅延が必須であり、読み取り時のオーバヘッドを許容できる場合にのみ、ENABLE ON QUERY COMPUTATIONを導入します。
入門実践：注文テーブルにおけるフル更新と増分更新
以下では、業務データベースで一般的な 注文テーブル を例に説明します。デフォルトのフルメンテナンス のマテリアライズドビュー、Mlog + FAST による増分メンテナンス、手動更新 および スケジュール更新 の基本的な使い方を示します（マテリアライズドビューの更新と同じです）。
テーブルの作成、データの生成、フルマテリアライズドビューの作成
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
item_id INT,
item_count INT,
item_price INT,
region VARCHAR(100)
);
INSERT INTO orders VALUES
(1, 10001, 1, 20, 100, 'HZ'),
(2, 10002, 1, 10, 150, 'BJ'),
(3, 10001, 2, 50, 50, 'SH');
CREATE MATERIALIZED VIEW mv1
AS
SELECT region, SUM(item_count * item_price) AS sum_price
FROM orders
GROUP BY region;
SELECT * FROM mv1;
マテリアライズドビューを作成する際には、まず 初回マテリアライズ が行われます。明示的にリフレッシュポリシーを宣言していない場合、通常は 手動でのフルリフレッシュ が必要であり、これによりベーステーブルとの一貫性を維持できます。
手動でのフルリフレッシュ（DBMS_MVIEW.REFRESH）
ベーステーブルへの書き込みを続けると、マテリアライズドビューの結果は自動的に変更されません。リフレッシュをトリガーする必要があります：
INSERT INTO orders VALUES
(4, 10002, 2, 10, 100, 'SH'),
(5, 10003, 1, 2, 20, 'HZ');
SELECT * FROM mv1;
実行結果：
+--------+-----------+
| region | sum_price |
+--------+-----------+
| HZ | 2000 |
| BJ | 1500 |
| SH | 2500 |
+--------+-----------+
3 rows in set
マテリアライズドビューの結果が自動的に変更されていないことが確認できます。以下のSQLを実行してリフレッシュをトリガーする必要があります：
CALL DBMS_MVIEW.REFRESH('mv1', 'c', refresh_parallel => 2);
SELECT * FROM mv1;
実行結果：
+--------+-----------+
| region | sum_price |
+--------+-----------+
| HZ | 2040 |
| BJ | 1500 |
| SH | 3500 |
+--------+-----------+
3 rows in set
'c' は complete なフルリフレッシュを表します。refresh_parallel などのパラメータは DBMS_MVIEW.REFRESH を参照してください。
リフレッシュの実行状況を確認できます（例）：
SELECT MVIEWS, METHOD, START_TIME, END_TIME
FROM oceanbase.DBA_MVREF_RUN_STATS;
削除後は増分リフレッシュに切り替える：Mlog + FAST
DROP MATERIALIZED VIEW mv1;
CREATE MATERIALIZED VIEW LOG ON orders
WITH PRIMARY KEY, ROWID, SEQUENCE (item_count, item_price, region) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW mv1(region, c, sum_price, cnt_price)
REFRESH FAST
ON DEMAND START WITH sysdate() NEXT sysdate() + INTERVAL 5 MINUTE
AS
SELECT region,
COUNT(*) AS c,
SUM(item_count * item_price) AS sum_price,
COUNT(item_count * item_price) AS cnt_price
FROM orders
GROUP BY region;
新規注文を挿入した後、まずMlogを確認し、その後 手動で増分リフレッシュ を行います。
INSERT INTO orders VALUES (6, 10001, 3, 30, 70, 'HZ');
SELECT * FROM mlog$_orders;
CALL DBMS_MVIEW.REFRESH('mv1', 'f');
SELECT * FROM mlog$_orders;
SELECT region, sum_price FROM mv1;
作成時に START WITH ... NEXT ... が設定されている場合は、DBA_SCHEDULER_JOBS でバックグラウンドのリフレッシュタスクを確認することもできます（具体的なフィールドは現在のバージョンのディクショナリ説明に準じます）。
オブジェクトの削除順序
マテリアライズドビュー、マテリアライズドビューのログ、およびベーステーブルを削除する際は、MVを削除してから、Mlogを削除し、最後にベーステーブルを削除する ことに注意してください（ベーステーブルがまだMlogにバインドされている場合、直接 DROP TABLE するとエラーが発生する可能性があります）。
DROP MATERIALIZED VIEW mv1;
DROP MATERIALIZED VIEW LOG ON orders;
DROP TABLE orders;
シナリオ1：運用分析とレポートクエリ
運用分析およびレポートクエリのシナリオでは、一般的な課題として明細テーブルの列数が多く、スキャン範囲が広く、クエリ条件が頻繁に変更されることが挙げられます。
このようなシナリオは通常、読み取り処理が主体となるため、カラムストアマテリアライズドビューを活用して不要な列のスキャンを削減し、必要に応じてインデックスやクエリのリライトを組み合わせることで、クエリパフォーマンスをさらに最適化することが適しています。
このセクションでは、簡単な例を通じて、カラムストアマテリアライズドビューを使用してワイドテーブルのクエリと多次元分析をサポートする方法を説明します。
CREATE TABLE IF NOT EXISTS tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);
CREATE MATERIALIZED VIEW mv_ec_tbl1
WITH COLUMN GROUP(each column)
AS SELECT *
FROM tbl1;
クエリのリライトを併用する場合、通常はリライト後の条件がマテリアライズドビュー上のインデックスを利用できることを保証する必要があります（ルールについては マテリアライズドビューによるクエリのリライト を参照）：
CREATE INDEX idx1_mv_ec_tbl1 ON mv_ec_tbl1(col1);
シナリオ2：リアルタイム指標の計算とクエリ
リアルタイム指標シナリオでは、階層集計、増分メンテナンス、低遅延クエリ、および業務SQLの変更を最小限に抑えることが一般的な要件です。
このようなシナリオは通常、単一の機能ではカバーできず、業務の時間的要件に応じて、ネストされたマテリアライズドビュー、マテリアライズドビューのログ、FAST更新、リアルタイムマテリアライズドビュー、クエリのリライトなどの機能を組み合わせて使用する必要があります。
ネストマテリアライズドビュー
クエリプロセスに複数層の集計や複数段階の処理が含まれる場合、中間結果を最初に1層のマテリアライズドビューに格納し、その上にさらに次の層のマテリアライズドビューを作成することができます。この方法は階層的な事前計算のシナリオに適しており、複雑な指標を複数段階のメンテナンスに分解することができます。
CREATE TABLE IF NOT EXISTS tbl3(id INT, name VARCHAR(30), PRIMARY KEY(id));
CREATE TABLE IF NOT EXISTS tbl4(id INT, age INT, PRIMARY KEY(id));
CREATE MATERIALIZED VIEW mv1_tbl3_tbl4 (PRIMARY KEY (id1, id2))
REFRESH COMPLETE
AS SELECT tbl3.id id1, tbl4.id id2, tbl3.name, tbl4.age
FROM tbl3, tbl4
WHERE tbl3.id = tbl4.id;
CREATE MATERIALIZED VIEW mv_mv1_tbl3_tbl4
REFRESH COMPLETE
AS SELECT SUM(age) age_sum
FROM mv1_tbl3_tbl4;
CREATE MATERIALIZED VIEW mv1_mv1_tbl3_tbl4
REFRESH COMPLETE INCONSISTENT
AS SELECT SUM(age) age_sum
FROM mv1_tbl3_tbl4;
ある層でフル更新が実行された場合、それに依存するネストされたマテリアライズドビューは、その後の増分更新を行う前に通常、再度フル更新を行う必要があります（作成/更新のドキュメントを参照）。
マテリアライズドビューのログと増分更新（FAST）
業務では毎回すべてのデータを再計算するよりも更新効率の方が重要な場合、マテリアライズドビューのログとFAST更新を組み合わせて使用することを検討できます。この方法ではベーステーブルの変更を記録し、更新時には可能な限り増分データのみを処理します。
Mlog自動管理が有効になっていない場合は、REFRESH FASTマテリアライズドビューを作成する前にmlogを作成する必要があります。
CREATE TABLE IF NOT EXISTS tbl5 (col1 INT PRIMARY KEY, col2 INT, col3 INT);
CREATE MATERIALIZED VIEW LOG ON tbl5
WITH SEQUENCE (col2, col3) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW mv_tbl5
REFRESH FAST
AS SELECT col2, COUNT(*) cnt, COUNT(col3) cnt_col3, SUM(col3) sum_col3
FROM tbl5
GROUP BY col2;
CALL DBMS_MVIEW.REFRESH('mv_tbl5');
CALL DBMS_MVIEW.REFRESH('mv_tbl5', 'c');
リアルタイムマテリアライズドビュー
シナリオによっては、クエリの即時性に対する要求が高く、できるだけリアルタイムの結果に近づけたい一方で、頻繁な能動的更新には適していない場合があります。このような場合、実際のニーズに応じてリアルタイムマテリアライズドビューの使用を評価し、クエリ時の計算によるデータの高い鮮度を得ることができます。
CREATE TABLE IF NOT EXISTS tbl2(col1 INT, col2 INT, col3 INT);
CREATE MATERIALIZED VIEW LOG ON tbl2
WITH PRIMARY KEY, ROWID, SEQUENCE (col1, col2, col3) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW mv_tbl2_rt
ENABLE ON QUERY COMPUTATION
AS SELECT col1, COUNT(*) AS cnt
FROM tbl2
GROUP BY col1;
クエリのリライト（ENABLE QUERY REWRITE）
業務SQLの変更を最小限に抑えつつ、オプティマイザーがマテリアライズドビューの結果を自動的に利用できるようにするには、クエリのリライト機能を組み合わせることができます。クエリのリライトは、分析シナリオが固定されている場合に適していますが、リライトが成功するかどうかは、クエリ条件とマテリアライズドビューの定義が一致するかどうかに依存します。
CREATE TABLE IF NOT EXISTS test_tbl1 (col1 INT, col2 INT, col3 INT);
CREATE TABLE IF NOT EXISTS test_tbl2 (col1 INT, col2 INT, col3 INT);
CREATE MATERIALIZED VIEW mv_test_tbl1_tbl2
ENABLE QUERY REWRITE
AS SELECT t1.col1 col1, t1.col2 t1col2, t1.col3 t1col3, t2.col2 t2col2, t2.col3 t2col3
FROM test_tbl1 t1, test_tbl2 t2
WHERE t1.col1 = t2.col1;
SET query_rewrite_enabled = 'force';
SELECT COUNT(*), test_tbl1.col1 col1
FROM test_tbl1, test_tbl2
WHERE test_tbl1.col1 = test_tbl2.col1 AND test_tbl2.col2 > 10
GROUP BY col1;
シナリオ3：ファイルデータのインポートと定期的なリフレッシュ
データがオンライン業務テーブルではなくファイルから取得される場合、まず外部テーブルを使用してファイルデータを読み取り、その後外部テーブルに基づいてフルリフレッシュマテリアライズドビューを作成できます。この方法は、定期的な更新、オフライン同期後のクエリ、およびレイクハウスシナリオにおける定期的なデータ取り込み要件に適しています。
V4.3.5 BP2 以降では、外部テーブルをベーステーブルとして フルリフレッシュ マテリアライズドビューを作成できるようになりました。事前に secure_file_priv を設定し、外部ファイルを準備して CREATE EXTERNAL TABLE を実行する必要があります（手順の詳細は マテリアライズドビューの作成 — 外部テーブルに基づく を参照）。
CREATE EXTERNAL TABLE ext_tbl1 (
id INT,
name VARCHAR(50),
c_date DATE
)
LOCATION = '/home/admin'
FORMAT = (
TYPE = 'CSV'
FIELD_DELIMITER = ','
FIELD_OPTIONALLY_ENCLOSED_BY = '\''
)
PATTERN = 'ext_tbl1.csv';
CREATE MATERIALIZED VIEW mv_ext_tbl1
REFRESH COMPLETE
AS SELECT * FROM ext_tbl1;
外部テーブルの実践例については、外部テーブルによるデータのインポートおよび外部テーブルの概要を参照してください。