Chapter 2. DB2 UDB’s materialized views 93
? Functions that depend on physical characteristics (for example
DBPARTITIONNUM, HASHEDVALUE).
? Table or view references to system objects (catalog tables). EXPLAIN tables
should also not be specified.
? Expressions that are a structured type or LOB type (or a distinct type based
on a LOB type).
? When REPLICATED is specified, the following restrictions apply:
– GROUP BY clause is not allowed.
– The materialized view query must only reference a single table, that is, not
include a join.
2.11.2 REFRESH IMMEDIATE and queries with staging table
The following limitations only apply to REFRESH IMMEDIATE materialized
views, and queries used to create REFRESH DEFERRED tables associated with
a staging table.
General restrictions
? The fullselect must be a subselect, with the exception that UNION ALL is
supported in the input table expression of a GROUP BY.
? The subselect can
not include:
– References to a nickname
– Functions that are non-deterministic
– Scalar fullselects
– Predicates with fullselects
– Special registers like CURRENT TIMESTAMP
– SELECT DISTINCT
? If the FROM clause references more than one table or view, it can only define
an inner join without using the explicit INNER JOIN syntax.
? GROUP BY restrictions
When a GROUP BY clause is specified, the following considerations apply:
– Column functions SUM, COUNT, COUNT_BIG and GROUPING (without
DISTINCT) are supported. The select list must contain a COUNT(*) or
COUNT_BIG(*) column. If the materialized view select list contains
SUM(X) where X is a nullable argument, then the materialized view must
also have COUNT(X) in its select list. These column functions can not be
part of any expressions.
– A HAVING clause is not allowed.
94 High-Function Business Intelligence in e-business
– If in a multiple partition database partition group, the partitioning key must
be a subset of the GROUP BY items.
No duplicate rows in materialized view restriction
The materialized view must not contain duplicate rows, and the following
restrictions specific to this uniqueness requirement apply, depending upon
whether or not a GROUP BY clause is specified.
? When a GROUP BY is specified, the following uniqueness related restrictions
apply:
– All GROUP BY items must be included in the select list.
– When the GROUP BY contains GROUPING SETS, CUBE or ROLLUP,
then the GROUP BY items and associated GROUPING column functions
in the select list must form a unique key of the result set. Thus, the
following restrictions must be satisfied:
• No grouping sets may be repeated. For example, ROLLUP(X,Y),X is
not allowed because it is equivalent to GROUPING
SETS((X,Y),(X),(X))
• If X is a nullable GROUP BY item that appears within GROUPING
SETS, CUBE, or ROLLUP, then GROUPING(X) must appear in the
select list
? When a GROUP BY clause is
not specified, the following uniqueness related
restrictions apply:
– The materialized view’s non-duplicate requirement is achieved by deriving
a unique key for the materialized view from one of the unique key
constraints defined in each of the underlying tables. Therefore, the
underlying tables must have at least one unique key constraint defined on
them, and the columns of these keys must appear in the select list of the
materialized view definition.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.