CREATE MATERIALIZED VIEW

Purpose

You can use this statement to create a materialized view.

A materialized view is a special database object that stores the latest copy of query results and is periodically or manually refreshed. Materialized views can contain operations such as aggregation and join operations, and subqueries, and can be indexed and partitioned to further enhance the query performance.

Required privileges

You have the CREATE TABLE privilege. For more information about privileges in OceanBase Database, see Privilege types 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 rowstore materialized view.

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

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

refresh_clause

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

  • refresh_option: the refresh method of the materialized view. If you do not specify a refresh method, FORCE is used by default. Valid values:

    • COMPLETE: specifies to perform a complete refresh. In this method, OceanBase Database recalculates the data of the entire materialized view to ensure data consistency with the base table.

    • FAST: specifies to perform an incremental refresh. In this method, OceanBase Database refreshes only the data related to changes in the base table.

      Notice

      This method determines the content for an incremental refresh based on the records in the materialized view log. Therefore, to support incremental refreshes of a materialized view, you need to create a materialized view log for the base table before you create the materialized view. For information about how to create a materialized view log, see Create a materialized view log.

    • FORCE: specifies to perform a hybrid refresh. In this method, OceanBase Database performs an incremental refresh first. If the incremental refresh fails, OceanBase Database performs a complete refresh. The hybrid refresh method is used by default.

    • nested_refresh_option: optional. Specifies the refresh policy for nested materialized views. If not specified, the default is INDIVIDUAL. Valid values:

      • INDIVIDUAL: default value, indicates independent refresh.
      • INCONSISTENT: indicates cascaded inconsistent refresh.
      • CONSISTENT: indicates cascaded consistent refresh.

      Note

      • For OceanBase Database V4.3.5, starting from V4.3.5 BP3, the nested_refresh_option parameter (which specifies the refresh policy for nested materialized views) is supported.
      • For non-nested materialized views, there is no cascaded refresh behavior. Regardless of the specified refresh policy, it has no effect and independent refresh is always used by default. The three refresh policies only take effect for background tasks. When manually refreshing using the PL package (DBMS_MVIEW.REFRESH), the refresh is performed according to the specified PL parameters.

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

    • ON DEMAND: refresh on demand. You need to manually refresh using the DBMS_MVIEW.REFRESH procedure or set up an automatic refresh schedule with the START WITH/NEXT clauses.
    • COMMIT: the materialized view is automatically refreshed when a transaction is committed on the base table.
    • STATEMENT: the materialized view is automatically refreshed when a transaction containing DML statements on the base table is committed.

  • [START WITH expr]: optional. Specifies the time for the first automatic refresh of the materialized view.

  • [NEXT expr]: optional. Specifies the interval for the automatic refresh of the materialized view.

• NEVER REFRESH: specifies to disable refresh for the materialized view. If you specify the NEVER REFRESH clause, the materialized view is refreshed only when it is created, and will not be refreshed again.

query_rewrite_clause

• [ENABLE | DISABLE] QUERY REWRITE: specifies whether to enable query rewrite functionality.
  • ENABLE QUERY REWRITE: enables query rewrite, allowing the optimizer to use the materialized view to rewrite queries.
  • DISABLE QUERY REWRITE: disables query rewrite. This is the default value.

For more information about materialized view query rewrite, see Materialized view query rewrite.

on_query_computation_clause

• [ENABLE | DISABLE] ON QUERY COMPUTATION: specifies whether to enable query computation functionality.
  • ENABLE ON QUERY COMPUTATION: enables query computation and marks the materialized view as a real-time materialized view.
  • DISABLE ON QUERY COMPUTATION: disables query computation and creates a regular materialized view. This is the default value.

• DISABLE ON QUERY COMPUTATION: creates a standard materialized view. This is the default value.
• ENABLE ON QUERY COMPUTATION: creates a real-time materialized view.

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

Examples

1. Create the basic tables related to 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 without automatic refresh (for static reports).

   -- Create a product category sales summary report (no automatic refresh)
   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 an on-demand refresh materialized view (for periodically updated analytical reports).

   -- Create a monthly sales trend analysis materialized view
   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 from 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 hot products ranking (automatically refreshed every day at 2 AM)
   CREATE MATERIALIZED VIEW mv_hot_products 
   REFRESH COMPLETE 
   START WITH TRUNC(SYSDATE) + 1 + 2/24  -- 2 AM tomorrow
   NEXT TRUNC(SYSDATE) + 1 + 2/24        -- Then refresh every day at 2 AM
   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 a materialized view (to improve query performance).

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

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

   -- Create a sales analysis wide table (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 materialized view definitions.

   -- 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 materialized views
   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 a materialized view

• Refresh a materialized view

• Query a materialized view

• Drop a materialized view