DB2 UDB's High-Function Business Intelligence in e-business

242 High-Function Business Intelligence in e-business

Materialized view main syntax elements

Figure C-1 describes the main syntax elements for creating and refreshing

materialized views.

Figure C-1 Main syntax elements of materialized views

When DEFINITION ONLY is specified, any valid fullselect that does not

reference a typed table or typed view can be specified.The query is used only to

define the table. Such a table is

not a materialized view. Therefore, the

REFRESH TABLE statement cannot be used.

Appendix C. Materialized view syntax elements 243

With the DATA INITIALLY DEFERRED option, data is not inserted into the table

as part of the CREATE TABLE statement. The materialized view has to be

populated using the SET INTEGRITY command or a REFRESH TABLE

statement, or some other user determined mechanisms depending upon whether

the materialized view is system maintained or user maintained.

The ENABLE QUERY OPTIMIZATION parameter allows the materialized view to

be used for query optimization.

The DISABLE QUERY OPTIMIZATION ensures that the materialized view is not

used for query optimization, however, it can still be directly queried

The MAINTAINED BY SYSTEM option indicates that the data in the materialized

view is maintained by the system and it is the default.

The MAINTAINED BY USER option indicates that the materialized view is

maintained by the user. The user is allowed to perform update, delete, or insert

operations against the user-maintained materialized view. The REFRESH

TABLE statement, used for system-maintained materialized views can

not be

invoked against user-maintained materialized views. Only a REFRESH

DEFERRED materialized view can be defined as MAINTAINED BY USER.

Figure C-2 shows the syntax of the REFRESH statement that refreshes the data

in a materialized view.

Figure C-2 REFRESH TABLE statement

These are the options for the REFRESH statement:

򐂰 The INCREMENTAL option specifies an incremental refresh for the table by

considering only the appended portion (if any) of its base tables, or the

content of an associated staging table (if one exists, and its contents are

consistent).

򐂰 The NOT INCREMENTAL option specifies a full refresh for the table by

recomputing the materialized view definition.

244 High-Function Business Intelligence in e-business