物化视图 快速刷新限制条件
刚看了一下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.
refrence :
ON-COMMIT :
Refresh occurs automatically when a transaction that modified one of the
materialized view's fact tables commits.
Can be used with materialized views on single table aggregates
and with materialized views containing joins only.
As can be seen from above, an ON-COMMIT can be used only under specific cases:
These are:
1. The M.V. should have a single table aggregate or
2. the M.V. should have a join only.
3. count(*) must be present for Single-Table Aggregates (see example above).
4. count(<col> should be present.
Here, <col> stands for the column which is being aggregated.
Note: the only time that count(col)is not required is when
the aggregate itself is a count(col).
5. It should be possible perform a fast refresh on the materialized view.
Fast refresh by itself has a few restrictions.
These are as follows:
a)The FROM list must contain base tables only (that is, no views).
b)It cannot contain references to non-repeating expressions like
SYSDATE and ROWNUM.
c)It cannot contain references to RAW or LONG RAW data types.
d)It cannot contain HAVING or CONNECT BY clauses.
e)The WHERE clause can contain only joins and they must be equi-joins
(inner or outer) and all join predicates must be connected with
ANDs. No selection predicates on individual tables are allowed
f)It cannot have subqueries, inline views, or set functions like
UNION or MINUS.
In addition for M.V.'s with Single-Table Aggregates and Materialized Views
with Joins and Aggregates, there are some more conditions on refresh
to the ones mentioned above:
Single Table Aggregates:
=======================
i) They can only have a single table.
ii) The SELECT list must contain all GROUP BY columns.
iii) Expressions are allowed in the GROUP BY and SELECT
clauses provided they are the same.
iv) They cannot have a WHERE clause.
v) They cannot have a MIN or MAX function.
vi) A materialized view log must exist on the table and must contain all
columns referenced in the materialized view. The log must have been
created with the INCLUDING NEW VALUES clause.
vii) If AVG(expr) or SUM(expr) is specified, you must have COUNT(expr).
viii) If VARIANCE(expr) or STDDEV(expr) is specified,
you must have COUNT(expr) and SUM(expr).
Joins and Aggregates :
=====================
i)The WHERE clause can contain inner equi-joins only
(that is, no outer joins)
ii)Materialized views from this category are FAST refreshable after
Direct Load to the base tables; they are not FAST refreshable after
conventional DML to the base tables.
iii)Materialized views from this category can have only the
ON DEMAND option (so, the on-commit cannot be used for this category).
做参考：
ON COMMIT：
刷新发生时，自动交易，修改了一个
物化视图的事实表提交。
可以用单表聚集的物化视图
和物化视图包含联接。
从上面可以看出，仅在特定情况下，一个ON COMMIT可用于：
它们是：
1。 M.V.应该有一个单一表聚合或
2。 M.V.应该有一个只参加。
3。 COUNT（*）必须是单表骨料（见上面的例子）。
4。计数（<col>应在场。
在这里，被聚合的列是<col>代表。
注：计数（COL）是唯一的一次，不需要时
总本身就是一个计数（COL）。
5。它应该是可以进行快速刷新物化视图。
快速刷新本身有一些限制。
这些如下：
一）从列表必须只包含基表（即，没有意见）。
B）它不能包含非重复表达式的引用
SYSDATE和ROWNUM。
C）它不能包含RAW或LONG RAW数据类型的引用。
D）它可以不包含HAVING或CONNECT BY子句。
e）在WHERE子句中可以包含联接和他们必须相等联接
（内部或外部）和所有连接谓词必须连接
与门。允许单个表没有选择谓词
F），它不能有子查询，内嵌视图，或一组功能，如
UNION或负。
除了MV的单表的聚集和物化视图
联接和聚合，还有一些更刷新
上面提到的那些：
单表总量：
=======================
i）彼等只能有一个单一的表。
ii）本SELECT列表必须包含所有GROUP BY列。
III）表达式允许在GROUP BY和SELECT
条款提供，它们是相同的。
IV），他们不能有一个WHERE子句。
V），他们不能有一个MIN或MAX功能。
六）物化视图日志必须存在于表必须包含所有
物化视图中引用的列。日志必须一直
建立包括新的价值观条款。
vii）倘指定AVG（表达式）或SUM（表达式），你必须有COUNT（表达式）。
八）如果方差（表达式）或STDDEV（表达式）的规定，
你必须有COUNT（表达式）和SUM（表达式）。
加入总量：
=====================
i）本WHERE子句可以包含内等加入
（也就是说，没有外部联接）
2）这一类的物化视图快速刷新后
直接加载到基表，他们不是快速刷新后
传统的DML到基表。
三）只能有这一类的物化视图
ON DEMAND选项，提交不能用于这一类。
