マテリアライズドビューの作成

本記事では、SQLステートメントを使用してマテリアライズドビューを作成する方法について説明します。

権限要件

マテリアライズドビューを作成するには、CREATE TABLE 権限が必要です。OceanBaseデータベースの権限の詳細については、MySQLモードの権限分類を参照してください。

構文

マテリアライズドビューを作成するSQLステートメントの形式は次のとおりです：

CREATE MATERIALIZED VIEW view_name [([column_list] [PRIMARY KEY(column_list)])]
    [table_option_list]
    [partition_option]
    [mv_column_group_option]
    [refresh_clause]
    [query_rewrite_clause]
    [on_query_computation_clause]
    AS view_select_stmt;

パラメータ説明：

• view_name：作成するマテリアライズドビューの名前を指定します。

• column_list：オプションです。マテリアライズドビューの列リストを指定します。ビューの列に明示的な名前を指定したい場合は、column_list 句を使用し、カンマ区切りで列名を記述します。

• PRIMARY KEY(column_list)：オプションです。マテリアライズドビューの主キーを指定するために使用します。

• table_option_list：オプションです。マテリアライズドビューのテーブルオプションを指定します。

• partition_option：オプションです。マテリアライズドビューのパーティションオプションを指定します。

• mv_column_group_option：オプションです。マテリアライズドビューのストレージ形式を指定します。指定しない場合、デフォルトで行ストア形式のマテリアライズドビューが作成されます。

• refresh_clause：オプションです。マテリアライズドビューのリフレッシュ方式を指定します。

• query_rewrite_clause：オプションです。現在のマテリアライズドビューで自動リライトを有効にするかどうかを指定します。

• on_query_computation_clause：オプションです。現在のマテリアライズドビューが通常のマテリアライズドビューかリアルタイムマテリアライズドビューかを指定します。

• AS view_select_stmt：マテリアライズドビューデータのクエリ（SELECT）ステートメントを定義するために使用します。このステートメントはベーステーブルからデータを取得し、結果をマテリアライズドビューに格納します。

  説明

  • OceanBaseデータベースは、外部テーブルをマテリアライズドビューのベーステーブルとして使用して、フルリフレッシュマテリアライズドビューを作成することをサポートしています。
  • OceanBaseデータベースは、マテリアライズドビューの作成時にベーステーブルに AS OF PROCTIME() 句を追加することをサポートしています。ただし、マテリアライズドビューのベーステーブル以外の場所で AS OF PROCTIME() を使用した場合は、エラーが発生します。AS OF PROCTIME() は、増分リフレッシュ時にこのテーブルのリフレッシュをスキップするよう指定するために使用され、AS OF PROCTIME() の対象となるテーブルはmlogを作成しなくても済みます。
  • OceanBaseデータベースは、通常のビューを次元テーブル（AS OF PROCTIME()）として宣言した場合、それを増分リフレッシュマテリアライズドビューのベーステーブルとして使用できることをサポートしています。

マテリアライズドビュー作成構文の詳細なパラメータ説明については、CREATE MATERIALIZED VIEWを参照してください。

マテリアライズドビューの作成

通常マテリアライズドビューの作成

マテリアライズドビューを作成する際、DISABLE ON QUERY COMPUTATION 句を省略するか指定して通常マテリアライズドビューを作成します。

例：

1. マテリアライズドビューのベーステーブルとしてテーブル tbl1 を作成します。

   CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);
   

2. テーブル tbl1 に基づいて、mv_tbl1 という名前のマテリアライズドビューを作成します。

   CREATE MATERIALIZED VIEW mv_tbl1
       AS SELECT col1, col2
         FROM tbl1
         WHERE col3 >= 20;
   

   または

   CREATE MATERIALIZED VIEW mv_tbl1
       DISABLE ON QUERY COMPUTATION
       AS SELECT col1, col2
         FROM tbl1
         WHERE col3 >= 20;
   

ネストマテリアライズドビューの作成

ネストマテリアライズドビューとは、既存のマテリアライズドビューに基づいて構築されるマテリアライズドビューです。例えば、次の図では、マテリアライズドビュー mv1 はテーブル tbl1 とテーブル tbl2 に基づいて構築されており、典型的なマテリアライズドビューです。一方、マテリアライズドビュー mv2 はマテリアライズドビュー mv1 とテーブル tbl3 に基づいて構築されており、ネストマテリアライズドビューに該当します。同様に、マテリアライズドビュー mv3 はマテリアライズドビュー mv1 とマテリアライズドビュー mv2 に基づいて構築されており、これもネストマテリアライズドビューです。

OceanBaseデータベースでは、ネストマテリアライズドビューを作成する際にリフレッシュ戦略を指定できます。取り得る値は以下の通りです：

• INDIVIDUAL：デフォルト値で、独立リフレッシュを示します。
• INCONSISTENT：カスケード非一貫性リフレッシュを示します。
• CONSISTENT：カスケード一貫性リフレッシュを示します。

ネストマテリアライズドビューの制限事項

• ネストマテリアライズドビューの増分更新をサポートするためには、マテリアライズドビュー（ベーステーブル）にmlogを作成する必要があります。
• あるマテリアライズドビューがフル更新された場合、それに依存するマテリアライズドビュー（ネストマテリアライズドビュー）は、その後の増分更新を行う前に、まずフル更新を1回実行する必要があります。そうでない場合、エラーが発生します。
• ネストマテリアライズドビューはリアルタイムマテリアライズドビューとして作成することはできません。つまり、ネストマテリアライズドビューの作成時に ENABLE ON QUERY COMPUTATION 句を指定することはできません。

例：

1. テーブル tbl3 をマテリアライズドビューのベーステーブルとして作成します。

   CREATE TABLE tbl3(id INT, name VARCHAR(30), PRIMARY KEY(id));
   

2. テーブル tbl4 をマテリアライズドビューのベーステーブルとして作成します。

   CREATE TABLE tbl4(id INT, age INT, PRIMARY KEY(id));
   

3. テーブル tbl3 と tbl4 に基づいて、マテリアライズドビュー mv1_tbl3_tbl4 を作成します。

   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;
   

4. マテリアライズドビュー mv1_tbl3_tbl4 に基づいて、マテリアライズドビュー（ネストマテリアライズドビュー） mv_mv1_tbl3_tbl4 を作成します。

   CREATE MATERIALIZED VIEW mv_mv1_tbl3_tbl4
       REFRESH COMPLETE
       AS SELECT SUM(AGE) age_sum
           FROM mv1_tbl3_tbl4;
   

5. マテリアライズドビュー mv1_tbl3_tbl4 に基づいて、マテリアライズドビュー（ネストマテリアライズドビュー） mv1_mv1_tbl3_tbl4 を作成し、更新戦略を INCONSISTENT に設定します。

   CREATE MATERIALIZED VIEW mv1_mv1_tbl3_tbl4
       REFRESH COMPLETE INCONSISTENT
       AS SELECT SUM(AGE) age_sum
           FROM mv1_tbl3_tbl4;
   

リアルタイムマテリアライズドビューの作成

マテリアライズドビューを作成する際に、ENABLE ON QUERY COMPUTATION 句を指定してリアルタイムマテリアライズドビューを作成します。

リアルタイムマテリアライズドビューの注意事項

• リアルタイムマテリアライズドビューを作成する前に、マテリアライズドビューが依存するベーステーブルすべてでマテリアライズドビューのログを作成する必要があります。

  説明

  OceanBaseデータベースはマテリアライズドビューのログの自動管理機能をサポートしています。mlogの自動管理を有効にしている場合、リアルタイムマテリアライズドビューを作成する前に、ユーザーはベーステーブルのmlogを作成する必要はありません。OceanBaseデータベースは対応するmlogを自動的に作成するか、既存のmlogテーブル定義を更新し、新しく作成されたマテリアライズドビューが依存する列を含めます。詳細については、マテリアライズドビューのログの自動管理を参照してください。

• 特定のタイプのマテリアライズドビューのみがリアルタイムマテリアライズドビューとして指定することをサポートしています。条件を満たさないマテリアライズドビューにリアルタイムマテリアライズドビューを指定すると、エラーが発生します。リアルタイムマテリアライズドビューの要件は、増分更新を行うマテリアライズドビューの要件と同じです。詳細については、マテリアライズドビューの更新の増分更新の基本要件を参照してください。

  • MIN/MAX 関数を使用するマテリアライズドビューはリアルタイムマテリアライズドビューをサポートしません。
  • 外部結合を含む複合マテリアライズドビューはリアルタイムマテリアライズドビューをサポートしません。
  • 集合クエリを含むマテリアライズドビューはリアルタイムマテリアライズドビューをサポートしません。
  • ネストされたマテリアライズドビューはリアルタイムマテリアライズドビューとして作成することはできません。

• クエリを実行するセッションのシステム変数値と、マテリアライズドビュー作成時にマテリアライズドビューに固定されたセッション変数値が一致しない場合、セッション上のシステム変数値をリアルタイムマテリアライズドビューに固定されたセッション変数値に修正する必要があります。そうでない場合、リアルタイムマテリアライズドビューは利用できなくなり、リアルタイムマテリアライズドビューのクエリ書き換えが反映されなかったり、直接リアルタイムマテリアライズドビューをクエリした際にエラーが発生したりします。

例：

1. マテリアライズドビューのベーステーブルとしてテーブル tbl2 を作成します。

   CREATE TABLE tbl2(col1 INT, col2 INT, col3 INT);
   

2. tbl2 テーブルにマテリアライズドビューのログを作成します。

   CREATE MATERIALIZED VIEW LOG ON tbl2
       WITH PRIMARY KEY, ROWID, SEQUENCE (col1, col2, col3) INCLUDING NEW VALUES;
   

3. テーブル tbl2 に基づいてリアルタイムマテリアライズドビュー mv_tbl2 を作成します。

   CREATE MATERIALIZED VIEW mv_tbl2
       ENABLE ON QUERY COMPUTATION
       AS SELECT col1, count(*) AS cnt
          FROM tbl2
          GROUP BY col1;
   

4. リアルタイムマテリアライズドビューを作成した後、ビュー DBA_MVIEWS を使用して、マテリアライズドビューの位置付けがリアルタイムマテリアライズドビューであるかどうかを確認できます。

   SELECT MVIEW_NAME, ON_QUERY_COMPUTATION
   FROM oceanbase.DBA_MVIEWS
   WHERE MVIEW_NAME = 'mv_tbl2';
   

   実行結果は次のとおりです：

   +------------+----------------------+
   | MVIEW_NAME | ON_QUERY_COMPUTATION |
   +------------+----------------------+
   | mv_tbl2    | Y                    |
   +------------+----------------------+
   1 row in set
   

5. リアルタイムマテリアライズドビューの実行計画を確認します。

   EXPLAIN BASIC SELECT * FROM mv_tbl2;
   

   以下の実行計画からわかるように、実行時にはマテリアライズドビューとビューが依存するベーステーブルのmlogから同時にデータを読み取り、これら2つのデータを計算・統合して、最終的にリアルタイムのマテリアライズドビューデータを取得します。

   実行結果は次のとおりです：

   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                                                       |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | ==============================================                                                                                                                                   |
   | |ID|OPERATOR                   |NAME         |                                                                                                                                   |
   | ----------------------------------------------                                                                                                                                   |
   | |0 |HASH GROUP BY              |             |                                                                                                                                   |
   | |1 |└─SUBPLAN SCAN             |INNER_RT_MV$$|                                                                                                                                   |
   | |2 |  └─UNION ALL              |             |                                                                                                                                   |
   | |3 |    ├─TABLE FULL SCAN      |mv_tbl2      |                                                                                                                                   |
   | |4 |    └─HASH GROUP BY        |             |                                                                                                                                   |
   | |5 |      └─SUBPLAN SCAN       |DLT_T$$      |                                                                                                                                   |
   | |6 |        └─WINDOW FUNCTION  |             |                                                                                                                                   |
   | |7 |          └─TABLE FULL SCAN|mlog$_tbl2   |                                                                                                                                   |
   | ==============================================                                                                                                                                   |
   | Outputs & filters:                                                                                                                                                               |
   | -------------------------------------                                                                                                                                            |
   |   0 - output([INNER_RT_MV$$.col1], [cast(T_FUN_SUM(INNER_RT_MV$$.cnt), BIGINT(20, 0))]), filter([T_FUN_SUM(INNER_RT_MV$$.cnt) > cast(0, DECIMAL_INT(64,                          |
   |       0))]), rowset=16                                                                                                                                                           |
   |       group([INNER_RT_MV$$.col1]), agg_func([T_FUN_SUM(INNER_RT_MV$$.cnt)])                                                                                                      |
   |   1 - output([INNER_RT_MV$$.col1], [INNER_RT_MV$$.cnt]), filter(nil), rowset=16                                                                                                  |
   |       access([INNER_RT_MV$$.col1], [INNER_RT_MV$$.cnt])                                                                                                                          |
   |   2 - output([UNION([1])], [UNION([2])]), filter(nil), rowset=16                                                                                                                 |
   |   3 - output([mv_tbl2.col1], [cast(mv_tbl2.cnt, DECIMAL_INT(42, 0))]), filter(nil), rowset=16                                                                                    |
   |       access([mv_tbl2.col1], [mv_tbl2.cnt]), partitions(p0)                                                                                                                      |
   |       is_index_back=false, is_global_index=false,                                                                                                                                |
   |       range_key([mv_tbl2.__pk_increment]), range(MIN ; MAX)always true                                                                                                           |
   |   4 - output([DLT_T$$.col1], [T_FUN_SUM(CASE WHEN DLT_T$$.OLD_NEW$$ = 'N' THEN 1 ELSE -1 END)]), filter(nil), rowset=16                                                          |
   |       group([DLT_T$$.col1]), agg_func([T_FUN_SUM(CASE WHEN DLT_T$$.OLD_NEW$$ = 'N' THEN 1 ELSE -1 END)])                                                                         |
   |   5 - output([DLT_T$$.OLD_NEW$$], [DLT_T$$.col1]), filter([DLT_T$$.OLD_NEW$$ = 'N' AND DLT_T$$.SEQUENCE$$ = DLT_T$$.MAXSEQ$$ OR DLT_T$$.OLD_NEW$$ = 'O'                          |
   |       AND DLT_T$$.SEQUENCE$$ = DLT_T$$.MINSEQ$$]), rowset=16                                                                                                                     |
   |       access([DLT_T$$.OLD_NEW$$], [DLT_T$$.SEQUENCE$$], [DLT_T$$.MAXSEQ$$], [DLT_T$$.MINSEQ$$], [DLT_T$$.col1])                                                                  |
   |   6 - output([mlog$_tbl2.OLD_NEW$$], [mlog$_tbl2.SEQUENCE$$], [T_FUN_MAX(mlog$_tbl2.SEQUENCE$$)], [T_FUN_MIN(mlog$_tbl2.SEQUENCE$$)], [mlog$_tbl2.col1]), filter(nil), rowset=16 |
   |       win_expr(T_FUN_MAX(mlog$_tbl2.SEQUENCE$$)), partition_by([mlog$_tbl2.M_ROW$$]), order_by(nil), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED             |
   |       FOLLOWING)                                                                                                                                                                 |
   |       win_expr(T_FUN_MIN(mlog$_tbl2.SEQUENCE$$)), partition_by([mlog$_tbl2.M_ROW$$]), order_by(nil), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED             |
   |       FOLLOWING)                                                                                                                                                                 |
   |   7 - output([mlog$_tbl2.M_ROW$$], [mlog$_tbl2.SEQUENCE$$], [mlog$_tbl2.OLD_NEW$$], [mlog$_tbl2.col1], [ORA_ROWSCN]), filter([ORA_ROWSCN > last_refresh_scn(500452)]), rowset=16 |
   |       access([mlog$_tbl2.M_ROW$$], [mlog$_tbl2.SEQUENCE$$], [mlog$_tbl2.OLD_NEW$$], [mlog$_tbl2.col1], [ORA_ROWSCN]), partitions(p0)                                             |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                                                |
   |       range_key([mlog$_tbl2.M_ROW$$], [mlog$_tbl2.SEQUENCE$$]), range(MIN,MIN ; MAX,MAX)always true                                                                              |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   38 rows in set
   

クエリのリライトを有効にしたマテリアライズドビューの作成

マテリアライズドビューを作成する際、ENABLE QUERY REWRITE 句を指定して、そのマテリアライズドビューの自動クエリリライトを有効にします。マテリアライズドビューのリライトおよびリライト制御の詳細については、マテリアライズドビューのクエリリライトを参照してください。

例：

1. テーブル tbl1 に基づいてマテリアライズドビュー mv_spj_tbl1 を作成し、自動リライトを有効にします。

   CREATE MATERIALIZED VIEW mv_spj_tbl1
       ENABLE QUERY REWRITE
       AS SELECT *
          FROM tbl1;
   

2. マテリアライズドビュー作成後、ビュー DBA_MVIEWS を使用して、マテリアライズドビューの自動リライトが有効かどうか確認できます。

   SELECT MVIEW_NAME, REWRITE_ENABLED
   FROM oceanbase.DBA_MVIEWS
   WHERE MVIEW_NAME = 'mv_spj_tbl1';
   

   実行結果は次のとおりです：

   +-------------+-----------------+
   | MVIEW_NAME  | REWRITE_ENABLED |
   +-------------+-----------------+
   | mv_spj_tbl1 | Y               |
   +-------------+-----------------+
   1 row in set
   

カラムストア形式マテリアライズドビューの作成

OceanBaseデータベースは、行ストア、カラムストア、および行ストア・カラムストアの冗長形式のマテリアライズドビューをサポートしています。mv_column_group_optionオプションを指定することで、カラムストアまたは行ストア・カラムストアの冗長形式のマテリアライズドビューを明示的に作成できます。マテリアライズドビューが複数のテーブルをJOINして形成されたワイドテーブルの場合、カラムストア形式のマテリアライズドビューを作成することで、特定のクエリのパフォーマンスを向上させることができます。WITH COLUMN GROUP(each column)を指定することで、カラムストア形式のマテリアライズドビューを作成します。

例：

テーブルtbl1に基づいて、カラムストア形式のマテリアライズドビューmv_ec_tbl1を作成します。

CREATE MATERIALIZED VIEW mv_ec_tbl1
    WITH COLUMN GROUP(each column)
    AS SELECT *
       FROM tbl1;

マテリアライズドビュー作成時の主キーの追加

例：

テーブル tbl1 に基づいて、mv_pk_tbl1 という名前のマテリアライズドビューを作成し、主キーを指定します。

CREATE MATERIALIZED VIEW mv_pk_tbl1(v_id, v_name, PRIMARY KEY(v_id))
    AS SELECT col1, col2
       FROM tbl1
       WHERE col3 >= 20;

マテリアライズドビュー作成時のテーブルオプションとパーティションオプションの追加

マテリアライズドビューを作成する際には、テーブルオプションを設定できます。また、データの特性とアクセスパターンに応じて適切なパーティションオプションを設計・構成することで、クエリ性能と管理効率を向上させることができます。

テーブルオプションとパーティションオプションの詳細なパラメータ説明については、CREATE TABLEを参照してください。

例：

テーブル tbl1 に基づいて、mv_pp_tbl1 という名前のマテリアライズドビューを作成します。マテリアライズドビューの並列度を 5 に指定し、col1 列に基づいてHashパーティションに分割し、8 つのパーティションに分けます。tbl1 テーブルの条件 col3 >= 20 を満たすレコードをベーステーブルとしてクエリし、そのクエリ結果をマテリアライズドビューのデータとします。

CREATE MATERIALIZED VIEW mv_pp_tbl1
    PARALLEL 5
    PARTITION BY HASH(col1) PARTITIONS 8
    AS SELECT col1, col2
       FROM tbl1
       WHERE col3 >= 20;

マテリアライズドビューにインデックスを追加する

マテリアライズドビューを作成するステートメントでは、直接インデックスを作成することはできませんが、CREATE INDEX ステートメントまたは ALTER TABLE ステートメントを使用して、マテリアライズドビューにインデックスを作成できます。

例：

• マテリアライズドビュー mv_tbl1 の col1 列に idx1_mv_tbl1 という名前のインデックスを作成します。

  CREATE INDEX idx1_mv_tbl1 ON mv_tbl1(col1);
  

• マテリアライズドビュー mv_tbl1 の col2 列に idx2_mv_tbl1 という名前のインデックスを作成します。

  ALTER TABLE mv_tbl1 ADD INDEX idx2_mv_tbl1(col2);
  

マテリアライズドビューの更新

OceanBaseデータベースのマテリアライズドビューは、フル更新、増分更新、ハイブリッド更新、および更新しないの4種類の更新ポリシーをサポートしています。詳細は以下のとおりです：

• フル更新：マテリアライズドビュー全体のデータを再計算し、ビュー内のデータがソーステーブルと完全に一致することを保証します。
• 増分更新：ソーステーブルで変更されたデータのみを更新し、ビュー全体の再計算を回避します。
• ハイブリッド更新：デフォルトのオプションです。まず増分更新を試み、増分更新が失敗した場合にフル更新を実行します。
• 更新しない：マテリアライズドビューは作成時にのみ更新され、作成後は再度更新することはできません。

マテリアライズドビューの更新に関する詳細は、マテリアライズドビューの更新を参照してください。

フル更新マテリアライズドビューの作成

マテリアライズドビューを作成する際に、REFRESH COMPLETE 句を使用して、マテリアライズドビューの更新ポリシーをフル更新に設定します。

例：

テーブル tbl1 に基づいて、mv_rc_tbl1 という名前のマテリアライズドビューを作成し、マテリアライズドビューの更新ポリシーをフル更新 (REFRESH COMPLETE) に指定します。また、tbl1 テーブルから col3 が 20 以上の条件を満たす col1 および col2 列を選択して、マテリアライズドビューのデータソースとします。

CREATE MATERIALIZED VIEW mv_rc_tbl1
    REFRESH COMPLETE
    AS SELECT col1, col2
       FROM tbl1
       WHERE col3 >= 20;

外部テーブルに基づいてフルリフレッシュマテリアライズドビューを作成する

OceanBaseデータベースは、外部テーブルをマテリアライズドビューのベーステーブルとして使用し、フルリフレッシュマテリアライズドビューを作成することをサポートしています。

外部テーブルの詳細については、外部テーブルについてを参照してください。

例：

以下では、外部ファイルがローカルにある場合とOceanBaseデータベースのMySQLモードにある場合の2つの例を挙げて、外部テーブルを作成します。手順は以下の通りです：

1. 外部ファイルを準備します。

   以下のコマンドを実行し、OBServerノードにログインするマシンの/home/adminディレクトリにext_tbl1.csvファイルを作成します。

   [admin@xxx /home/admin]# vi ext_tbl1.csv
   

   ファイルの内容は以下の通りです：

   1,'A1','2025-01-01'
   2,'A2','2025-02-01'
   3,'A3','2025-03-01'
   

2. インポートファイルのパスを設定します。

   注意

   セキュリティ上の理由から、システム変数secure_file_privを設定する際は、ローカルソケット接続を介してデータベースに接続し、このグローバル変数を変更するSQLステートメントを実行する必要があります。詳細については、secure_file_privを参照してください。

   1. 以下のコマンドを実行し、OBServerノードが存在するマシンにログインします。

      ssh admin@10.10.10.1
      

   2. 以下のコマンドを実行し、ローカルUnixソケット接続を介してテナントmysql001に接続します。

      obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
      

   3. 以下のSQLコマンドを実行し、インポートパスを/home/adminに設定します。

      SET GLOBAL secure_file_priv = "/home/admin";
      

3. テナントmysql001に再接続します。

   例：

   obclient -h10.10.10.1 -P2881 -uroot@mysql001 -p****** -A -Ddb_test
   

4. 外部テーブルext_tbl1を作成します。

   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';
   

5. 外部テーブルext_tbl1に基づいて、フルリフレッシュマテリアライズドビューmv_ext_tbl1を作成します。

   CREATE MATERIALIZED VIEW mv_ext_tbl1
       REFRESH COMPLETE
       AS SELECT *
           FROM ext_tbl1;
   

6. マテリアライズドビューmv_ext_tbl1のデータを確認します。

   SELECT * FROM mv_ext_tbl1;
   

   実行結果は次のとおりです：

   +------+------+------------+
   | id   | name | c_date     |
   +------+------+------------+
   |    1 | A1   | 2025-01-01 |
   |    3 | A3   | 2025-03-01 |
   |    2 | A2   | 2025-02-01 |
   +------+------+------------+
   3 rows in set
   

増分更新マテリアライズドビューの作成

マテリアライズドビューを作成する際に、REFRESH FAST 句を使用して、マテリアライズドビューの更新ポリシーを増分更新に設定します。

増分更新マテリアライズドビューの作成に関する注意事項

• 増分更新をサポートするマテリアライズドビューは、現在、単一テーブルの非集計、単一テーブルの集計、複数テーブルの結合、複数テーブルの結合と集計、および集合クエリ(UNION ALL)のSQLステートメントをサポートしています。これら5つのシナリオに該当しないSQLステートメントについては、現在増分更新はサポートされていません。増分更新をサポートするSQLステートメントの要件については、マテリアライズドビューの更新の増分更新セクションを参照してください。

• REFRESH FASTメソッドは、マテリアライズドビューのログに記録された情報を使用して、増分更新が必要な内容を特定します。そのため、マテリアライズドビューを増分更新する場合は、マテリアライズドビューを作成する前に、ベーステーブルのマテリアライズドビューのログを作成する必要があります。マテリアライズドビューのログの作成方法については、マテリアライズドビューのログを参照してください。

  説明

  OceanBaseデータベースは、マテリアライズドビューのログの自動管理機能をサポートしています。mlogの自動管理を有効にすると、増分更新マテリアライズドビューを作成する前に、ユーザーはベーステーブルのmlogを作成する必要がありません。OceanBaseデータベースは、対応するmlogを自動的に作成するか、既存のmlogテーブル定義を更新し、新しく作成されたマテリアライズドビューが依存する列を含めます。詳細については、マテリアライズドビューのログの自動管理を参照してください。

• マテリアライズドビューを作成する際には、ベーステーブルにAS OF PROCTIME()句を追加できます。ただし、ベーステーブルの位置以外でAS OF PROCTIME()を使用した場合は、エラーが発生します。

  • AS OF PROCTIME()は、増分更新時にこのテーブルの更新をスキップし、マテリアライズドビューの増分更新を高速化するために使用されます。また、AS OF PROCTIME()を指定したテーブルはmlogを作成する必要がありません。このテーブルにエイリアスを使用する必要がある場合は、テーブルエイリアスをAS OF PROCTIME()句の後に配置する必要があります。

  • 通常のビューを次元テーブル(AS OF PROCTIME())として宣言し、それを増分更新マテリアライズドビューのベーステーブルとして使用する場合、以下の制限があります：

    • ベーステーブルと同様に、マテリアライズドビューで使用されるすべてのテーブルが次元テーブルであることは許可されません。

例：

1. テーブル tbl5 をマテリアライズドビューのベーステーブルとして作成します。

   CREATE TABLE tbl5 (col1 INT PRIMARY KEY, col2 INT, col3 INT);
   

2. tbl5 テーブルにマテリアライズドビューのログを作成し、ログのオプションとして SEQUENCE を指定します。これは、変更されたデータを識別するためにシーケンス番号を使用することを示します。列部分では、col2 と col3 を含む記録する列を指定します。

   CREATE MATERIALIZED VIEW LOG ON tbl5
       WITH SEQUENCE (col2, col3) INCLUDING NEW VALUES;
   

3. テーブル tbl5 に基づいて、mv_tbl5 という名前のマテリアライズドビューを作成し、マテリアライズドビューの更新戦略を増分更新として指定します。クエリ部分では、tbl5 テーブルを col2 列でグループ化し、各グループのレコード数 (cnt)、NULLではない col3 列のレコード数 (cnt_col3)、および col3 列の合計 (sum_col3) をマテリアライズドビューの結果として指定します。

   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;
   

4. テーブル tbl5 と tbl1 に基づいて、mv2_tbl5_tbl1 という名前のマテリアライズドビューを作成し、マテリアライズドビューの更新戦略を増分更新として指定します。col1 フィールドで内部結合 (INNER JOIN) を行います。AS OF PROCTIME() を使用して、マテリアライズドビューの増分更新時にテーブル tbl1 をスキップするよう指定します。

   CREATE MATERIALIZED VIEW mv2_tbl5_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT
           t5.col1 tbl5_c1,
           t1.col1 tbl1_c1,
           t5.col2 tbl5_c2,
           t1.col2 tbl1_c2
       FROM tbl5 t5
           INNER JOIN tbl1 AS OF PROCTIME() t1
           ON t5.col1 = t1.col1
       WHERE t5.col2 = 3;
   

5. AS OF PROCTIME() として宣言された通常のビューに基づいて、増分更新マテリアライズドビューを作成します。

   1. テーブル tbl5 に基づいてビュー v1_tbl5 を作成します。

      obclient> CREATE VIEW v1_tbl5 AS SELECT * FROM tbl5;
      

   2. テーブル tbl5 と v1_tbl5 に基づいて、mv3_tbl5_v_tbl5 という名前のマテリアライズドビューを作成し、マテリアライズドビューの更新戦略を増分更新として指定します。col1 フィールドで結合を行います。AS OF PROCTIME() を使用して、ビュー v1_tbl5 を次元テーブルとして指定します。

      obclient> CREATE MATERIALIZED VIEW mv3_tbl5_v_tbl5
          AS SELECT
              a.col1 a_c1,
              b.col1 b_c1
          FROM tbl5 a JOIN v1_tbl5 AS OF PROCTIME() b
          ON a.col1 = b.col1;
      

ハイブリッド更新マテリアライズドビューの作成（デフォルトオプション）

マテリアライズドビューを作成する際に、REFRESH FORCE 句を省略するか指定して、マテリアライズドビューの更新ポリシーをハイブリッド更新に設定します。

例：

テーブル tbl1 に基づいて、mv_rf_tbl1 という名前のマテリアライズドビューを作成し、マテリアライズドビューの更新ポリシーをハイブリッド更新に指定します。また、tbl1 テーブルから col3 が 20 以上の条件を満たす col1 および col2 列を選択して、マテリアライズドビューのデータソースとします。

CREATE MATERIALIZED VIEW mv_rf_tbl1
    REFRESH FORCE
    AS SELECT col1, col2
       FROM tbl1
       WHERE col3 >= 20;

永遠にリフレッシュしないマテリアライズドビューの作成

マテリアライズドビューを作成する際、NEVER REFRESH 句を使用して、マテリアライズドビューがリフレッシュ不要であることを設定します。これは、マテリアライズドビューが作成時にのみリフレッシュされ、作成後は再度リフレッシュすることが許可されないことを意味します。

例：

テーブル tbl1 に基づいて、mv_nr_tbl1 という名前のマテリアライズドビューを作成し、そのリフレッシュポリシーを永遠にリフレッシュしないように指定します。また、tbl1 テーブルから col3 が 20 以上の条件を満たす col1 および col2 列を選択して、マテリアライズドビューのデータソースとします。

CREATE MATERIALIZED VIEW mv_nr_tbl1
    NEVER REFRESH
    AS SELECT col1, col2
       FROM tbl1
       WHERE col3 >= 20;

自動更新マテリアライズドビューの作成

マテリアライズドビューを作成する際に、START WITH datetime_expr および NEXT datetime_expr 句を指定することで、バックグラウンドで自動的に更新されるタスクを作成できます。

例：

テーブル tbl1 に基づいて、mv_rc_swn_tbl1 という名前のマテリアライズドビューを作成し、マテリアライズドビューの更新戦略をフル更新に指定します。また、マテリアライズドビューの更新計画で、初期更新時刻を現在日付に設定し、その後は 1 日ごとにマテリアライズドビューを更新します。

CREATE MATERIALIZED VIEW mv_rc_swn_tbl1
    REFRESH COMPLETE
        START WITH sysdate() NEXT sysdate() + interval 1 day
    AS SELECT col1, col2
       FROM tbl1
       WHERE col3 >= 20;

関連ドキュメント

• DBMS_MVIEWの概要
• マテリアライズドビューの概要
• マテリアライズドビューのログ
• マテリアライズドビューの更新
• マテリアライズドビューによるクエリのリライト
• マテリアライズドビューのクエリ
• マテリアライズドビューの変更
• マテリアライズドビューの削除