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

@H_502_8@包含聚集的物化视图

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(*)。

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

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,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 selectdeptno,sum(sal) sum_sal,count(sal) cnt_salfrom 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;

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,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;

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,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,count(sal) cnt_sal from emp group by deptno;

Materialized view created.

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