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

本記事では、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 データベース V4.3.5 バージョンにおいて：

  • V4.3.5 BP2 バージョンより、外部テーブルをマテリアライズドビューのベーステーブルとして使用し、フル更新のマテリアライズドビューを作成することがサポートされました。
  • V4.3.5 BP4 バージョンより、マテリアライズドビュー作成時にベーステーブルに対して AS OF PROCTIME() 句を追加することがサポートされました。ただし、マテリアライズドビューのベーステーブル定義以外の場所で AS OF PROCTIME() を使用するとエラーになります。AS OF PROCTIME() は、増分更新時にそのテーブルの更新をスキップすることを指定するために使用され、AS OF PROCTIME() が指定されたテーブルには mlog の作成が不要となります。
  • V4.3.5 BP5 バージョンより、通常のビューをディメンションテーブル（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データベースV4.3.5バージョンでは、V4.3.5 BP3バージョンからネストされたマテリアライズドビューのリフレッシュポリシーを指定できるようになりました。値は以下の通りです：

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

ネストされたマテリアライズドビュー機能の制限

• ネストされたマテリアライズドビューの増分更新をサポートするためには、マテリアライズドビュー（ベーステーブル）にmlogを作成する必要があります。
• ネストされたマテリアライズドビューはリアルタイム・マテリアライズドビューとして作成できません。つまり、ネストされたマテリアライズドビューを作成する際に、ENABLE ON QUERY COMPUTATION句を指定することはサポートされていません。
• マテリアライズドビュー（ネストされたマテリアライズドビュー）がリアルタイムマテリアライズドビューの場合、下層のマテリアライズドビューに対して増分更新を行い、mlogを更新する必要があります。リアルタイムマテリアライズドビューのクエリ結果は、mlogをシミュレーションする方法で下層のマテリアライズドビューと自身の結果を統合して取得します。したがって、リアルタイムマテリアライズドビューのデータが最新であることを保証するために、まず下層のマテリアライズドビューに対して増分更新を行い、mlogを更新する必要があります。

例：

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 句を指定してリアルタイムマテリアライズドビューを作成します。

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

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

  説明

  V4.3.5バージョンでは、V4.3.5 BP4バージョンからマテリアライズドビューログの自動管理機能がサポートされています。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 列に基づいてハッシュパーティションを行い、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データベースは、V4.3.5 BP2バージョンから外部テーブルをマテリアライズドビューの基準テーブルとして使用し、フル更新マテリアライズドビューを作成できるようになりました。

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

例：

以下は、外部ファイルがローカルおよびOceanBaseデータベースのMySQLモードに存在する場合に外部テーブルを作成する例です。手順は以下のとおりです：

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 を設定する際は、ローカルのUnixソケット経由でデータベースに接続し、このグローバル変数を変更する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文の要件については、マテリアライズドビューの更新の増分更新セクションを参照してください。

  説明

  OceanBaseデータベースV4.3.5バージョンでは、マテリアライズドビューの増分更新はV4.3.5 BP3バージョンから単一テーブル非集計および集合クエリシナリオをサポートしています。

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

  説明

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

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

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

    説明

    OceanBaseデータベースV4.3.5バージョンでは、V4.3.5 BP4バージョンから、マテリアライズドビューを作成する際に基準テーブルにAS OF PROCTIME()句を追加することがサポートされています。

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

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

    説明

    OceanBaseデータベースV4.3.5バージョンでは、V4.3.5 BP5バージョンから、通常のビューを次元テーブル(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)、空ではない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の概要
• マテリアライズドビューの概要
• マテリアライズドビューログ
• マテリアライズドビューの更新
• マテリアライズドビューによるクエリの書き換え
• マテリアライズドビューのクエリ
• マテリアライズドビューの変更
• マテリアライズドビューの削除