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

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

包含聚集的物化视图

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

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

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

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

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

3.允许的聚集函数包括：SUM、COUNT、AVG、STDDEV、VARIANCE、@H_321_21@mIN和@H_321_21@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物化视图；

物化视图包含@H_321_21@mIN或@H_321_21@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;
@H_321_21@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,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,sum(sal) sum_sal,count(sal) cnt_sal from emp group by deptno;
AS SELEct deptno,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;
@H_321_21@materialized vIEw log dropped.

12:48:12 SCott@ prod> create MATERIAliZED VIEW log on emp with rowID;
@H_321_21@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,count(sal) cnt_sal from emp group by deptno;
AS SELEct deptno,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;
@H_321_21@materialized vIEw log dropped.

12:49:04 SCott@ prod> create MATERIAliZED VIEW log on emp with rowID including new values;
@H_321_21@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,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;
@H_321_21@materialized vIEw log dropped.

12:51:16 SCott@ prod> create MATERIAliZED VIEW log on emp with rowID,sequence(sal,deptno) including new values;
@H_321_21@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,count(sal) cnt_sal from emp group by deptno;
@H_321_21@materialized vIEw created.

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