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
򐂰 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

Get DB2 UDB's High-Function Business Intelligence in e-business now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.