CREATE MATERIALIZED VIEW

Purpose

This statement is used to create a materialized view.

A materialized view is a special type of database object that stores a copy of the query results and is periodically refreshed (or manually refreshed) to keep the data up to date. Materialized views can include operations such as aggregations, joins, and subqueries, and they can be indexed and partitioned to further improve performance.

Privilege requirements

To create a materialized view, you must have the CREATE TABLE privilege. For more information about the privileges of OceanBase Database, see Privilege classification in Oracle mode.

Syntax

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)

Parameters

mv_column_group_option

• WITH COLUMN GROUP(all columns): specifies to create a materialized view in rowstore format.

• WITH COLUMN GROUP(each column): specifies to create a materialized view in columnstore format.

• WITH COLUMN GROUP(all columns, each column): specifies to create a materialized view in rowstore-columnstore redundant format.

refresh_clause

• REFRESH [refresh_option [nested_refresh_option]] [ON DEMAND | COMMIT | STATEMENT] [[START WITH expr] [NEXT expr]]: specifies the refresh method of the materialized view.

  • refresh_option: specifies the refresh method of the materialized view. This parameter is optional. If you do not specify a refresh method, the default value is FORCE. Valid values:

    • COMPLETE: specifies to perform a complete refresh. That is, the entire data of the materialized view is recalculated to ensure that the data in the view is consistent with that in the source table.

    • FAST: specifies to perform an incremental refresh. That is, only the data that has changed in the source table is refreshed to avoid recalculating the entire view.

      Notice

      • When you use the REFRESH FAST method, the records in the materialized view log are used to determine the data to be refreshed. Therefore, you must create a materialized view log (mlog) for the base table before you create the materialized view.
      • The columns used in the incremental refresh of the materialized view must be included in the mlog.

      For more information about how to create a materialized view log, see Create a materialized view log.

    • FORCE: the default value. Specifies to perform a hybrid refresh. First, an incremental refresh is attempted. If the incremental refresh fails, a complete refresh is performed.

    • nested_refresh_option: specifies the refresh strategy of the nested materialized view. This parameter is optional. If you do not specify a refresh strategy, the default value is INDIVIDUAL. Valid values:

      • INDIVIDUAL: the default value. Specifies to perform an independent refresh.
      • INCONSISTENT: specifies to perform an inconsistent cascade refresh.
      • CONSISTENT: specifies to perform a consistent cascade refresh.

      Note

      • Starting from OceanBase Database V4.3.5 BP3, you can specify the nested_refresh_option parameter to set the refresh strategy of the nested materialized view.
      • For non-nested materialized views, cascade refresh is not supported. Therefore, the specified refresh strategy is ineffective and the default value, `INDIVIDUAL`, is used. The three specified refresh strategies only take effect in background tasks. When you manually use the PL package (DBMS_MVIEW.REFRESH) to schedule a refresh, the refresh is performed based on the specified PL parameters.

  • [ON DEMAND | COMMIT | STATEMENT]: specifies the refresh timing of the materialized view.

    • ON DEMAND: specifies to perform an on-demand refresh. You can manually perform a refresh by using the DBMS_MVIEW.REFRESH procedure or set an automatic refresh plan by using the START WITH and NEXT clauses.
    • COMMIT: specifies to perform an automatic refresh when a transaction on the base table is committed.
    • STATEMENT: specifies to perform an automatic refresh when a transaction containing a DML statement on the base table is committed.

  • [START WITH expr]: optional. Specifies the initial time at which the materialized view is automatically refreshed.

  • [NEXT expr]: optional. Specifies the time interval between automatic refreshes of the materialized view.

• NEVER REFRESH: specifies that the materialized view does not need to be refreshed. That is, the materialized view is only refreshed when it is created and cannot be refreshed again after it is created.

query_rewrite_clause

• [ENABLE | DISABLE] QUERY REWRITE: specifies whether to enable query rewrite.
  • ENABLE QUERY REWRITE: enables query rewrite, allowing the optimizer to rewrite queries using the materialized view.
  • DISABLE QUERY REWRITE: the default value. Disables query rewrite.

For more information about materialized view query rewriting, see Query rewriting of materialized views.

on_query_computation_clause

• DISABLE ON QUERY COMPUTATION: the default value, which specifies to create a regular materialized view.

• ENABLE ON QUERY COMPUTATION: specifies to create a real-time materialized view.

  Notice

  • Materialized views that use the MIN or MAX functions do not support real-time materialized views.
  • Aggregated materialized views with outer joins do not support real-time materialized views.
  • Materialized views with set queries do not support real-time materialized views.
  • Nested materialized views cannot be created as real-time materialized views.

For more information about real-time materialized views, see the Create a real-time materialized view section in Create a materialized view.

Examples

1. Create the base tables for sales.

   -- Create the products table
   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 the sales orders table
   CREATE TABLE sales_orders (
       order_id       NUMBER PRIMARY KEY,
       product_id     NUMBER,
       quantity       NUMBER,
       amount         NUMBER(10,2),
       order_date     DATE DEFAULT SYSDATE
   );
   
   -- Create the customers table
   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 a materialized view that is never refreshed (for static reports).

   -- Create a materialized view for product category sales (never refreshed)
   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 a materialized view that is refreshed on demand (for periodically updated analytical reports).

   -- Create a materialized view for monthly sales trend analysis
   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)  -- Data for the last 12 months
   GROUP BY
       TO_CHAR(TRUNC(order_date, 'MM'), 'YYYY-MM'),
       p.category_id
   ORDER BY
       month, p.category_id;
   

4. Create a materialized view with an automatic refresh schedule (for real-time monitoring).

   -- Create a materialized view for the best-selling products (refreshed at 2:00 AM daily)
   CREATE MATERIALIZED VIEW mv_hot_products
   REFRESH COMPLETE
   START WITH TRUNC(SYSDATE) + 1 + 2/24  -- 2:00 AM tomorrow
   NEXT TRUNC(SYSDATE) + 1 + 2/24        -- Refreshed at 2:00 AM every day
   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 indexes on the materialized views (to improve query performance).

   -- Create a materialized view for customer behavior analysis
   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)  -- Data for the last 12 months
   GROUP BY
       product_id;
   
   -- Create indexes on the materialized view
   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 a partitioned materialized view (to handle large volumes of data).

   -- Create a partitioned sales summary materialized view by product category
   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 a materialized view in columnar format (suitable for analytical queries).

   -- Create a wide sales analysis table in columnar format
   CREATE MATERIALIZED VIEW mv_sales_analysis_wide
   WITH COLUMN GROUP(EACH COLUMN)  -- Columnar format
   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. View the materialized view definition.

   -- View the DDL definition of the monthly sales trend materialized view
   SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', 'MV_MONTHLY_SALES_TREND') FROM DUAL;
   
   -- View the refresh status of the materialized view
   SELECT
       mview_name,
       refresh_mode,
       refresh_method,
       last_refresh_date,
       staleness
   FROM
       user_mviews
   ORDER BY
       last_refresh_date DESC NULLS LAST;
   

References

• Overview of materialized views

• Create materialized views

• Refresh materialized views

• Query materialized views

• Delete materialized views