Oracle物化视图刷新方式on commit 问题

Question

SQL> CREATE Materialized View q
2 REFRESH Force
3 on commit
4 NEXT SYSDATE + 5/(24*60)
5 AS
6 select nvl(a.n1,b.n1) as n1,nvl(a.n2,b.n2) as n2,nvl(n3,0) as n3,nvl(n4,0)a
s n4,(nvl(n4,0)-nvl(n3,0)) as n5 from a full join b on a.n1=b.n1 and a.n2=b.n2;

select nvl(a.n1,b.n1) as n1,nvl(a.n2,b.n2) as n2,nvl(n3,0) as n3,nvl(n4,0)as n4,
(nvl(n4,0)-nvl(n3,0)) as n5 from a full join b on a.n1=b.n1 and a.n2=b.n2

*
ERROR at line 6:
ORA-12051: ON COMMIT attribute is incompatible with other options
哦 谢谢 我是初学者 那你是上哪找的 是Oracle 官网吗 还是 Oracle 帮助 文档

Answer 1

刚看了一下10g的快速刷新限制条件，明确说明了包含分析函数的物化视图是无法快速刷新的：

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 analytical functions (for example, RANK) in the SELECT clause.

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 view cannot have remote detail tables.

Nested materialized views must have a join or aggregate.