oracle 当底层表改变时更新物化视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2027807/
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
Update materialized view when urderlying tables change
提问by álvaro González
I have a materialized view defined this way:
我有一个这样定义的物化视图：
CREATE MATERIALIZED VIEW M_FOO
REFRESH COMPLETE ON COMMIT
AS
SELECT FOO_ID, BAR
FROM FOO
WHERE BAR IS NOT NULL
GROUP BY FOO_ID, BAR
/
COMMENT ON MATERIALIZED VIEW M_FOO IS 'Foo-Bar pairs';
I wrote as a sort of cache: the source table is huge but the number of different pairs is fairly small. I need those pairs to get them JOINed with other tables. So far so good: it absolutely speeds queries.
我写了一种缓存：源表很大，但不同对的数量相当少。我需要这些对才能将它们与其他表连接起来。到目前为止一切顺利：它绝对加快了查询速度。
But I want to make sure that the view does not contain obsolete data. The underlying table is modified four or five times per month but I don't necessarily know when. I understand that a materialized view can be defined so it updates when the source tables change. However, the docs get pretty complicate.
但我想确保该视图不包含过时的数据。底层表每个月修改四五次，但我不一定知道什么时候。我知道可以定义物化视图，以便在源表更改时更新。但是，文档变得非常复杂。
What's the exact syntax I need to use?
Do I need to create a materialized view log?
What's the difference between fast and complete refresh?
我需要使用的确切语法是什么？
我需要创建物化视图日志吗？
快速刷新和完全刷新有什么区别？
回答by APC
To take your questions in reverse order
以相反的顺序回答你的问题
A FAST refresh is also known as an incremental refresh. That should give you a clue as to the difference. A COMPLETE refresh rebuilds the entire MVIEW from scratch, whereas a FAST refresh applies just the changes from DML executed against the feeder table(s).
快速刷新也称为增量刷新。这应该给你一个关于差异的线索。COMPLETE 刷新从头开始重建整个 MVIEW，而 FAST 刷新仅应用 DML 中针对馈送表执行的更改。
In order to do execute FAST refreshes you need the appropriate MVIEW LOG. This tracks changes to the data of the underlying tables, which allows Oracle to efficiently apply a deltato the materialized view, rather than querying the whole table.
为了执行快速刷新，您需要适当的 MVIEW LOG。这会跟踪对基础表的数据的更改，这使 Oracle 可以有效地将增量应用于物化视图，而不是查询整个表。
As for the syntax, here are the basics:
至于语法，这里是基础知识：
SQL> create materialized view log on emp
2 with rowid, primary key, sequence (deptno, job)
3 including new values
4 /
Materialized view log created.
SQL> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno, job from emp
5 group by deptno, job
6 /
Materialized view created.
SQL>
The ON COMMITclause means that the MVIEW is refreshed transactionally (as opposed to ON DEMANDwhich is regular refresh in bulk). The REFRESHclauses specifies whether to apply incremental or complete refreshes. There are some categories of query which force the use of COMPLETErefresh, although these seem to diminish with each new version of Oracle.
该ON COMMIT子句意味着 MVIEW 以事务方式刷新（与ON DEMAND定期批量刷新相反）。这些REFRESH子句指定是应用增量刷新还是完全刷新。有一些类别的查询会强制使用COMPLETE刷新，尽管这些类别似乎随着 Oracle 的每个新版本而减少。
A quick test to see that it works ...
快速测试看看它是否有效......
SQL> select * from emp_mv
2 order by deptno, job
3 /
DEPTNO JOB
---------- ---------
10 MANAGER
10 PRESIDENT
10 SALES
20 ANALYST
20 CLERK
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN
40 CLERK
40 DOGSBODY
11 rows selected.
SQL>
How about a new record?
新纪录怎么样？
SQL> insert into emp (empno, ename, deptno, job)
2 values (6666, 'GADGET', 40, 'INSPECTOR')
3 /
1 row created.
SQL> commit
2 /
Commit complete.
SQL> select * from emp_mv
2 order by deptno, job
3 /
DEPTNO JOB
---------- ---------
10 MANAGER
10 PRESIDENT
10 SALES
20 ANALYST
20 CLERK
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN
40 CLERK
40 DOGSBODY
40 INSPECTOR
12 rows selected.
SQL>
You can find more details on the syntax in the SQL Reference. It's also worth reading the Materialized View chapter in the Data Warehousing Guide.
您可以在SQL 参考 中找到有关语法的更多详细信息。也值得阅读数据仓库指南中的物化视图章节。
Despite the concerns of the commenters below this does work as advertised. Unfortunately the usual places for publishing demos (SQL Fiddle, db<>fiddle) do not allow materialized views. I have published something on Oracle SQL Live (free Oracle account required): I am awaiting Oracle approval for it and will update this question when it arrives.
尽管下面的评论者担心这确实像宣传的那样有效。不幸的是，通常用于发布演示的地方（SQL Fiddle、db<>fiddle）不允许物化视图。我已在 Oracle SQL Live 上发布了一些内容（需要免费的 Oracle 帐户）：我正在等待 Oracle 对其进行批准，并将在收到此问题时更新此问题。
回答by Jim Hudson
A fast refresh will only insert/update/delete changed data into the materialized view. A complete refresh will empty the materialized view and then copy over all rows.
快速刷新只会将更改的数据插入/更新/删除到物化视图中。完全刷新将清空物化视图，然后复制所有行。
The "on commit" means the materialized view will be refreshed whenever a change is committed in the master table. So your current syntax is going to be extremely inefficient. Every time somebody changes any row in foo, m_foo will be truncated and then every row in foo table will be inserted.
“提交时”意味着只要在主表中提交更改，就会刷新物化视图。因此，您当前的语法将非常低效。每次有人更改 foo 中的任何行时，m_foo 将被截断，然后将插入 foo 表中的每一行。
You can do better with fast refreshes, where only the modified rows in foo will be sent to m_foo. That gives you consistency without lots of overhead.
您可以通过快速刷新做得更好，其中只有 foo 中修改过的行才会发送到 m_foo。这为您提供了一致性，而不会产生大量开销。
create materialized view log on foo with primary key; -- assuming you have a primary key, you should create materialized view m_foo refresh fast on commit as \;
使用主键在 foo 上创建物化视图日志；-- 假设你有一个主键，你应该在提交时快速创建物化视图 m_foo refresh as \;
There are some additional subtleties with grants and synonyms if you're using db links, or the schema that owns foo isn't the one that owns m_foo.
如果您使用数据库链接，或者拥有 foo 的架构不是拥有 m_foo 的架构，则授权和同义词还有一些额外的微妙之处。
