Re: MV creation error
- From: Vladimir Begun <Vladimir.Begun@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 10 Feb 2005 10:55:50 -0800
Mladen Gogala wrote:
> The fine manual tells you that you cannot use fast refresh with group
> functions.
This statement is too bold to be true, you'd find opposite in the Data
Warehouse guide. :)
"
Materialized Views with Aggregates
In data warehouses, materialized views normally contain aggregates as
shown in Example 8-1. For fast refresh to be possible, the SELECT list
must contain all of the GROUP BY columns (if present), and there must
be a COUNT(*) and a COUNT(column) on any aggregated columns. Also,
materialized view logs must be present on all tables referenced in the
query that defines the materialized view. The valid aggregate functions
are: SUM, COUNT(x), COUNT(*), AVG, VARIANCE, STDDEV, MIN, and MAX, and
the expression to be aggregated can be any SQL value expression.
See "Restrictions on Fast Refresh on Materialized Views with Aggregates".
Fast refresh for a materialized view containing joins and aggregates is
possible after any type of DML to the base tables (direct load or
conventional INSERT, UPDATE, or DELETE). It can be defined to be refreshed
ON COMMIT or ON DEMAND. A REFRESH ON COMMIT materialized view will be
refreshed automatically when a transaction that does DML to one of the
materialized view's detail tables commits. The time taken to complete the
commit may be slightly longer than usual when this method is chosen. This
is because the refresh operation is performed as part of the commit process.
Therefore, this method may not be suitable if many users are concurrently
changing the tables upon which the materialized view is based.
Here are some examples of materialized views with aggregates. Note that
materialized view logs are only created because this materialized view
will be fast refreshed.
"
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
