Materialized view in oracle with Fast Refresh instead of complete dosn't work

Question

I have created a materialized view with Refresh complete like that and it works well:

CREATE MATERIALIZED VIEW VM4 
   Build immediate 
   refresh complete on commit 
AS 
select C.codecomp, 
    count(c.numpolice) as NbContrat, 
    SUM(c.montant) as MontantGlobal 
from contrat C  
group by c.codecomp;

Now I would like to create a similar view but with Refresh Fast but it dosn't work and it shows me this error: error

Knowing that I have already created the LOG of the Contrat table ilke that:

CREATE MATERIALIZED VIEW LOG ON contrat with rowid ;

Answer 1

Check documentation:

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.

• The materialized view must not contain references to RAW or LONG RAW data types.

• It cannot contain a SELECT list subquery.

• It cannot contain analytic functions (for example, RANK) in the SELECT clause.

• It cannot reference a table on which an XMLIndex index is defined.

• It cannot contain a MODEL clause.

• It cannot contain a HAVING clause with a subquery.

• It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.

• It cannot contain a [START WITH …] CONNECT BY clause.

• It cannot contain multiple detail tables at different sites.

• ON COMMIT materialized views cannot have remote detail tables.

• Nested materialized views must have a join or aggregate.

• Materialized join views and materialized aggregate views with a GROUP BY clause cannot select from an index-organized table.

Restrictions on Fast Refresh on Materialized Views with Aggregates

Defining queries for materialized views with aggregates or joins have the following restrictions on fast refresh:

• All restrictions from "General Restrictions on Fast Refresh".

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

• 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.

    • Specify the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.

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

• COUNT(*) must be specified.

• Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are not allowed.

• For each aggregate such as AVG(expr), the corresponding COUNT(expr) must be present. Oracle recommends that SUM(expr) be specified. See Requirements for Using Materialized Views with Aggregates for further details.

• If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified. See Requirements for Using Materialized Views with Aggregates for further details.

• The SELECT column in the defining query cannot be a complex expression with columns from multiple base tables. A possible workaround to this is to use a nested materialized view.

• The SELECT list must contain all GROUP BY columns.

• The materialized view is not based on one or more remote tables.

• If you use a CHAR data type in the filter columns of a materialized view log, the character sets of the master site and the materialized view must be the same.

• If the materialized view has one of the following, then fast refresh is supported only on conventional DML inserts and direct loads.

  • Materialized views with MIN or MAX aggregates

  • Materialized views which have SUM(expr) but no COUNT(expr)

  • Materialized views without COUNT(*)

  Such a materialized view is called an insert-only materialized view.

• A materialized view with MAX or MIN is fast refreshable after delete or mixed DML statements if it does not have a WHERE clause.

  The max/min fast refresh after delete or mixed DML does not have the same behavior as the insert-only case. It deletes and recomputes the max/min values for the affected groups. You need to be aware of its performance impact.

• 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, see Oracle Database SQL Tuning Guide.

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

• 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.

• For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, the following restrictions apply:

  • 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.

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

I think you missed

• COUNT(*) must be specified.
• For each aggregate such as AVG(expr), the corresponding COUNT(expr) must be present.

• Specify with ROWID and INCLUDING NEW VALUES.

• Specify the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.