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（指定嵌套物化视图的刷新策略）。
      • 对于非嵌套物化视图来说，不存在级联刷新行为，无论指定为何种刷新策略均无意义，都默认独立刷新。指定的三种刷新策略只在后台任务生效，当手动使用 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;
   

相关文档

• 物化视图概述

• 创建物化视图

• 刷新物化视图

• 查询物化视图

• 删除物化视图