CREATE MV

Syntax

CREATE {MATERIALIZED VIEW  | MV} mv-name [column-name-list]
{ REFRESH refresh-type }{ initialization-type } [file-options]
AS query-expr

mv-name is:
[catalog-name.][schema-name.]mv-name

column-name-list is:
		(column-name [, column-name]...)

refresh-type is:
		ON STATEMENT

initialization-type is:
		INITIALIZE ON CREATE

file-options is:
		STORE BY store-option
		| LOCATION [\node.]$volume[.subvolume.file-name]
		[NAME partition-name]
		| partn-file-option
		| ATTRIBUTE[S] attribute [,attribute]...

store-option is:
		(key-column-list)

partn-file-option is:
		{ [RANGE] PARTITION
		[BY (partitioning-column [,partitioning-column]...)]
		[(ADD range-partn-defn [,ADD range-partn-defn]...)]
		| HASH PARTITION
		[BY (partitioning-column [,partitioning-column]...)]
		[(ADD partn-defn [,ADD partn-defn]...)]}


range-partn-defn is:
			FIRST KEY {col-value | (col-value [,col-value ]...)}
			partn-defn

partn-defn is:
		LOCATION [\node.]$volume[.subvolume.file-name]
  [NAME partition-name]
		[EXTENT ext-size | (pri-ext-size [,sec-ext-size])]
		[MAXEXTENTS num-extents]
	
attribute is:
	 {AUDITCOMPRESS | NO AUDITCOMPRESS}
		| BLOCKSIZE number-bytes
		| {CLEARONPURGE | NO CLEARONPURGE}
		| EXTENT ext-size | (pri-ext-size [,sec-ext-size])
		| MAXEXTENTS num-extents

query-expr is:
		SELECT column-expr [, column-expr]...
		FROM table-ref [, table-ref] ...
		[WHERE search-condition]

table-ref is:
		simple-table | joined-table

simple-table is:
		{base-table-name}
		[[AS] corr-name [(col-expr-list)]]

joined-table is: 
		table-ref [NATURAL] [INNER] JOIN table-ref [join-spec]

join-spec is:
		ON search-condition

column-expr is:
		{non-aggregate-column-expr [[AS] derived-name]

Description

This statement is used to create MVs on SQL/MX tables. MVs are database objects that store the results of a query, just as a regular table. The MV can be queried directly without querying the base table.

Options

mv-name
Specifies the ANSI logical name of the MV. mv-name is in the [catalog-name.][schema-name.]mv-name form, where each part of the name is a valid SQL identifier. It can have maximum 128 characters. The mv-name must be unique within the schema.
column-name-list

Specifies the names of the columns in the MV. The column names in the list correspond to the columns in the query-expr .

refresh-type

Specifies how and when the MV must get refreshed.

ON STATEMENT

When the refresh-type is ON STATEMENT, MV is refreshed immediately upon updating the base tables. The MV is maintained automatically as part of the statement that updates the base tables. Therefore, once initialized, MVs are always consistent with the base tables through INSERT, UPDATE, or DELETE statements.

When the refresh-type is ON STATEMENT, MV updates require simultaneous updates to the corresponding MVs.

initialization-type

Specifies when the MV gets its initial content from the base tables.

INITIALIZE ON CREATE

Specifies that the MV is populated when it is created.

STORE BY store-option

Specifies the set of columns on which the clustering key of MV is based. The clustering key determines the order of rows within the physical file that holds the MV.

If the STORE BY clause is not specified, SQL/MX determines the storage order of base tables and primary keys.

For search-condition and column-expr description, see the SQL/MX 3.4 Reference Manual.

Permissions

To create an MV, you must own the schema or be the super ID, or have CREATE permission on the schema where the MV must be created. You must have SELECT privileges on all the underlying tables and views.

Considerations

  • MV definition can have joins of two or more tables, but only inner equi-joins are allowed.
  • If an expression or function in the query-expr does not have an implicit name, then an explicit name must be specified using the AS clause in the SELECT list or the column-name-list must be specified. Otherwise, an error is returned.

  • If two or more columns in the projection list of the join query have the same name, then either explicit unique alias names must be given to those columns or the column-name-list must be specified. Otherwise, an error is returned.

  • HPE does not recommend creating an MV on a table with referential integrity constraint.

  • Only SELECT privilege can be granted on an MV.

  • If the MV definition query contains *, the existing columns of the underlying tables are included in the MV definition.

NOTE:

Additional columns and indexes may be added by SQL/MX to the MV at creation time. SHOWDDL displays these indexes and columns. When creating MV, warning 12112 indicates creation of additional indexes.

Limitations

  • Aggregate functions cannot be specified in the queries of the MV definition.

  • The MV definition queries cannot have DISTINCT, GROUP BY, HAVING, or ORDER BY clauses.

  • The MV cannot have UNION clause.

  • Subqueries are not allowed in MVs.

  • MV definition cannot have Sequence Generator pseudo columns.

  • MV definition cannot have IDENTITY column of a base table in the projection list of query.

  • MV cannot be defined on a table with dependent triggers. A trigger cannot be created on a table with an MV.

  • Index, triggers, views, and other MVs cannot be created on MVs. An MV column cannot be referenced in a referential integrity constraint.

  • Embedded INSERT, UPDATE, and DELETE operations are not allowed on tables having dependent MVs.

  • PROTOTYPE clause and mxrpm tool do not support similarity checks on MVs.

  • Self-referencing updates of clustering key of a table is not allowed when a MV exists on the table.

  • Rows cannot be updated, or inserted into, or deleted from an updatable view if the base table has dependent MV.

Example command

The following statement creates an MV, hr_details: 
create table employee (e_empid int not null, empname varchar (50) not null, address varchar (100) not null, contact varchar (15), primary key (e_empid));
create table salary (s_empid int not null, sal numeric (18, 6), dept varchar (20), primary key (s_empid));
create materialized view hr_details 
refresh on statement 
initialize on create 
as 
select empname, address, sal from employee join salary on e_empid = s_empid
where dept = 'HR';