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

本記事では、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 [PARALLEL n] [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 [PARALLEL n] [query_rewrite_clause] [on_query_computation_clause]：オプションです。詳細は以下のとおりです：

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

  • PARALLEL n：リフレッシュの並列度を指定します。

    説明

    V4.4.2バージョンでは、V4.4.2 BP2バージョンから、REFRESH 句内で REFRESH ... PARALLEL n を使用してリフレッシュの並列度を指定できるようになりました。

  • 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 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データベースでは、ネストマテリアライズドビューの作成時にリフレッシュポリシーを指定でき、その値は以下の通りです：

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

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

• リアルタイムマテリアライズドビューを作成する前に、そのマテリアライズドビューが依存するすべてのベーステーブルに対して、マテリアライズドビューのログを作成する必要があります。OceanBaseデータベースはマテリアライズドビューのログの自動管理機能をサポートしており、システムはデフォルトでマテリアライズドビューのログを自動作成します。詳細については、マテリアライズドビューのログを参照してください。

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

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

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

例：

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

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

2. テーブル 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;
   

3. リアルタイムマテリアライズドビューを作成した後、ビュー 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
   

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

   EXPLAIN BASIC SELECT * FROM mv_tbl2;
   

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

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

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

例：

マテリアライズドビュー 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データベースでは、外部テーブルをマテリアライズドビューのベーステーブルとして使用して、フル更新マテリアライズドビューを作成できます。

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

例：

以下では、外部ファイルの場所がローカルにある場合と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 を設定する際は、ローカルソケット接続を介してデータベースに接続し、このグローバル変数を変更する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文の要件の詳細については、マテリアライズドビューの更新の増分更新セクションを参照してください。

  注意

  V4.4.2バージョンでは、V4.4.2 BP2バージョン以降、増分更新マテリアライズドビューを作成する際、マテリアライズドビューの定義に集計関数やUNION ALL演算子が含まれる場合でも、対応する依存列を手動で指定する必要がなくなりました。

• REFRESH FASTメソッドは、マテリアライズドビューのログに記録された情報を利用して、増分更新が必要な内容を特定します。OceanBaseデータベースはマテリアライズドビューのログの自動管理機能をサポートしており、システムはデフォルトでマテリアライズドビューのログを自動的に作成します。詳細については、マテリアライズドビューのログを参照してください。

• マテリアライズドビューを作成する際、ベーステーブルに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 テーブルに基づいて、mv_tbl5 という名前のマテリアライズドビューを作成し、マテリアライズドビューのリフレッシュ戦略を増分リフレッシュ（REFRESH FAST）に設定します。クエリ部分では、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;
   

   V4.4.2バージョンでは、V4.4.2 BP2バージョン以降、増分リフレッシュマテリアライズドビューを作成する際に、マテリアライズドビューの定義に集約関数や UNION ALL 演算子が含まれる場合、対応する依存列を手動で指定する必要がなくなりました。以下のステートメントを使用してマテリアライズドビューを作成できます：

   CREATE MATERIALIZED VIEW mv2_tbl5
       REFRESH FAST
       AS SELECT col2, SUM(col3) sum_col3
          FROM tbl5
          GROUP BY col2;
   

3. 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;
   

4. 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;

マテリアライズドビュー作成時のリフレッシュ並列度の指定

マテリアライズドビューを作成する際に、REFRESH ... PARALLEL n を使用してリフレッシュ並列度を指定します。

例：

tbl1 テーブルに基づいて、mv_rcp3_tbl1 という名前のマテリアライズドビューを作成し、マテリアライズドビューのリフレッシュ戦略をフルリフレッシュ（REFRESH COMPLETE）に設定します。また、クエリ並列度を16、リフレッシュ並列度を5に指定し、tbl1 テーブルから col3 が 20 以上の条件を満たす col1 と col2 列を選択して、マテリアライズドビューのデータソースとして指定します。

obclient> CREATE MATERIALIZED VIEW mv_rcp3_tbl1
    PARALLEL 16
    REFRESH COMPLETE PARALLEL 5 ON DEMAND
    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の概要
• マテリアライズドビューの概要
• マテリアライズドビューのログ
• マテリアライズドビューのリフレッシュ
• マテリアライズドビューによるクエリのリライト
• マテリアライズドビューのクエリ
• マテリアライズドビューの変更
• マテリアライズドビューの削除