物化视图及日志内部机制的一点研究

foreverlee · 发表于 2006-6-8 00:22

多谢杨斑竹最近马上也要研究MV

pineapple0401 · 发表于 2010-1-27 09:36

lixunpeng · 发表于 2010-4-11 20:04

好详细，原来杨版主5年前就这么厉害了！

gaopengtttt · 发表于 2011-9-4 13:22

GOOD 版主研究很透。

gaopengtttt · 发表于 2011-9-4 13:35

SQL> create materialized view oo
  2  refresh complete
  3  enable query rewrite
  4  as
  5  select * from test;
select * from test
            *
ERROR at line 5:
ORA-12014: table 'TEST' does not contain a primary key constraint
我的表中没有主键。
SQL> create materialized view oo
  2  refresh complete with rowid
  3  as
  4  select * from test;

Materialized view created.

使用RIWID可以完成。我想问问，在完全刷新中ROWID或者主键到底起到什么作用？
快速刷新应该比较好理解，用于定位刷新哪些行。但是完全刷新，就是DELETE然后倒入数据啊，没用到主键或者ROWID啊

gaopengtttt · 发表于 2011-9-4 14:13

WITH SEQUENCE在刷新的时候有什么特别的用处没有？
谢谢！

Yong Huang · 发表于 2011-9-5 02:38

> WITH SEQUENCE在刷新的时候有什么特别的用处没有？

You must be referring to the "create materialized view log" statement. Documentation says

http://download.oracle.com/docs/ ... statements_6003.htm

"Specify SEQUENCE to indicate that a sequence value providing additional ordering information should be recorded in the materialized view log. Sequence numbers are necessary to support fast refresh after some update scenarios."

"To provide for fast refresh of materialized *aggregate* views, you must also specify the SEQUENCE and..." [my emphasis]

http://download.oracle.com/docs/ ... /b14223/basicmv.htm

"Oracle recommends that the keyword SEQUENCE be included in your materialized view log statement unless you are sure that you will never perform a mixed DML operation (a combination of INSERT, UPDATE, or DELETE operations on multiple tables). The SEQUENCE column is required in the materialized view log to support fast refresh with a combination of INSERT, UPDATE, or DELETE statements on multiple tables."

Yong Huang

xuebao_zx · 发表于 2011-9-28 11:04

楼主辛苦了，这么好的文章需要顶起来。

oracle_yn_365 · 发表于 2011-11-17 11:54

挺好的

[原创] 物化视图及日志内部机制的一点研究

浏览过的版块