CREATE MATERIALIZED VIEW

Description

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 (manually or automatically) to keep the data up to date. Materialized views can include operations such as aggregations, joins, and subqueries, and 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 seekdb, see Privilege classification.

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]] [mv_refresh_on_clause]
    | NEVER REFRESH

refresh_option:
    COMPLETE
    | FAST
    | FORCE

nested_refresh_option:
    INDIVIDUAL
    | INCONSISTENT
    | CONSISTENT

mv_refresh_on_clause:
    [ON DEMAND] [[START WITH expr] [NEXT expr]]

query_rewrite_clause:
    DISABLE QUERY REWRITE
    | ENABLE QUERY REWRITE

on_query_computation_clause:
    DISABLE ON QUERY COMPUTATION
    | ENABLE 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

info

You cannot directly create an index in the statement for creating a materialized view. You can use the CREATE INDEX statement or the ALTER TABLE statement to create an index for a materialized view.

Parameter	Description
view_name	The name of the materialized view to be created.
column_list	Optional. The list of columns of the materialized view. If you want to specify the names of the view columns, you can use the column_list clause and separate the column names with commas. Notice The view must have unique column names, just like the base table. By default, the column names in the view are the same as those in the `SELECT` statement. The number of column names specified in `column_list` must be the same as the number of columns retrieved in the `SELECT` statement.
column_name	The name of the materialized view column. By default, the column names retrieved by the `SELECT` statement are used as the view column names.
PRIMARY KEY	The primary key of the materialized view. Notice If you do not specify the column names of the materialized view, the column names in the primary key must be the same as those retrieved by the `SELECT` statement. If you specify the column names of the materialized view, separate the column names from the `PRIMARY KEY` definition with an English comma.
table_option_list	Optional. The table options of the materialized view. Like a regular table, a materialized view can have its own table options. For more information, see CREATE TABLE.
partition_option	Optional. The partition options of the materialized view. Like a regular table, a materialized view can have its own partition options. For more information, see CREATE TABLE.
mv_column_group_option	Optional. The storage format of the materialized view. If this parameter is not specified, a row-based materialized view is created by default. For more information, see mv_column_group_option.
refresh_clause	Optional. The refresh mode of the materialized view. For more information, see refresh_clause.
mv_refresh_on_clause	Optional. The refresh timing of the materialized view. For more information, see mv_refresh_on_clause.
query_rewrite_clause	Optional. Specifies whether to enable automatic query rewriting for the materialized view. For more information, see query_rewrite_clause.
on_query_computation_clause	Optional. Specifies whether the materialized view is a real-time materialized view. For more information, see on_query_computation_clause.
view_select_stmt	The `SELECT` statement used to define the data of the materialized view. This statement is used to retrieve data from the base table and store the results in the materialized view. The syntax of `view_select_stmt` is the same as that of a regular `SELECT` statement. For more information, see SELECT statement. Notice seekdb supports creating full-refresh materialized views based on regular views and external tables. seekdb supports adding the `AS OF PROCTIME()` clause to the base table when creating a materialized view. If the `AS OF PROCTIME()` clause is used outside the base table of the materialized view, an error is returned. The `AS OF PROCTIME()` clause is used to specify that the materialized view should skip refreshing this table during incremental refreshes. Additionally, the `AS OF PROCTIME()` table does not require an mlog to be created. seekdb supports using regular views declared as dimension tables (`AS OF PROCTIME()`) as the base tables for incremental refresh materialized views.

mv_column_group_option

WITH COLUMN GROUP(all columns)：specifies to create a materialized view in row store format.
WITH COLUMN GROUP(each column)：specifies to create a materialized view in column store format.
WITH COLUMN GROUP(all columns, each column)：specifies to create a materialized view in row and column store redundant format.

info

You can use the SHOW CREATE TABLE view_name; or SHOW CREATE VIEW view_name; command to view the definition of a materialized view and determine its storage format.

refresh_clause

REFRESH [refresh_option [nested_refresh_option]] [mv_refresh_on_clause]：specifies the refresh method of a materialized view.
- refresh_option：specifies the refresh method of a materialized view. This option 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 completely consistent with that in the source table.
  - FAST：specifies to perform an incremental refresh. That is, only data related to changes in the source table is refreshed, and the entire view is not completely recalculated.
    tip
    The REFRESH FAST method uses the records in the materialized view log to determine the data to be incrementally refreshed. Therefore, when you perform an incremental refresh on a materialized view, you must create a materialized view log (mlog) for the base table before you create the materialized view.
    
    All columns used in the incremental refresh of a materialized view must be in the mlog. For information about how to create a materialized view log, see Materialized View Log.
  - FORCE：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 a nested materialized view. This option is optional. If you do not specify a refresh strategy, the default value is INDIVIDUAL. Valid values:
    - INDIVIDUAL：default value. Specifies to perform an independent refresh.
    - INCONSISTENT：specifies to perform a cascading inconsistent refresh.
    - CONSISTENT：specifies to perform a cascading consistent refresh.
    info
    seekdb supports the nested_refresh_option parameter, which specifies the refresh strategy of a nested materialized view.
    
    For a non-nested materialized view, no cascading refresh is performed regardless of the specified refresh strategy. The specified three 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 according to the specified PL parameters.
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.

mv_refresh_on_clause

info

In addition to using the mv_refresh_on_clause clause to define a refresh plan, you can manually refresh a materialized view by calling the dbms_mviews.refresh stored procedure.

ON DEMAND：optional. Specifies to perform an on-demand refresh.
[[START WITH expr] [NEXT expr]]：optional. Specifies the refresh interval.
- [START WITH expr]：optional. Specifies the start time of the refresh plan.
- [NEXT expr]：optional. Specifies the next refresh time.
  tip
  - If the NEXT clause is used, the time expression of the refresh plan must be set to a future time. Otherwise, an error is returned.
  - If you expect a materialized view to be periodically automatically scheduled for refresh, set the scheduling cycle by defining the NEXT clause.

We recommend that you use sysdate() to represent the current time in the current time zone. Here is an example of a time expression:

START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY

The preceding clause specifies to refresh the materialized view every 1 day starting from the current time (sysdate()).

query_rewrite_clause

tip

This feature applies only to materialized views that contain only the SELECT JOIN and WHERE clauses, that is, SPJ queries. Materialized views that do not meet the conditions will not be used for rewriting, but no error will be returned.

DISABLE QUERY REWRITE：default value. Specifies to disable automatic rewriting for the current materialized view.
ENABLE QUERY REWRITE：specifies to enable automatic rewriting for the current materialized view.

For more information about materialized view rewriting, see Materialized View Query Rewriting.

on_query_computation_clause

DISABLE ON QUERY COMPUTATION：default value. Specifies to create a regular materialized view.
ENABLE ON QUERY COMPUTATION：specifies to create a real-time materialized view.

For more information about real-time materialized views, see the Create a Real-Time Materialized View section in Create a Materialized View.

Example

Create a table named test_tbl1.

CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT, col4 INT);

Create a materialized view named mv_test_tbl1. Hash partition the materialized view by the col1 column and divide it into 8 partitions. Use the complete refresh method to refresh the materialized view, starting from the current date and refreshing it every 1 day. Query records from the test_tbl1 table where col3 >= 30 and use the query results as the data for the materialized view.
```
CREATE MATERIALIZED VIEW mv_test_tbl1
  PARTITION BY HASH(col1) PARTITIONS 8
  REFRESH COMPLETE START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY
  AS SELECT col1, col2, col3
    FROM test_tbl1
    WHERE col3 >= 30;
```

Create a materialized view named mv2_test_tbl1 and specify the primary key.

CREATE MATERIALIZED VIEW mv2_test_tbl1(c1, c2, c3, PRIMARY KEY(c1))
      AS SELECT col1, col2, col3
        FROM test_tbl1;

Create a columnar materialized view named mv3_test_tbl1.

CREATE MATERIALIZED VIEW mv3_test_tbl1
  WITH COLUMN GROUP(each column)
  AS SELECT col1, col2, col3
    FROM test_tbl1;

View the definition of the materialized view mv3_test_tbl1.
```
SHOW CREATE VIEW mv3_test_tbl1;
```

CREATE MATERIALIZED VIEW

Description

Privilege requirements

Syntax

Parameters

mv_column_group_option

refresh_clause

mv_refresh_on_clause

query_rewrite_clause

on_query_computation_clause

Example

References

Contents

Description​

Privilege requirements​

Syntax​

Parameters​

mv_column_group_option​

refresh_clause​

mv_refresh_on_clause​

query_rewrite_clause​

on_query_computation_clause​

Example​

References​

Contents

Description

Privilege requirements

Syntax

Parameters

mv_column_group_option

refresh_clause

mv_refresh_on_clause

query_rewrite_clause

on_query_computation_clause

Example

References