oracle 提交物化视图时快速刷新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20103190/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fast Refresh on commit of materialized view
提问by Deepak Venga
I just created tables DEPT and EMP like follow :
我刚刚创建了表 DEPT 和 EMP，如下所示：
create table DEPT
( dept_no number , dept_name varchar(32) , dept_desc varchar(32),
CONSTRAINT dept_pk Primary Key (dept_no) );
create table EMP
( emp_no number, dept_no number, CONSTRAINT emp_pk Primary Key (emp_no,dept_no));
insert into dept values (10,'it','desc1');
insert into dept values (20,'hr','desc2');
insert into emp values (1,10);
insert into emp values (2,20);
I created materialized view logs on these tables with rowid and materialized views as follows:
我使用 rowid 和物化视图在这些表上创建了物化视图日志，如下所示：
create materialized view log on emp with rowid;
create materialized view log on dept with rowid;
create materialized view empdept_mv refresh fast on commit as
select a.rowid dept_rowid, b.rowid emp_rowid, a.dept_no,b.emp_no
from dept a, emp b
where a.dept_no=b.dept_no ;
select * from emp;
EMP_NO DEPT_NO
---------- ----------
1 10
2 20
3 30
select * from dept;
DEPT_NO DEPT_NAME DEPT_DESC
---------- -------------------------------- --------------------------------
10 it desc1
20 hr desc2
30 it desc3
select * from empdept_mv;
DEPT_ROWID EMP_ROWID DEPT_NO EMP_NO
------------------ ------------------ ---------- ----------
AAAli5AABAAAPZ6AAA AAAli7AABAAAQs6AAA 10 1
AAAli5AABAAAPZ6AAB AAAli7AABAAAQs6AAB 20 2
I inserted a new record and did COMMIT; ..but still when i check the materialized view, the new record is not shown in the materialized view.
我插入了一条新记录并执行了 COMMIT；..但是当我检查物化视图时，新记录没有显示在物化视图中。
insert into dept values (30,'it','desc3');
commit;
insert into emp values (3,30);
commit;
select * from empdept_mv;
DEPT_ROWID EMP_ROWID DEPT_NO EMP_NO
------------------ ------------------ ---------- ----------
AAAli5AABAAAPZ6AAA AAAli7AABAAAQs6AAA 10 1
AAAli5AABAAAPZ6AAB AAAli7AABAAAQs6AAB 20 2
Now, when I run the procedure for Fast and complete refresh as per, The Fast refresh does not update the Mview but the complete refresh does. ( Note: But the Mview is still REFRESH ON COMMIT)
现在，当我运行快速，彻底刷新的过程按，快速刷新不更新MView的，但完全刷新呢。（注意：但 Mview 仍然是 REFRESH ON COMMIT）
execute DBMS_MVIEW.REFRESH('empdept_mv', 'F', '', TRUE, FALSE, 0,0,0,FALSE, FALSE);
PL/SQL procedure successfully completed.
DEPT_ROWID EMP_ROWID DEPT_NO EMP_NO
------------------ ------------------ ---------- ----------
AAAli5AABAAAPZ6AAA AAAli7AABAAAQs6AAA 10 1
AAAli5AABAAAPZ6AAB AAAli7AABAAAQs6AAB 20 2
execute DBMS_MVIEW.REFRESH('test_mview2', 'C', '', TRUE, FALSE, 0,0,0,FALSE, FALSE);
PL/SQL procedure successfully completed.
DEPT_ROWID EMP_ROWID DEPT_NO EMP_NO
------------------ ------------------ ---------- ----------
AAAli5AABAAAPZ6AAA AAAli7AABAAAQs6AAA 10 1
AAAli5AABAAAPZ6AAB AAAli7AABAAAQs6AAB 20 2
AAAli5AABAAAPZ6AAC AAAli7AABAAAQs6AAC 30 3
The DBMS_MVIEW.EXPLAIN_MVIEW output is as shown : (capability_name --Possible-- msgtxt)
DBMS_MVIEW.EXPLAIN_MVIEW 输出如下所示：(capability_name --Possible--msgtxt)
- PCT --N--
- REFRESH_COMPLETE --Y--
- REFRESH_FAST --Y--
- REWRITE --N--
- PCT_TABLE --N-- Oracle error: see RELATED_NUM and RELATED_TEXT for details
- REFRESH_FAST_AFTER_INSERT --Y--
- REFRESH_FAST_AFTER_ONETAB_DML --Y--
- REFRESH_FAST_AFTER_ANY_DML --Y--
- REFRESH_FAST_PCT --N-- PCT is not possible on any of the detail tables in the mater
- REWRITE_FULL_TEXT_MATCH --N-- Oracle error: see RELATED_NUM and RELATED_TEXT for details
- REWRITE_FULL_TEXT_MATCH --N-- query rewrite is disabled on the materialized view
- REWRITE_PARTIAL_TEXT_MATCH --N-- materialized view cannot support any type of query rewrite
- REWRITE_PARTIAL_TEXT_MATCH --N-- query rewrite is disabled on the materialized view
- REWRITE_GENERAL --N-- materialized view cannot support any type of query rewrite
- REWRITE_GENERAL --N-- query rewrite is disabled on the materialized view
- REWRITE_PCT --N-- general rewrite is not possible or PCT is not possible on an
- PCT_TABLE_REWRITE --N-- Oracle error: see RELATED_NUM and RELATED_TEXT for details
- PCT --N--
- REFRESH_COMPLETE --Y--
- REFRESH_FAST --Y--
- 重写--N--
- PCT_TABLE --N-- Oracle 错误：有关详细信息，请参阅 RELATED_NUM 和 RELATED_TEXT
- REFRESH_FAST_AFTER_INSERT --Y--
- REFRESH_FAST_AFTER_ONETAB_DML --Y--
- REFRESH_FAST_AFTER_ANY_DML --Y--
- REFRESH_FAST_PCT --N-- PCT 在材料中的任何明细表上都是不可能的
- REWRITE_FULL_TEXT_MATCH --N-- Oracle 错误：有关详细信息，请参阅 RELATED_NUM 和 RELATED_TEXT
- REWRITE_FULL_TEXT_MATCH --N-- 在物化视图上禁用查询重写
- REWRITE_PARTIAL_TEXT_MATCH --N--物化视图不能支持任何类型的查询重写
- REWRITE_PARTIAL_TEXT_MATCH --N-- 在物化视图上禁用查询重写
- REWRITE_GENERAL --N-- 物化视图不能支持任何类型的查询重写
- REWRITE_GENERAL --N-- 在物化视图上禁用查询重写
- REWRITE_PCT --N-- 一般重写是不可能的或 PCT 是不可能的
- PCT_TABLE_REWRITE --N-- Oracle 错误：有关详细信息，请参阅 RELATED_NUM 和 RELATED_TEXT
How can I achieve Fast Refresh On Commit ?
The Oracle Version details are as follows:
NLSRTL 10.2.0.4.0 Production
Oracle Database 10g 10.2.0.4.0 64bit Production
PL/SQL 10.2.0.4.0 Production
TNS for Linux: 10.2.0.4.0 Production
如何在提交时实现快速刷新？
Oracle 版本详情如下：
NLSRTL 10.2.0.4.0 Production
Oracle Database 10g 10.2.0.4.0 64bit Production
PL/SQL 10.2.0.4.0 Production
TNS for Linux：10.2.0.4.0 Production
回答by g00dy
I don't know if the problem still persists, but as I took a look at the artice you provided, I noticed something (which might just be the solution here):
我不知道问题是否仍然存在，但是当我查看您提供的文章时，我注意到了一些事情（这可能只是这里的解决方案）：
ON COMMIT Refresh
提交刷新
A materialized view can be refreshed automatically using the ON COMMIT method. Therefore, whenever a transaction commits which has updated the tables on which a materialized view is defined, those changes are automatically reflected in the materialized view. The advantage of using this approach is you never have to remember to refresh the materialized view. The only disadvantage is the time required to complete the commit will be slightly longer because of the extra processing involved.However, in a data warehouse, this should not be an issue because there is unlikely to be concurrent processes trying to update the same table.
可以使用 ON COMMIT 方法自动刷新物化视图。因此，每当提交更新了定义了实体化视图的表的事务时，这些更改都会自动反映在实体化视图中。使用这种方法的优点是您永远不必记住刷新物化视图。唯一的缺点是由于涉及额外的处理，完成提交所需的时间会稍长一些。但是，在数据仓库中，这应该不是问题，因为不太可能有并发进程尝试更新同一个表。
- Notice the bold line.
- 注意粗线。
Then we have:
然后我们有：
Table 7-1 ON DEMAND Refresh Methods
表 7-1 按需刷新方法
Refresh Option Parameter Description COMPLETEC Refreshes by recalculating the defining queryof the materialized view.
刷新选项 参数 说明 COMPLETEC 通过重新计算物化视图的定义查询来刷新。
FASTF Refreshes by incrementally applying changes to the materialized view. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log-based FAST and FAST_PCT.
FASTF 通过对实体化视图增量应用更改来刷新。对于局部物化视图，它选择优化器估计最有效的刷新方法。考虑的刷新方法是基于日志的 FAST 和 FAST_PCT。
FAST_PCTP Refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables.
FAST_PCTP 通过重新计算具体化视图中受详细表中更改的分区影响的行来刷新。
FORCE? Attempts a fast refresh. If that is not possible, it does a complete refresh. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST, FAST_PCT, and COMPLETE.
力？尝试快速刷新。如果这是不可能的，它会完全刷新。对于局部物化视图，它选择优化器估计最有效的刷新方法。考虑的刷新方法是基于日志的 FAST、FAST_PCT 和 COMPLETE。
- Notice the bold lines.
- I personally prefer the FORCE Option.
- 注意粗线。
- 我个人更喜欢 FORCE 选项。
Could you please tell, if this occurs again after some time (depending of the parameters of the DB and the machine it runs on, so I can't even hint you how much)?
你能告诉我，如果这种情况在一段时间后再次发生（取决于数据库的参数和它运行的机器，所以我什至不能暗示你多少）？
When Fast Refresh is Possible
何时可以进行快速刷新
Not all materialized views may be fast refreshable.Therefore, use the package DBMS_MVIEW.EXPLAIN_MVIEWto determine what refresh methods are available for a materialized view.
并非所有物化视图都可以快速刷新。因此，使用包DBMS_MVIEW.EXPLAIN_MVIEW来确定哪些刷新方法可用于物化视图。
If you are not sure how to make a materialized view fast refreshable, you can use the DBMS_ADVISOR.TUNE_MVIEWprocedure, which provides a script containing the statements required to create a fast refreshable materialized view.
如果您不确定如何使物化视图可快速刷新，则可以使用DBMS_ADVISOR.TUNE_MVIEW过程，该过程提供了一个脚本，其中包含创建可快速刷新的物化视图所需的语句。
Cheers
干杯
回答by Dxxg
I see that you created the materialized view logs with ROWID, which is not really required as both tables have a primary key so you could try without the ROWID.
我看到您使用 ROWID 创建了物化视图日志，这并不是真正需要的，因为两个表都有一个主键，因此您可以尝试不使用 ROWID。
create materialized view log on emp; create materialized view log on dept;
在 emp 上创建物化视图日志；在部门创建物化视图日志；
Additionally, if you create the materialized view log with ROWID you should create the materialized view with rowid.
此外，如果您使用 ROWID 创建物化视图日志，则应使用 rowid 创建物化视图。
create materialized view empdept_mv refresh fast on commit WITH ROWID as select a.rowid dept_rowid, b.rowid emp_rowid, a.dept_no,b.emp_no from dept a, emp b where a.dept_no=b.dept_no ;
创建物化视图 empdept_mv 在提交 WITH ROWID 时快速刷新为 select a.rowid dept_rowid, b.rowid emp_rowid, a.dept_no,b.emp_no from dept a, emp b where a.dept_no=b.dept_no ；
You could try those changes and see if the materialized views fast refresh on commit.
您可以尝试这些更改，看看物化视图是否在提交时快速刷新。
