Oracle 物化视图之--复杂物化视图创建

Oracle 物化视图之--复杂物化视图创建

包含聚集的物化视图

1.必须满足所有快速刷新物化视图都满足的条件；

2.物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制：

(1)包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列；

(2)必须指明ROWID和INCLUDING NEW VALUES；

(3)如果对基本的操作同时包括INSERT、UPDATE和DELETE操作（即不是只包含INSERT操作），那么物化视图日志应该包括SEQUENCE。

3.允许的聚集函数包括：SUM、COUNT、AVG、STDDEV、VARIANCE、MIN和MAX；

4.必须指定COUNT(*)；

不加COUNT(*)，建立快速刷新的物化视图会成功，但通过执行EXPLAIN_MVIEW过程，可以发现，COUNT(*)对于INSERT操作是可以快速刷新的，不过对于UPDATE和DELETE则会造成快速刷新的失败。

5.如果指明了除COUNT之外的聚集函数，则COUNT(expr)也必须存在；

比如：包含SUM(a)，则必须同时包含COUNT(a)。

6.如果指明了VARIANCE(expr)或STDDEV(expr)，除了COUNT(expr)外，SUM(expr)也必须指明；

Oracle推荐同时包括SUM(expr*expr)。

7.SELECT列表中必须包括所有的GROUP BY列；

8.当物化视图属于下面的某种情况，则快速刷新只支持常规DML插入和直接装载，这种类型的物化视图又称为INSERT-ONLY物化视图；

物化视图包含MIN或MAX聚集函数；

物化视图包含SUM(expr)，但是没有包括COUNT(expr)；

物化视图没有包含COUNT(*)。

注意：如果建立了这种物化视图且刷新机制是ON COMMIT的，则会存在潜在的问题。当出现了UPDATE或DELETE语句，除非手工完全刷新解决这个问题，否则物化视图至此以后都不再自动刷新，且不会报任何错误。

9.如果包含inline views、outer joins、self joins或grouping set，则兼容性的设置必须在9.0以上；

10.如果物化视图建立在视图或子查询上，则要求视图必须可以完全合并的。

11.如果没有外连接，则对WHERE语句没有限制。如果包含外连接，则要求WHERE语句只能包括AND连接和“=”操作。对于包含外连接的聚集物化视图，快速刷新支持outer表的修改。且inter表的连接列上必须存在唯一约束。

12.对于包含了ROLLUP、CUBE、GROUPINGSET的物化视图必须满足下列限制条件：

SELECT语句列表中应该包含GROUPING标识符：可以是GROUP BY表达式中所有列的GROUPING_ID函数，也可以是GROUP BY表达式中每一列的GROUPING函数；

例如：GROUP BY语句为：GROUP BY CUBE(a, b)，则SELECT列表应该包括GROUPING_ID(a,b)或者GROUPING(a)和GROUPING(b)。

GROUPBY不能产生重复的GROUPING。

比如：GROUP BY a,ROLLUP(a, b)则不支持快速刷新，因为包含了重复的GROUPING：(a), (a,b), (a)。

案例分析：

1）

12:15:24 SCOTT@ prod> create MATERIALIZED VIEW log on emp with primary key;
Materialized view log created.

12:16:00 SCOTT@ prod> CREATE MATERIALIZED VIEW "SCOTT"."EMP_MVW"
12:17:52   2  refresh fast with primary key
12:17:59   3  enable query rewrite
12:18:06   4  as
12:18:08   5  select empno,max(sal) max_sal,avg(sal) avg_sal,min(sal) min_sal,sum(sal) sum_sal from emp
12:18:27   6  group by empno;
select empno,max(sal) max_sal,avg(sal) avg_sal,min(sal) min_sal,sum(sal) sum_sal from emp
                                                                                      *
ERROR at line 5:
ORA-12015: cannot create a fast refresh materialized view from a complex query

2）
12:44:24 SCOTT@ prod> CREATE MATERIALIZED VIEW "SCOTT"."EMP_MVW"
12:44:51   2   refresh fast with primary key
12:44:51   3    enable query rewrite
12:44:51   4   AS select deptno,max(sal) max_sal,avg(sal) avg_sal,min(sal) min_sal,sum(sal) sum_sal ,count(sal) cnt_sal from emp  group by deptno;
 AS select deptno,max(sal) max_sal,avg(sal) avg_sal,min(sal) min_sal,sum(sal) sum_sal ,count(sal) cnt_sal from emp  group by deptno
                                                                                                               *
ERROR at line 4:
ORA-12032: cannot use rowid column from materialized view log on "SCOTT"."EMP"

3）
12:45:50 SCOTT@ prod> drop MATERIALIZED VIEW log on emp;
Materialized view log dropped.

12:48:12 SCOTT@ prod> create MATERIALIZED VIEW log on emp with rowid;
Materialized view log created.

12:48:24 SCOTT@ prod> CREATE MATERIALIZED VIEW "SCOTT"."EMP_MVW"
12:48:43   2   refresh fast 
12:48:43   3    enable query rewrite
12:48:43   4   AS select deptno,max(sal) max_sal,avg(sal) avg_sal,min(sal) min_sal,sum(sal) sum_sal ,count(sal) cnt_sal from emp  group by deptno;
 AS select deptno,max(sal) max_sal,avg(sal) avg_sal,min(sal) min_sal,sum(sal) sum_sal ,count(sal) cnt_sal from emp  group by deptno
                                                                                                               *
ERROR at line 4:
ORA-32401: materialized view log on "SCOTT"."EMP" does not have new values

4）
12:48:43 SCOTT@ prod> drop MATERIALIZED VIEW log on emp;
Materialized view log dropped.

12:49:04 SCOTT@ prod> create MATERIALIZED VIEW log on emp with rowid including new values;
Materialized view log created.

12:49:39 SCOTT@ prod> CREATE MATERIALIZED VIEW "SCOTT"."EMP_MVW"
12:49:54   2   refresh fast 
12:49:54   3    enable query rewrite
12:49:54   4   AS select deptno,max(sal) max_sal,avg(sal) avg_sal,min(sal) min_sal,sum(sal) sum_sal ,count(sal) cnt_sal from emp  group by deptno;
 AS select deptno,max(sal) max_sal,avg(sal) avg_sal,min(sal) min_sal,sum(sal) sum_sal ,count(sal) cnt_sal from emp  group by deptno
                                                                                                               *
ERROR at line 4:
ORA-12033: cannot use filter columns from materialized view log on "SCOTT"."EMP"

5）
12:49:55 SCOTT@ prod> drop MATERIALIZED VIEW log on emp;
Materialized view log dropped.

12:51:16 SCOTT@ prod> create MATERIALIZED VIEW log on emp with rowid,sequence(sal,deptno) including new values;
Materialized view log created.

12:52:13 SCOTT@ prod>  CREATE MATERIALIZED VIEW "SCOTT"."EMP_MVW"
12:52:32   2   refresh fast with rowid
12:52:32   3    enable query rewrite
12:52:32   4   AS select deptno,max(sal) max_sal,avg(sal) avg_sal,min(sal) min_sal,sum(sal) sum_sal ,count(sal) cnt_sal from emp  group by deptno;
Materialized view created.

--------- 至此，带有分组和聚合函数的物化视图创建成功！