Doc ID: Note:222843.1 Type: BULLETIN
Last Revision Date: 04-DEC-2003 Status: PUBLISHED

PURPOSE
-------
This note describes 9i Local Aggregate Materialized View Fast Refresh Restrictions.


RELATED DOCUMENTS
-----------------
Oracle9i Data Warehousing Guide
OSS339642 Conventional DML Ignored by Local Aggregate Materialized View Fast RefreshNote 222463.1 MATERIALIZED VIEW AGGREGATE FAST REFRESH


GENERAL RESTRICTIONS ON FAST REFRESH
------------------------------------
The defining query of the materialized view is restricted as follows:
The materialized view must:
Not contain references to non-repeating expressions like SYSDATE and ROWNUM.
Not contain references to RAW or LONG RAW data types.


RESTRICTIONS ON FAST REFRESH ON MATERIALIZED VIEWS WITHOUT AGGREGATES
---------------------------------------------------------------------
Defining queries for materialized views with joins only and no aggregates
have these restrictions on fast refresh:

All restrictions from the above "General Restrictions on Fast Refresh".

They cannot have GROUP BY clauses or aggregates.

If the WHERE clause of the query contains outer joins, then unique constraints
must exist on the join columns of the inner join table.

If there are no outer joins, you can have arbitrary selections and joins in the
WHERE clause. However, if there are outer joins, the WHERE clause cannot have
any selections. Furthermore, if there are outer joins, all the joins must be
connected by ANDs and must use the equality (=) operator.

Rowids of all the tables in the FROM list must appear in the SELECT list of the query.

Materialized view logs must exist with rowids for all the base tables in the
FROM list of the query.


RESTRICTIONS ON FAST REFRESH MATERIALIZED VIEWS AGGREGATES
----------------------------------------------------------
Defining queries for materialized views with joins and aggregates
have these restrictions on fast refresh:

All restrictions from the above "General Restrictions on Fast Refresh".

Fast refresh is supported for both ON COMMIT and ON DEMAND materialized views,
however the following restrictions apply:

1. All tables in the materialized view must have materialized view logs,
and the materialized view logs must:
Contain all columns from the table referenced in the materialized view.
Specify with ROWID and INCLUDING NEW VALUES.
The SEQUENCE clause is required when the materialized view log is defined
in order to support fast refresh after UPDATE.

2. Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for
fast refresh.

3. COUNT(*) must be specified.

4. For each aggregate AGG(expr), the corresponding COUNT(expr) must be
present.

5. If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and
SUM(expr) must be specified. Oracle recommends that SUM(expr *expr)
be specified.

6. The SELECT list must contain all GROUP BY columns.

7. If the materialized view has one of the following, then fast refresh
is supported on conventional DML inserts or direct loads or a combination
of both but not not on deletes or updates.

Materialized views with MIN or MAX aggregates
Materialized views which have SUM(expr) but no COUNT(expr)
Materialized views without COUNT(*)

8. The COMPATIBILITY parameter must be set to 9.0 if the materialized
aggregate view has inline views, outer joins, self joins or grouping sets
and FAST REFRESH is specified during creation. Note that all other
requirements for fast refresh specified above must also be satisfied.

9. Materialized views with named views or subqueries in the FROM clause
can be fast refreshed provided the views can be completely merged.
For information on which views will merge, refer to the
Oracle9i Database Performance Guide and Reference.

10. Materialized aggregate views with self joins (that is, multiple
instances of the same table in the defining query) are fast refreshable
after conventional DML and direct loads. The two tables will be treated
as if they were separate tables.

11. If there are no outer joins, you may have arbitrary selections and
joins in the WHERE clause.

12. Materialized aggregate views with outer joins are fast refreshable after
conventional DML and direct loads, provided ONLY the outer table has been
modified. Also, unique constraints must exist on the join columns of the
inner join table. If there are outer joins, all the joins must be connected
by ANDs and must use the equality (=) operator.

13. For materialized views with CUBE, ROLLUP, Grouping Sets, or concatenation
of them, the following restrictions apply:

A. The SELECT list should contain grouping distinguisher that can either
be a GROUPING_ID function on all GROUP BY expressions or GROUPING
functions one for each GROUP BY expression. For example, if the GROUP BY
clause of the materialized view is "GROUP BY CUBE(a, b)", then the
SELECT list should contain either "GROUPING_ID(a, b)" or
"GROUPING(a) AND GROUPING(b)" for the materialized view to be fast
refreshable.

B. GROUP BY should not result in any duplicate groupings. For example,
"GROUP BY a, ROLLUP(b, a)" is not fast refreshable because it results
in duplicate groupings "(a, b), (a, b), AND (a)".