一个经过简化的例子：

CREATE TABLE t1(c1 NUMBER);
CREATE TABLE t2(c1 NUMBER);
CREATE TABLE t3(c1 NUMBER,c2 NUMBER);

-- 创建mv的查询
SELECT t2.c1
      ,t4.c1
      ,MAX(t3.c1)
FROM (SELECT MAX(t1.c1) c1
      FROM t1) t4
    ,t2
    ,t3
WHERE t3.c1 > t4.c1
      AND t2.c1 = t3.c2
GROUP BY t2.c1
        ,t4.c1

对于这种包含两层嵌套的查询，直接建立成快速刷新方式的物化视图显然是不可能的，但是可以利用中间物化视图进行过渡，而且对于10.2中，物化视图快速刷新的限制条件进一步放宽，使得包含MAX/MIN聚集函数的物化视图快速刷新的条件得以放宽：

SQL> CREATE TABLE T1(C1 NUMBER);

Table created.

SQL> CREATE TABLE T2(C1 NUMBER);

Table created.

SQL> CREATE TABLE T3(C1 NUMBER,C2 NUMBER);

Table created.

SQL> CREATE MATERIALIZED VIEW LOG ON T1
  2  WITH ROWID, SEQUENCE (C1)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW LOG ON T2
  2  WITH ROWID, SEQUENCE (C1)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW LOG ON T3
  2  WITH ROWID, SEQUENCE (C1, C2)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T4
  2  REFRESH FAST AS
  3  SELECT COUNT(*) CN, COUNT(C1), MAX(T1.C1) C1
  4  FROM T1;

Materialized view created.

SQL> CREATE MATERIALIZED VIEW LOG ON MV_T4
  2  WITH ROWID, SEQUENCE (C1)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW  MV_T123
  2  REFRESH FAST AS
  3  SELECT T2.C1 T2_C1, MV_T4.C1 T4_C1, COUNT(*) CNT, COUNT(T3.C1), MAX(T3.C1)
  4  FROM MV_T4, T2, T3
  5  WHERE T3.C1 > MV_T4.C1
  6  AND T2.C1 = T3.C2
  7  GROUP BY T2.C1, MV_T4.C1;

Materialized view created.

利用中间物化视图，这种多层的快速刷新物化视图可以很容易的建立。

不过需要注意的是，包含MAX/MIN的物化视图，仍然需要INSERT ONLY物化视图，对于包含UPDATE和DELETE操作的情况是不使用的。

SQL> @?/rdbms/admin/utlxmv

Table created.

SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW('MV_T123')

PL/SQL procedure successfully completed.

SQL> SELECT CAPABILITY_NAME, POSSIBLE, RELATED_TEXT, MSGTXT
  2  FROM MV_CAPABILITIES_TABLE
  3  WHERE CAPABILITY_NAME NOT LIKE '%PCT%'
  4  AND CAPABILITY_NAME LIKE 'REFRESH%';

CAPABILITY_NAME               P RELATED_TEXT MSGTXT
----------------------------- - ------------ ----------------------------------------------------------
REFRESH_COMPLETE              Y
REFRESH_FAST                  Y
REFRESH_FAST_AFTER_INSERT     Y
REFRESH_FAST_AFTER_ONETAB_DML N MAX(T3.C1)   mv uses the MIN or MAX aggregate functions
REFRESH_FAST_AFTER_ANY_DML    N              see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

对于MAX/MIN的INSERT ONLY特性，没有什么太好的办法，这里介绍了一个例子，可以在一定程度上解决这个问题：

改造包含MAX或MIN的物化视图：http://yangtingkun.itpub.net/post/468/492826