Oracle物化视图介绍(3)

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

GROUP BY不能产生重复的GROUPING。

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

首先，建立一个只包含聚集操作的满足快速刷新条件的例子，然后建立一个同时包括聚集和连接操作的例子。

最后依次违反上述11个条件，分别测试，查看会出现什么错误。（第一种约束条件是所有物化视图都必须满足的条件，在文章物化视图的快速刷新（一）中已经进行了说明，这里就不再重复描述了）。

首先，创建测试环境：

SQL> create table dim_a (id number primary key, name varchar2(30)); 表已创建。

SQL> create table dim_b (id number primary key, name varchar2(30)); 表已创建。

SQL> create table fact (id number, aid number, bid number, num number); 表已创建。

SQL> alter table fact add constraint fk_fact_aid foreign key (aid) references dim_a(id); 表已更改。

SQL> alter table fact add constraint fk_fact_bid foreign key (bid) references dim_b(id); 表已更改。

SQL> insert into dim_a select rownum, 'a'||rownum from user_objects; 已创建48行。

SQL> insert into dim_b select rownum, 'b'||rownum from user_objects; 已创建48行。

SQL> insert into fact select rownum, mod(rownum, 6) + 1, mod(rownum, 5 ) + 1, rownum *2

2 from user_objects; 已创建48行。 SQL> commit; 提交完成。

建立可以成功快速刷新的例子，这个物化视图只包含聚集：

SQL> create materialized view log on fact with rowid, sequence (aid, num) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view mv_fact refresh fast on commit as 2 select aid, count(*) count, count(num) count_num, sum(num) sum_num 3 from fact group by aid; 实体化视图已创建。

建立快速刷新的例子，物化视图包含聚集和连接：

SQL> drop materialized view mv_fact; 实体化视图已删除。

SQL> drop materialized view log on fact; 实体化视图日志已删除。

SQL> create materialized view log on fact with rowid, sequence (aid, num) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view log on dim_a with rowid, sequence (id, name) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view mv_fact refresh fast on commit as

2 select name, count(*) count, count(num) count_num, sum(num) sum_num 3 from fact, dim_a where aid = dim_a.id group by name; 实体化视图已创建。

接着，依次违反聚集物化视图快速刷新的每一个限制，检查出现的错误信息：

2.物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制： (1)包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列； (2)必须指明ROWID和INCLUDING NEW VALUES；

(3)如果对基表的操作同时包括INSERT、UPDATE和DELETE操作（即不是只包含INSERT操作），那么物化视图日志应该包括SEQUENCE。 SQL> drop materialized view mv_fact; 实体化视图已删除。

SQL> drop materialized view log on fact; 实体化视图日志已删除。

SQL> create materialized view mv_fact refresh fast on commit as 2 select aid, count(*) count, count(num) count_num, sum(num) sum_num 3 from fact group by aid; from fact group by aid *

ERROR 位于第 3 行:

ORA-23413: 表 \不带实体化视图日志

SQL> create materialized view log on fact with rowid, sequence (aid, num) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view mv_fact_error refresh fast on commit as 2 select aid, count(*) count, count(num) count_num, sum(num) sum_num, 3 count(bid) count_b from fact group by aid; count(bid) count_b from fact group by aid *

ERROR 位于第 3 行:

ORA-12033: 不能使用 \上实体化视图日志中的过滤器列

SQL> create materialized view mv_fact_error refresh fast on commit as 2 select aid, count(*) count, count(num) count_num, sum(num) sum_num

3 from fact where bid = 5 group by aid; from fact where bid = 5 group by aid *

ERROR 位于第 3 行:

ORA-12033: 不能使用 \上实体化视图日志中的过滤器列 SQL> drop materialized view log on fact; 实体化视图日志已删除。

SQL> create materialized view log on fact with rowid, sequence (aid, num); 实体化视图日志已创建。

SQL> create materialized view mv_fact refresh fast on commit as 2 select aid, count(*) count, count(num) count_num, sum(num) sum_num 3 from fact group by aid; from fact group by aid *

ERROR 位于第 3 行:

ORA-32401: \上的实体化视图日志没有新值 SQL> drop materialized view log on fact; 实体化视图日志已删除。

SQL> drop materialized view log on dim_a; 实体化视图日志已删除。

SQL> create materialized view log on fact with rowid, sequence (aid, num) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view log on dim_a with primary key, sequence (name) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view mv_fact refresh fast on commit as

2 select name, count(*) count, count(num) count_num, sum(num) sum_num 3 from fact, dim_a where aid = dim_a.id group by name; from fact, dim_a where aid = dim_a.id group by name *

ERROR 位于第 3 行:

ORA-12032: 不能使用 \上实体化视图日志中的 rowid 列 SQL> drop materialized view log on fact; 实体化视图日志已删除。

SQL> drop materialized view log on dim_a; 实体化视图日志已删除。

SQL> create materialized view log on fact with rowid (aid, num) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view log on dim_a with rowid (id, name) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view mv_fact refresh fast on commit as

2 select name, count(*) count, count(num) count_num, sum(num) sum_num 3 from fact, dim_a where aid = dim_a.id group by name; 实体化视图已创建。

SQL> truncate table mv_capabilities_table; 表已截掉。 SQL> begin

2 …… 此处隐藏：5411字，全部文档内容请下载后查看。喜欢就下载吧 ……