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

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

権限要件

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

構文

マテリアライズドビューを作成する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]：オプションで、具体的には以下のとおりです：

  • 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 NUMBER PRIMARY KEY, col2 VARCHAR2(20), col3 NUMBER);
   

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

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

   または

   CREATE MATERIALIZED VIEW mv_tbl1
       REFRESH FORCE
       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 句を指定することはサポートされていません。

例：

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

   CREATE TABLE tbl3(id INT, name VARCHAR2(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
       REFRESH COMPLETE ON DEMAND
       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 sys.DBA_MVIEWS
   WHERE MVIEW_NAME = 'MV_TBL2';
   

   注意

   Oracleモードでは、ビュー sys.DBA_MVIEWS のフィールド MVIEW_NAME がテーブル名と一致する場合、テーブル名は大文字を使用する必要があります。

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

   +------------+----------------------+
   | 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), NUMBER(38, 0))]), filter([T_FUN_SUM(INNER_RT_MV$$.CNT) > cast(0, NUMBER(-1, -85))]), 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], [MV_TBL2.CNT]), 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$$ = cast('N', VARCHAR2(1048576 )) THEN cast(1, NUMBER(-1, -85)) ELSE (T_OP_NEG, cast(1,                        |
   |        NUMBER(-1, -85))) END)]), filter(nil), rowset=16                                                                                                                          |
   |       group([DLT_T$$.COL1]), agg_func([T_FUN_SUM(CASE WHEN DLT_T$$.OLD_NEW$$ = cast('N', VARCHAR2(1048576 )) THEN cast(1, NUMBER(-1, -85)) ELSE (T_OP_NEG,                       |
   |        cast(1, NUMBER(-1, -85))) END)])                                                                                                                                          |
   |   5 - output([DLT_T$$.OLD_NEW$$], [DLT_T$$.COL1]), filter([DLT_T$$.OLD_NEW$$ = cast('N', VARCHAR2(1048576 )) AND DLT_T$$.SEQUENCE$$ = DLT_T$$.MAXSEQ$$ OR                        |
   |       DLT_T$$.OLD_NEW$$ = cast('O', VARCHAR2(1048576 )) 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([cast(ORA_ROWSCN, NUMBER(-1,                        |
   |        -1)) > last_refresh_scn(500155)]), 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                                                                              |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   40 rows in set
   

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

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

例：

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

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

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

   SELECT MVIEW_NAME, REWRITE_ENABLED
   FROM sys.DBA_MVIEWS
   WHERE MVIEW_NAME = 'MV_SPJ_TBL1';
   

   注意

   Oracleモードでは、ビュー sys.DBA_MVIEWS のフィールド MVIEW_NAME がテーブル名と一致する場合、テーブル名は大文字で指定する必要があります。

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

   +-------------+-----------------+
   | 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 ステートメントを使用してマテリアライズドビューにインデックスを作成できます。

例：

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

CREATE INDEX idx_mv_tbl1 ON mv_tbl1(col1);

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

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データベースのOracleモードに存在する場合に外部テーブルを作成する例です。手順は以下のとおりです：

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

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

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

   ファイルの内容は次のとおりです：

   1,'A1'
   2,'A2'
   3,'A3'
   

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

   注意

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

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

      ssh admin@10.10.10.1
      

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

      obclient -S /home/admin/oceanbase/run/sql.sock -usys@oracle001 -p******
      

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

      SET GLOBAL secure_file_priv = "/home/admin/external_csv";
      

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

   例：

   obclient -h10.10.10.1 -P2881 -usys@oracle001 -p****** -A
   

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

   CREATE EXTERNAL TABLE ext_tbl1 (
       id INT,
       name VARCHAR2(50)
       )
       LOCATION = '/home/admin/external_csv'
       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 |
   +------+------+
   |    2 | A2   |
   |    1 | A1   |
   |    3 | A3   |
   +------+------+
   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という名前のマテリアライズドビューを作成し、マテリアライズドビューの更新戦略を増分更新(REFRESH FAST)と指定します。クエリ部分では、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 という名前のマテリアライズドビューを作成し、マテリアライズドビューのリフレッシュポリシーをハイブリッドリフレッシュ（REFRESH FORCE）に指定します。また、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 という名前のマテリアライズドビューを作成し、マテリアライズドビューのリフレッシュポリシーを永遠にリフレッシュしない（NEVER REFRESH）と指定します。また、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 current_date NEXT current_date + INTERVAL '1' HOUR
    AS SELECT col1, col2
       FROM tbl1
       WHERE col3 >= 20;

関連ドキュメント

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