CREATE MATERIALIZED VIEW

説明

このステートメントは、マテリアライズドビューを作成するために使用されます。

マテリアライズドビューとは、クエリ結果のコピーを格納し、データを最新の状態に保つために定期的に（または手動で）更新される特殊なタイプのデータベースオブジェクトです。マテリアライズドビューには集計、結合、サブクエリなどの操作が含まれることがあり、さらにパフォーマンスを向上させるためにインデックスやパーティションを設定することも可能です。

権限要件

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

構文

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;

column_list:
    column_name [, column_name ...]

refresh_clause:
    REFRESH [refresh_option [nested_refresh_option]] [ON DEMAND | COMMIT | STATEMENT] [[START WITH expr] [NEXT expr]]
    | NEVER REFRESH

refresh_option:
    COMPLETE
    | FAST
    | FORCE

nested_refresh_option:
    INDIVIDUAL
    | INCONSISTENT
    | CONSISTENT

query_rewrite_clause:
    [ENABLE | DISABLE] QUERY REWRITE

on_query_computation_clause:
    [ENABLE | DISABLE] ON QUERY COMPUTATION

mv_column_group_option:
    WITH COLUMN GROUP (ALL COLUMNS)
    | WITH COLUMN GROUP (EACH COLUMN)
    | WITH COLUMN GROUP(all columns, each column)

パラメータの説明

mv_column_group_option

• WITH COLUMN GROUP(all columns)：行ストア形式のマテリアライズドビューを作成することを指定します。

• WITH COLUMN GROUP(each column)：カラムストア形式のマテリアライズドビューを作成することを指定します。

• WITH COLUMN GROUP(all columns, each column)：行ストアとカラムストアの冗長形式のマテリアライズドビューを作成することを指定します。

refresh_clause

• REFRESH [refresh_option [nested_refresh_option]] [ON DEMAND | COMMIT | STATEMENT] [[START WITH expr] [NEXT expr]]：マテリアライズドビューのリフレッシュ方法を示します。

  • refresh_option：マテリアライズドビューのリフレッシュ方式を指定します。オプションです。リフレッシュ方式を指定しない場合、デフォルトは FORCE です。値は以下のとおりです：

    • COMPLETE：フル更新を行うことを示します。つまり、マテリアライズドビュー全体のデータを再計算し、ビュー内のデータがソーステーブルと完全に一致するようにします。

    • FAST：増分更新を行うことを示します。つまり、ソーステーブルの変更に関連するデータのみをリフレッシュし、ビュー全体の完全な計算を回避します。

      注意

      • REFRESH FASTメソッドは、マテリアライズドビューログ内のレコード情報を利用して、増分更新が必要な内容を決定するため、増分更新でマテリアライズドビューをリフレッシュする際には、マテリアライズドビューを作成する前に、ベーステーブルのマテリアライズドビューログ(mlog)を作成する必要があります。
      • マテリアライズドビューで使用される列はすべてmlogに存在していなければなりません。

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

    • FORCE：デフォルト値で、ハイブリッドリフレッシュを行うことを示します。最初に増分更新を試み、増分更新に失敗した場合はフル更新を実行します。

    • nested_refresh_option：ネストされたマテリアライズドビューのリフレッシュポリシーを指定します。オプションです。リフレッシュポリシーを指定しない場合、デフォルトは INDIVIDUAL です。値は以下のとおりです：

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

      説明

      • OceanBaseデータベースV4.3.5では、V4.3.5 BP3以降のバージョンからパラメータ nested_refresh_option（ネストされたマテリアライズドビューのリフレッシュポリシーを指定）がサポートされています。
      • ネストされていないマテリアライズドビューについては、カスケードリフレッシュ動作は存在せず、どのようなリフレッシュポリシーを指定しても意味がなく、デフォルトで独立したリフレッシュが行われます。指定された3種類のリフレッシュポリシーは、バックグラウンドタスクでのみ有効であり、手動でPLパッケージ(DBMS_MVIEW.REFRESH)を使用してリフレッシュをスケジュールする場合にのみ、指定されたPLパラメータに従ってリフレッシュが実行されます。

  • [ON DEMAND | COMMIT | STATEMENT]：マテリアライズドビューのリフレッシュタイミングを指定します。

    • ON DEMAND：オンデマンドリフレッシュであり、DBMS_MVIEW.REFRESHプロシージャを使用して手動でリフレッシュするか、START WITH/NEXT句を使用して自動リフレッシュ計画を設定する必要があります。
    • COMMIT：ベーステーブルのトランザクションコミット時にマテリアライズドビューを自動リフレッシュします。
    • STATEMENT：ベーステーブルのDMLステートメントを含むトランザクションのコミット時にマテリアライズドビューを自動リフレッシュします。

  • [START WITH expr]：オプションで、マテリアライズドビューの最初の自動リフレッシュ時間を指定します。

  • [NEXT expr]：オプションで、マテリアライズドビューの自動リフレッシュ時間間隔を指定します。

• NEVER REFRESH：マテリアライズドビューをリフレッシュする必要がないことを指定します。つまり、マテリアライズドビューは作成時にのみリフレッシュされ、作成後は再度リフレッシュすることが許可されません。

query_rewrite_clause

• [ENABLE | DISABLE] QUERY REWRITE：クエリリライト機能を有効にするかどうかを指定します。
  • ENABLE QUERY REWRITE：クエリリライトを有効にし、オプティマイザーがマテリアライズドビューを使用してクエリをリライトできるようにします。
  • DISABLE QUERY REWRITE：デフォルト値で、クエリリライトを無効にします。

マテリアライズドビューのリライトの詳細については、マテリアライズドビューによるクエリリライトを参照してください。

on_query_computation_clause

• DISABLE ON QUERY COMPUTATION：デフォルト値で、通常のマテリアライズドビューを作成することを指定します。

• ENABLE ON QUERY COMPUTATION：リアルタイムマテリアライズドビューを作成することを指定します。

  注意

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

リアルタイムマテリアライズドビューの詳細については、マテリアライズドビューの作成のリアルタイムマテリアライズドビューの作成セクションを参照してください。

例

1. 売上関連の基礎テーブルを作成します。

   -- プロダクトテーブルを作成
   CREATE TABLE products (
       product_id     NUMBER PRIMARY KEY,
       product_name   VARCHAR2(100) NOT NULL,
       category_id    NUMBER,
       unit_price     NUMBER(10,2),
       create_time    DATE DEFAULT SYSDATE
   );
   
   -- 売上注文書を作成
   CREATE TABLE sales_orders (
       order_id       NUMBER PRIMARY KEY,
       product_id     NUMBER,
       quantity       NUMBER,
       amount         NUMBER(10,2),
       order_date     DATE DEFAULT SYSDATE
   );
   
   -- 顧客テーブルを作成
   CREATE TABLE customers (
       customer_id    NUMBER PRIMARY KEY,
       customer_name  VARCHAR2(100) NOT NULL,
       email          VARCHAR2(100),
       phone          VARCHAR2(20),
       address        VARCHAR2(200),
       created_at     TIMESTAMP DEFAULT SYSTIMESTAMP
   );
   

2. 静的レポート用に、自動リフレッシュされないマテリアライズドビューを作成します。

   -- プロダクトカテゴリ別の売上集計レポートを作成（自動リフレッシュなし）
   CREATE MATERIALIZED VIEW mv_product_category_sales
   NEVER REFRESH
   AS
   SELECT
       p.category_id,
       COUNT(DISTINCT so.order_id) AS order_count,
       SUM(so.quantity) AS total_quantity,
       SUM(so.amount) AS total_amount
   FROM
       sales_orders so
   JOIN
       products p ON so.product_id = p.product_id
   GROUP BY
       p.category_id;
   

3. 定期的に更新される分析レポート用に、オンデマンドでリフレッシュされるマテリアライズドビューを作成します。

   -- 月次売上傾向分析マテリアライズドビューを作成
   CREATE MATERIALIZED VIEW mv_monthly_sales_trend
   REFRESH COMPLETE ON DEMAND
   AS
   SELECT
       TO_CHAR(TRUNC(order_date, 'MM'), 'YYYY-MM') AS month,
       p.category_id,
       COUNT(DISTINCT order_id) AS order_count,
       SUM(quantity) AS total_quantity,
       SUM(amount) AS total_amount
   FROM
       sales_orders so
   JOIN
       products p ON so.product_id = p.product_id
   WHERE
       order_date >= ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12)  -- 過去12か月間のデータ
   GROUP BY
       TO_CHAR(TRUNC(order_date, 'MM'), 'YYYY-MM'),
       p.category_id
   ORDER BY
       month, p.category_id;
   

4. リアルタイムモニタリング用に、自動リフレッシュ計画を持つマテリアライズドビューを作成します。

   -- ヒット商品ランキングを作成（毎日午前2時に自動リフレッシュ）
   CREATE MATERIALIZED VIEW mv_hot_products
   REFRESH COMPLETE
   START WITH TRUNC(SYSDATE) + 1 + 2/24  -- 翌日午前2時
   NEXT TRUNC(SYSDATE) + 1 + 2/24        -- その後は毎日午前2時にリフレッシュ
   AS
   SELECT
       p.product_id,
       p.product_name,
       p.category_id,
       COUNT(DISTINCT so.order_id) AS order_count,
       SUM(so.quantity) AS total_quantity,
       SUM(so.amount) AS total_amount
   FROM
       sales_orders so
   JOIN
       products p ON so.product_id = p.product_id
   WHERE
       so.order_date >= TRUNC(SYSDATE) - 30
   GROUP BY
       p.product_id, p.product_name, p.category_id
   ORDER BY
       total_quantity DESC
   FETCH FIRST 100 ROWS ONLY;
   

5. マテリアライズドビューにインデックスを作成して、クエリパフォーマンスを向上させます。

   -- 購買行動分析マテリアライズドビューを作成
   CREATE MATERIALIZED VIEW mv_customer_behavior
   REFRESH COMPLETE ON DEMAND
   AS
   SELECT
       product_id,
       COUNT(DISTINCT order_id) AS purchase_times,
       SUM(quantity) AS total_quantity,
       MIN(order_date) AS first_purchase_date,
       MAX(order_date) AS last_purchase_date
   FROM
       sales_orders
   WHERE
       order_date >= ADD_MONTHS(TRUNC(SYSDATE), -12)  -- 過去12か月間のデータ
   GROUP BY
       product_id;
   
   -- マテリアライズドビューにインデックスを作成
   CREATE INDEX idx_mv_cust_behavior ON mv_customer_behavior(product_id);
   CREATE INDEX idx_mv_cust_purchase ON mv_customer_behavior(last_purchase_date);
   

6. 大量データ処理用に、パーティションを持つマテリアライズドビューを作成します。

   -- プロダクトカテゴリ別にパーティション分割された売上集計マテリアライズドビューを作成
   CREATE MATERIALIZED VIEW mv_category_sales_partitioned
   PARTITION BY HASH(category_id) PARTITIONS 8
   REFRESH COMPLETE ON DEMAND
   AS
   SELECT
       p.category_id,
       TRUNC(so.order_date, 'MM') AS month,
       COUNT(DISTINCT so.order_id) AS order_count,
       SUM(so.quantity) AS total_quantity,
       SUM(so.amount) AS total_amount
   FROM
       sales_orders so
   JOIN
       products p ON so.product_id = p.product_id
   WHERE
       so.order_date >= TO_DATE('2023-01-01', 'YYYY-MM-DD')
   GROUP BY
       p.category_id, TRUNC(so.order_date, 'MM');
   

7. 分析系クエリに適した、カラムストア形式のマテリアライズドビューを作成します。

   -- 売上分析ワイドテーブルを作成（カラムストア形式）
   CREATE MATERIALIZED VIEW mv_sales_analysis_wide
   WITH COLUMN GROUP(EACH COLUMN)  -- カラムストア形式
   REFRESH COMPLETE ON DEMAND
   AS
   SELECT
       so.order_id,
       so.order_date,
       so.customer_id,
       p.product_id,
       p.product_name,
       p.category_id,
       p.unit_price,
       so.quantity,
       so.amount,
       so.quantity * p.unit_price AS calculated_amount
   FROM
       sales_orders so
   JOIN
       products p ON so.product_id = p.product_id
   WHERE
       so.order_date >= ADD_MONTHS(TRUNC(SYSDATE), -12);
   

8. マテリアライズドビューの定義を確認します。

   -- 月次売上傾向マテリアライズドビューのDDL定義を確認
   SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', 'MV_MONTHLY_SALES_TREND') FROM DUAL;
   
   -- マテリアライズドビューのリフレッシュ状態を確認
   SELECT
       mview_name,
       refresh_mode,
       refresh_method,
       last_refresh_date,
       staleness
   FROM
       user_mviews
   ORDER BY
       last_refresh_date DESC NULLS LAST;
   

関連ドキュメント

• マテリアライズドビューの概要

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

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

• マテリアライズドビューのクエリ

• マテリアライズドビューの削除