oracle物化视图无法快速刷新

[jc19870214]

CREATE MATERIALIZED VIEW zy
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
START WITH SYSDATE
NEXT trunc(SYSDATE+1)+2/24
WITH PRIMARY KEY
DISABLE QUERY REWRITE AS
SELECT a.*,b.DEPT_CODE
FROM ITEMLIST@to_db A, COM_EMPLOYEE@to_db B
WHERE a.CODE=b.EMPL_CODE
执行显示ora-12052无法快速刷新实体化视图
CREATE MATERIALIZED VIEW LOG ON ITEMLIST WITH PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON com_employee WITH PRIMARY KEY;
相应的mvlog已创建，会是什么原因？
或者有什么办法可以使本地数据库中有A表的数据并且加上一列相应的b.dept_code
注：b.dept_code 是会有更新的，而A表数据对应的想要是当时的

[jc19870214]

引用 2 楼 wildwave 的回复:

If you specify REFRESH FAST, Oracle performs further verification of the query definition to ensure that fast refresh can be performed if any of the detail tables change. These additional checks are: A materialized view log must be present for each detail table unless the table supports PCT. Also, when a materialized view log is required, the ROWID column must be present in each materialized view log. The rowids of all the detail tables must appear in the SELECT list of the materialized view query definition. If the materialized view contains only joins, the ROWID columns for each table (and each instance of a table that occurs multiple times in the FROM list) must be present in the SELECT list of the materialized view.

谢谢，很有帮助，改成这样了SELECT a.rowid,a.*,b.rowid,b.DEPT_CODE FROM ITEMLIST@to_db A, COM_EMPLOYEE@to_db B WHERE a.CODE=b.EMPL_CODE 但有一点不明白的是，这样写，如果远端数据库COM_EMPLOYEE B表update了b.dept_code,那MV中之前刷新的老数据对应的b.dept_code是不是也会相应刷新成新的b.dept_code

[小灰狼W]

引用 3 楼 jc19870214 的回复:

[quote=引用 2 楼 wildwave 的回复:] If you specify REFRESH FAST, Oracle performs further verification of the query definition to ensure that fast refresh can be performed if any of the detail tables change. These additional checks are: A materialized view log must be present for each detail table unless the table supports PCT. Also, when a materialized view log is required, the ROWID column must be present in each materialized view log. The rowids of all the detail tables must appear in the SELECT list of the materialized view query definition. If the materialized view contains only joins, the ROWID columns for each table (and each instance of a table that occurs multiple times in the FROM list) must be present in the SELECT list of the materialized view.

谢谢，很有帮助，改成这样了SELECT a.rowid,a.*,b.rowid,b.DEPT_CODE FROM ITEMLIST@to_db A, COM_EMPLOYEE@to_db B WHERE a.CODE=b.EMPL_CODE 但有一点不明白的是，这样写，如果远端数据库COM_EMPLOYEE B表update了b.dept_code,那MV中之前刷新的老数据对应的b.dept_code是不是也会相应刷新成新的b.dept_code[/quote] 会的。这些更改会被记录在MATERIALIZED VIEW LOG 中，在刷新的时候，根据rowid找到对应的记录，对目标物化视图进行增删改 你可以做个测试，修改完b表的dept_code以后，手动对物化视图进行刷新

[jc19870214]

明白了，非常感谢！

[anhy]

http://blog.itpub.net/22990797/viewspace-752142/ 看下物化视图快速刷新的限制，看看是不是有不满足的

[小灰狼W]

If you specify REFRESH FAST, Oracle performs further verification of the query definition to ensure that fast refresh can be performed if any of the detail tables change. These additional checks are: A materialized view log must be present for each detail table unless the table supports PCT. Also, when a materialized view log is required, the ROWID column must be present in each materialized view log. The rowids of all the detail tables must appear in the SELECT list of the materialized view query definition. If the materialized view contains only joins, the ROWID columns for each table (and each instance of a table that occurs multiple times in the FROM list) must be present in the SELECT list of the materialized view.

[天宫空间站]

For materialized views that use the fast refresh method, a materialized view log or direct loader log keeps a record of changes to the master tables.物化视图介绍中有这么句话，direct loader log指的是什么？能详细解释一下吗？