Skip to content
≡

Administrator

Developer

Tools

External Tools

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW#

Syntax#

create_materialized_view ::=#

create_mview

table_partitioning_clause ::=, tablespace_clause ::=, logging_clause ::=, lob_column_properties ::=, build_clause ::=, refresh_clause ::=

physical_attributes_clause ::=#

physical_attributes_clause

storage_clause ::=

build_clause ::=#

build_clause

refresh_clause ::=#

refresh_clause

Prerequisites#

At least one of the following conditions must met:

  • The SYS user
  • The CREATE MATERIALIZED VIEW system privilege are required to create a materialized view in the user's schema. The SELECT object privilege for each base table that the user does not own or the SELECT ANY TABLE system privilege is also required.
  • The CREATE ANY MATERIALIZED VIEW system privilege is required to create a materialized view in another user's schema. And the SELECT object privilege for each base table that the user does not own or the SELECT ANY TABLE system privilege as well.
  • Once the materialized view is created, an internal view and an internal table for the database are automatically created in the materialized view schema. These additionally created objects are used to maintain the materialized view data. The user requires the privilege to create such objects in order to create a Material View.

Description#

A new materialized view is created as the specified name. A materialized view is a database object that stores query results. A table, a view or another materialized view can constitute the FROM clause of the query. Such objects are defined as "base tables".

Like other tables, a materialized view stores the results of query statements in a tablespace and is mostly used for data warehouse purposes. The performance time for query execution can be significantly reduced by creating query statements that are frequently used or include time demanding JOIN or aggregate functions as materialized views.

Altibase provides only read-only materialized views.

user_name#

This specifies the owner name of the materialized view to be created. On omission, Altibase creates a materialized view in the user schema connected to the current session.

mview_name#

This specifies the name of the materialized view to be created. Refer to "Rules for Object Names" in Chapter 2 for more information on specifying names. Altibase automatically creates a table to maintain the data of the materialized view under the same name as the specified materialized view.

c_alias#

If the queried target from the base table is an expression, an alias must be specified for that expression. This alias becomes the column name for the materialized view. The number of aliases must equal the total number of queried targets(expressions and columns).

table_partitioning_clause#

Please refer to table_partitioning_clause of the CREATE TABLE statement.

segment_attributes_clause#

Please refer to segment_attributes_clause of the CREATE TABLE statement.

lob_column_properties#

Please refer to lob_column_properties of the CREATE TABLE statement.

phsical_attributes_clause#

Please refer to physical_attributes_clause of the CREATE TABLE statement.

build_clause clause#

This specifies the initial time point at which data of the materialized view is built. On omission, the default value is IMMEDIATE.

IMMEDIATE#

Data is built at the time point that the materialized view is created.

DEFERRED#

Data is built during refresh execution after the materialized view is created.

refresh_clause clause#

If the base table of the materialized view is altered, the data of the materialized view must be updated. This clause specifies the method and time that the materialized view is refreshed. On omission, the default values are FORCE and ON DEMAND.

After the REFRESH keyword, one of the following must be specified: COMPLETE, FAST, FORCE REFRESH or ON DEMAND, ON COMMIT

COMPLETE#

specifies data to be built by executing the sub query defined when the materialized view was created.

FAST#

currently not supported.

FORCE#

when refresh occurs, if possible, fast refresh is executed; if not, the database is commanded to execute complete refresh. Altibase does not currently support FAST; specifying FORCE is equivalent to COMPLETE.

ON DEMAND#

specifies refresh only on user's request.

ON COMMIT#

currently not supported.

NEVER REFRESH#

currently not supported.

Note: The user can manually request materialized view refresh by calling the REFRESH_MATERIALIZED_VIEW stored procedure. For further information on the REFRESH_MATERIALIZED_VIEW stored procedure, refer to the Chapter 10 in the Stored Procedures Manual.

subquery clause#

This specifies the query statement of the materialized view. When the user creates a materialized view, the subquery specified in the clause is executed and its result is stored in the materialized view.

Example#

<Query> Create a materialized view of the name mv1 that takes the employees table as its base table. Since the build clause and refresh clause are not specified, refresh is possible only on the user's request and complete refresh is executed.

CREATE MATERIALIZED VIEW mv1 AS
SELECT * FROM employees;