物化视图有三种刷新方式:COMPLETE、FAST和FORCE。
完全刷新(COMPLETE)会删除表中所有的记录(如果是单表刷新,可能会采用TRUNCATE的方式),然后根据物化视图中查询语句的定义重新生成物化视图。快速刷新(FAST)采用增量刷新的机制,只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。采用FORCE方式,Oracle会自动判断是否满足快速刷新的条件,如果满足则进行快速刷新,否则进行完全刷新。
显然快速刷新是物化视图刷新方式的首选。但是,不是所有的物化视图都可以进行快速刷新,只有满足某些条件的物化视图才具有快速刷新的能力。
根据查询的不同,快速刷新的限制条件也不相同,下面总结一下不同类型的物化视图对快速刷新的限制条件。
可以参考:
物化视图的快速刷新(二):http://yangtingkun.itpub.net/post/468/16456
物化视图的快速刷新(三):http://yangtingkun.itpub.net/post/468/16496
所有类型的快速刷新物化视图都必须满足的条件:
1.物化视图不能包含对不重复表达式的引用,如SYSDATE和ROWNUM;
2.物化视图不能包含对LONG和LONG RAW数据类型的引用。
只包含连接的物化视图:
1.必须满足所有快速刷新物化视图都满足的条件;
2.不能包括GROUP BY语句或聚集操作;
3.如果在WHERE语句中包含外连接,那么唯一约束必须存在于连接中内表的连接列上;
4.如果不包含外连接,那么WHERE语句没有限制,如果包含外连接,那么WHERE语句中只能使用AND连接,并且只能使用“=”操作。
5.FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中。
6.FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。
下面,我们看一个满足快速刷新条件的例子,然后依次违反上述6个条件,分别测试,查看会出现什么错误。
首先,创建测试环境:
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;
已创建74行。
SQL> insert into dim_b select rownum, 'b'||rownum from user_objects;
已创建74行。
SQL> insert into fact select rownum, mod(rownum, 6) + 1, mod(rownum, 5 ) + 1, rownum *2
2 from user_objects;
已创建74行。
SQL> commit;
提交完成。
接下来建立一个可以成功快速刷新的例子,这个物化视图只包含连接:
SQL> create materialized view log on dim_a with rowid;
实体化视图日志已创建。
SQL> create materialized view log on dim_b with rowid;
实体化视图日志已创建。
SQL> create materialized view log on fact with rowid;
实体化视图日志已创建。
SQL> create materialized view mv_fact refresh fast on commit as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id;
实体化视图已创建。
下面我们依次违反上面的6个条件,观察错误信息。
1.包含SYSDATE/ROWNUM或RAW/LONG RAW数据类型。
SQL> create materialized view mv_fact_err refresh fast on commit as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num, sysdate time
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位于第 4 行:
ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性
错误提示是不能设置ON COMMIT属性,去掉ON COMMIT语句再次尝试:
SQL> create materialized view mv_fact_err refresh fast as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num, sysdate time
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位于第 4 行:
ORA-12015: 不能从复杂查询中创建一个可快速刷新的实体化视图
根据预期一样,建立快速刷新的物化视图报错,不过提示信息不明确,很难定位具体问题。使用ROWNUM后报错的提示信息和使用SYSDATE的一样。
可以通过DBMS_MVIEW包的EXPLAIN_MVIEW过程来得到更加详细的错误信息。有关这个包的具体使用方法可以参考:http://blog.itpub.net/post/468/13318
SQL> begin
2 dbms_mview.explain_mview('select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num, rownum row_id
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id');
7 end;
8 /
PL/SQL 过程已成功完成。
SQL> select msgtxt from mv_capabilities_table where capability_name = 'REFRESH_FAST_AFTER_INSERT';
MSGTXT
---------------------------------------
在实体化视图中引用了 ROWNUM
使用这种方法就可以得到更加准确的错误信息了。
测试另外一个约束之前要说明一下,Oracle文档上给出的约束条件是不能包括RAW和LONG RAW类型,根据我的测试结果,估计是文档出现了错误,这里应该是LONG和LONG RAW类型。
SQL> alter table dim_a add (col_raw raw(10) default '1');
表已更改。
SQL> create materialized view mv_fact_err refresh fast on commit as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num, a.col_raw
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id;
实体化视图已创建。
SQL> update dim_a set col_raw = '2';
已更新74行。
SQL> commit;
提交完成。
SQL> select distinct col_raw from mv_fact_err;
COL_RAW
--------------------
02
SQL> drop materialized view mv_fact_err;
实体化视图已删除。
SQL> alter table dim_a drop (col_raw);
表已更改。
SQL> alter table dim_a add (col_long long default '1');
表已更改。
SQL> create materialized view mv_fact_err refresh fast on commit as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num, a.col_long
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位于第 4 行:
ORA-00997: 非法使用 LONG 数据类型
SQL> alter table dim_a drop (col_long);
表已更改。
2.不能包括GROUP BY语句或聚集操作
SQL> create materialized view mv_fact_err refresh fast on commit as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, count(num) count_num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id
7 group by f.rowid, a.rowid, b.rowid, f.id, a.name, b.name;
from fact f, dim_a a, dim_b b
*
ERROR 位于第 4 行:
ORA-32401: "YANGTK"."DIM_B" 上的实体化视图日志没有新值
不管是否存在连接,只要包括聚集操作,就要满足聚集物化视图快速刷新的条件。这个错误提示正是聚集物化视图快速刷新需要满足的条件之一。
3.如果在WHERE语句中包含外连接,那么唯一约束必须存在于连接中内表的连接列上:
SQL> drop materialized view mv_fact;
实体化视图已删除。
SQL> create materialized view mv_fact refresh fast on commit as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 and f.bid = b.id;
实体化视图已创建。
SQL> drop materialized view mv_fact;
实体化视图已删除。
SQL> alter table fact drop constraint fk_fact_aid;
表已更改。
SQL> alter table dim_a drop primary key;
表已更改。
SQL> create materialized view mv_fact refresh fast on commit as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 and f.bid = b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位于第 4 行:
ORA-12052: 无法快速刷新实体化视图 YANGTK.MV_FACT
这个错误提示不是很明确,但是Oracle错误消息文档上对这个错误产生的原因已经分析的很清楚了。
ORA-12052 cannot fast refresh materialized view string.string
Cause: Either ROWIDs of certain tables were missing in the definition or the inner table of an outer join did not have UNIQUE constraints on join columns.
Action: Specify the FORCE or COMPLETE option. If this error occurred during creation, the materialized view definition may have been changed. Refer to the documentation on materialized views.
SQL> delete mv_capabilities_table;
已删除13行。
SQL> begin
2 dbms_mview.explain_mview('select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 and f.bid = b.id');
7 end;
8 /
PL/SQL 过程已成功完成。
SQL> select msgtxt from mv_capabilities_table where capability_name = 'REFRESH_FAST_AFTER_INSERT';
MSGTXT
----------------------------------------------------------
在内部表的联接列上没有唯一性约束条件
SQL> alter table dim_a add primary key (id);
表已更改。
SQL> alter table fact add constraint fk_fact_aid foreign key (aid) references dim_a(id);
表已更改。
4.如果不包含外连接,那么WHERE语句没有限制,如果包含外连接,那么WHERE语句中只能使用AND连接,并且只能使用“=”操作:
SQL> create materialized view mv_fact refresh fast on commit as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 or f.bid = b.id;
实体化视图已创建。
SQL> create materialized view mv_fact_err refresh fast on commit as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 or f.bid = b.id;
where f.aid = a.id(+)
*
ERROR 位于第 5 行:
ORA-01719: OR 或 IN 操作数中不允许外部连接运算符 (+)
这个错误提示是十分明显的,不过下面的又只能通过EXPLAIN_MVIEW过程来定位错误原因。
SQL> create materialized view mv_fact_err refresh fast on commit as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 and f.bid != b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位于第 4 行:
ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性
SQL> create materialized view mv_fact_err refresh fast as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 and f.bid != b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位于第 4 行:
ORA-12015: 不能从复杂查询中创建一个可快速刷新的实体化视图
SQL> delete mv_capabilities_table;
已删除15行。
SQL> begin
2 dbms_mview.explain_mview('select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 and f.bid != b.id');
7 end;
8 /
PL/SQL 过程已成功完成。
SQL> select msgtxt from mv_capabilities_table where capability_name = 'REFRESH_FAST_AFTER_INSERT';
MSGTXT
------------------------------------------------------------
使用了除等号 (=) 外的运算符联接谓词
实体化视图中的外部联接
在实体化视图中存在一个或多个联接
5.FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中:
SQL> create materialized view mv_fact_err refresh fast on commit as
2 select f.rowid f_rowid, a.rowid a_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 or f.bid = b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位于第 4 行:
ORA-12052: 无法快速刷新实体化视图 YANGTK.MV_FACT_ERR
这个错误的信息在上面已经列出了,错误原因说明比较明确。
SQL> delete mv_capabilities_table;
已删除17行。
SQL> begin
2 dbms_mview.explain_mview('select f.rowid f_rowid, a.rowid a_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 or f.bid = b.id');
7 end;
8 /
PL/SQL 过程已成功完成。
SQL> select msgtxt from mv_capabilities_table where capability_name = 'REFRESH_FAST_AFTER_INSERT';
MSGTXT
---------------------------------------------------
SELECT 列表没有所有从表的 rowid
6.FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。
SQL> drop materialized view log on dim_a;
实体化视图日志已删除。
SQL> drop materialized view mv_fact;
实体化视图已删除。
SQL> create materialized view mv_fact refresh fast on commit as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位于第 4 行:
ORA-23413: 表 "YANGTK"."DIM_A" 不带实体化视图日志
这个错误说明十分明确,可以直接定位错误原因。
相关推荐
物化视图有三种刷新方式:COMPLETE、FAST 和 FORCE。 COMPLETE 刷新方式会删除表中所有的记录,然后根据物化视图中查询语句的定义重新生成物化视图。FAST 刷新方式采用增量刷新的机制,只将自上次刷新以后对基表进行...
- `REFRESH` 选项定义了刷新策略,如 `FORCE` 在每次查询时强制刷新,`FAST` 利用物化视图日志进行快速刷新,`COMPLETE` 是完全刷新,`NEVER` 表示不自动刷新。 - `ON COMMIT` 或 `ON DEMAND` 定义了刷新时机,`ON ...
创建物化视图时,可以指定不同的刷新方法:FAST、COMPLETE、FORCE和NEVER。FAST仅刷新自上次刷新以来的更改,节省资源;COMPLETE则对整个物化视图进行全面刷新,即使在数据量较大时也可能较慢;FORCE则尝试使用FAST...
1. FAST 子句:增量刷新,基于物化视图日志,用于发送主表已经修改的数据行到物化视图中。 2. COMPLETE 子句:完全刷新,重新生成整个视图。 3. FORCE 子句:当指定 FORCE 子句,如果增量刷新可用 Oracle 将完成增量...
#### 三、物化视图的刷新机制 刷新物化视图是指更新物化视图中的数据,以保持与源表的一致性。Oracle提供了几种不同的刷新方法: - **REFRESH FAST**:增量刷新,仅更新自上次刷新以来发生更改的部分数据。需要为...
刷新方法包括`FAST`、`COMPLETE`、`FORCE`和`NEVER`,其中`FAST`是增量刷新,`COMPLETE`是全量刷新,`FORCE`尝试快速刷新但若不可行则转为全量,`NEVER`则不刷新。 物化视图日志对于快速刷新至关重要,它可以是...
- **FORCE**:默认的刷新方式,根据实际情况选择FAST或COMPLETE方式。 - **NEVER**:物化视图不进行任何刷新。 3. **查询重写(Query Rewrite)** - **ENABLE QUERY REWRITE**:启用查询重写功能,允许Oracle自动...
2. **完全刷新(REFRESH COMPLETE)**:重新生成整个物化视图,适合数据变更较大或需要重建索引等情况。 3. **强制刷新(REFRESH FORCE)**:根据现有条件选择最合适的刷新方式,即若增量刷新可用则执行增量刷新,...
在创建物化视图时,可以指定刷新模式,例如`FAST`, `COMPLETE`, `ON COMMIT`等,以及并发控制选项如`NOFORCE`、`FORCE`等,以适应不同的应用需求。 #### 四、物化视图的刷新机制 物化视图的刷新是指当源表数据发生...
在Oracle数据库中,物化视图(Materialized View)是一种用于优化查询性能的重要工具。它预先计算并存储了复杂的查询结果,从而在实际查询时能够快速地返回数据,大大提升了查询效率。然而,在创建物化视图的过程中...
3. **数据刷新策略**:Oracle提供了多种刷新物化视图的策略,包括快速刷新(fast refresh)、完整刷新(complete refresh)和强制刷新(force refresh)。快速刷新依赖于主表的日志,可以仅更新自上次刷新以来发生...
- `FAST`、`COMPLETE`、`BEGIN`、`FORCE`和`NEVER`:这些是不同类型的刷新策略,分别对应快速刷新(仅刷新变化的部分)、完整刷新(重新计算整个视图)、定时开始刷新、强制刷新(即使视图正在被查询也强制刷新)和...
然而,如果物化视图包含分析函数,如`COUNT()`、`SUM()`等,就不能使用FAST刷新,因为分析函数的结果不能增量更新,这时可能需要使用COMPLETE或FORCE刷新。 完全刷新(COMPLETE)会丢弃现有的物化视图数据,然后...
物化视图的刷新选项包括`FAST`、`COMPLETE`和`FORCE`,它们决定了数据如何更新: - `FAST`:增量刷新,仅更新自上次刷新以来发生变化的数据行。 - `COMPLETE`:全量刷新,重新计算整个物化视图,这可能需要更多的...
`FAST`基于物化视图日志进行增量更新,`COMPLETE`则是全量刷新,而`FORCE`则是在无法进行增量刷新时强制进行全量刷新。刷新可以按需(`ON DEMAND`)或在每次提交(`COMMIT`)后执行,并可以设置特定的刷新时间间隔。 ...
- **Force刷新**:优先尝试Fast刷新,若不可行,则转为Complete刷新。 4. **创建和管理快照** 创建快照的基本语法是 `CREATE SNAPSHOT <快照名称> AS 查询语句>`,而设置刷新机制的命令为 `ALTER SNAPSHOT <快照...
SQL(Structured Query Language)结构化查询语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。同时也是数据库脚本文件的扩展名。 SQL语言主要包含5个部分 数据定义...
[REFRESH [FAST|COMPLETE|FORCE]] [ON DEMAND|COMMIT] [START WITH date] [NEXT date] [WITH {PRIMARY KEY|ROWID}] AS subquery; ``` 6. **手动刷新物化视图**: - 使用 `DBMS_MVIEW.REFRESH` 过程: ```...