包含DISTINCT的物化视图无法快速刷新，但是有些情况可以转化为可快速刷新的物化视图。

这一篇描述一个包含UNION ALL的例子。

改造DISTINCT物化视图（一）：http://yangtingkun.itpub.net/post/468/492698

改造DISTINCT物化视图（二）：http://yangtingkun.itpub.net/post/468/492739

看一个稍微复杂一点的例子：

SQL> CREATE TABLE T AS
  2  SELECT *        
  3  FROM DBA_TABLES;

Table created.

SQL> CREATE MATERIALIZED VIEW LOG ON T
  2  WITH ROWID, SEQUENCE (OWNER, TABLESPACE_NAME, STATUS)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE TABLE T1 AS
  2  SELECT *
  3  FROM DBA_INDEXES;

Table created.

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

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS
  2  SELECT DISTINCT OWNER,
  3  TABLESPACE_NAME,
  4  DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID') STATUS
  5  FROM T
  6  UNION ALL
  7  SELECT DISTINCT OWNER,
  8  TABLESPACE_NAME,
  9  DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID') STATUS
 10  FROM T1;
FROM T1
     *
ERROR at line 10:
ORA-12015: cannot create a fast refresh materialized view from a complex query

由于包含了DISTINCT，是的包含UNION ALL的物化视图无法快速刷新。这时利用第一篇文章中描述的方法，就可以实现UNION ALL视图的快速刷新：

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS
  2  SELECT OWNER,
  3  TABLESPACE_NAME,
  4  DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID') STATUS,
  5  COUNT(*) COUNT,
  6  1 FLAG
  7  FROM T
  8  GROUP BY OWNER, TABLESPACE_NAME, DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID')
  9  UNION ALL
 10  SELECT OWNER,
 11  TABLESPACE_NAME,
 12  DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID') STATUS,
 13  COUNT(*) COUNT,
 14  2 FLAG
 15  FROM T1
 16  GROUP BY OWNER, TABLESPACE_NAME, DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID');

Materialized view created.

除了这个方法，也可以建立嵌套的物化视图——先建立两个单表聚集的物化视图，在这两个物化视图上再建立一个UNION ALL的物化视图：

SQL> CREATE MATERIALIZED VIEW MV_T1_AGG REFRESH FAST AS
  2  SELECT OWNER,
  3  TABLESPACE_NAME,
  4  DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID') STATUS,
  5  COUNT(*) COUNT
  6  FROM T
  7  GROUP BY OWNER, TABLESPACE_NAME, DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID');          

Materialized view created.

SQL> CREATE MATERIALIZED VIEW MV_T2_AGG REFRESH FAST AS
  2  SELECT OWNER,
  3  TABLESPACE_NAME,
  4  DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID') STATUS,
  5  COUNT(*) COUNT
  6  FROM T1
  7  GROUP BY OWNER, TABLESPACE_NAME, DECODE(STATUS, 'UNUSABLE', 'INVALID', 'VALID');       

Materialized view created.

SQL> CREATE MATERIALIZED VIEW LOG ON MV_T1_AGG
  2  WITH ROWID;          

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW LOG ON MV_T2_AGG
  2  WITH ROWID;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T_UNIONALL REFRESH FAST AS
  2  SELECT ROWID R_ID,  
  3  OWNER,
  4  TABLESPACE_NAME,
  5  STATUS,                                               
  6  1 FLAG
  7  FROM MV_T1_AGG
  8  UNION ALL
  9  SELECT ROWID R_ID,
 10  OWNER,
 11  TABLESPACE_NAME,
 12  STATUS,
 13  2 FLAG
 14  FROM MV_T2_AGG;

Materialized view created.

需要注意，对于嵌套物化视图而言，先建聚集物化视图后建UNION ALL物化视图和先建UNION ALL物化视图后建聚集物化视图，都是可以支持快速刷新的。但是这两种方式返回的最终结果是有区别的。