更改餐桌时更新物化视图 [英] Update materialized view when urderlying tables change
问题描述
我有一个这样定义的物化视图:
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?
推荐答案
以相反的顺序回答您的问题
To take your questions in reverse order
快速刷新也称为增量刷新.那应该为您提供区别的线索.完全刷新从头开始重建整个MVIEW，而快速刷新仅应用DML对馈送器表执行的更改.
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).
为了执行FAST刷新，您需要适当的MVIEW LOG.这样可以跟踪对基础表数据的更改，从而使Oracle可以将 delta 有效地应用于物化视图，而不是查询整个表.
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 delta to the materialized view, rather than querying the whole table.
至于语法，这是基础知识:
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>
ON COMMIT子句意味着MVIEW是事务性刷新的(与ON DEMAND相反，后者是批量刷新的常规内容). REFRESH子句指定是应用增量刷新还是完整刷新.有一些查询类别强制使用COMPLETE刷新，尽管对于每个新版本的Oracle而言，这些查询似乎都会减少.
The ON COMMIT clause means that the MVIEW is refreshed transactionally (as opposed to ON DEMAND which is regular refresh in bulk). The REFRESH clauses specifies whether to apply incremental or complete refreshes. There are some categories of query which force the use of COMPLETE refresh, although these seem to diminish with each new version of 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>
新记录如何?
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 Fiddle，db fiddle)不允许实例化视图.我已经在Oracle SQL Live上发布了一些东西(需要免费的Oracle帐户):我正在等待Oracle的批准，它将在到达时更新此问题.
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.
这篇关于更改餐桌时更新物化视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
