当底层表发生变化时更新物化视图

Question

我有一个这样定义的物化视图：

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';

我编写为一种缓存：源表很大，但不同对的数量相当小。我需要这些对才能将它们与其他表连接起来。到目前为止一切都很好：它绝对加快了查询速度。

但我想确保视图不包含过时的数据。底层表每月修改四五次，但我不一定知道什么时候。我知道可以定义物化视图，以便在源表更改时它会更新。然而，文档变得相当复杂。

1. 我需要的确切语法是什么 使用？

2. 我需要创建一个物化的 查看日志？

3. 快和快有什么区别 和完全刷新？

Answer 1

以相反的顺序回答问题

快速刷新也称为增量刷新。这应该可以让您了解其中的差异。 COMPLETE 刷新从头开始重建整个 MVIEW，而 FAST 刷新仅应用对馈送表执行的 DML 的更改。

为了执行快速刷新，您需要适当的 MVIEW LOG。这会跟踪基础表数据的更改，从而使 Oracle 能够有效地将增量应用于物化视图，而不是查询整个表。

至于语法，以下是基础知识：

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 的每个新版本而减少。

快速测试看看它是否有效......

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>

您可以在 SQL 参考。还值得阅读 数据仓库指南中的物化视图章节。

---

尽管下面的评论者对此表示担忧，但这确实像广告中所说的那样有效。不幸的是，通常发布演示的地方（SQL Fiddle、db<>fiddle）不允许物化视图。我已经在 Oracle SQL Live 上发布了一些内容（需要免费的 Oracle 帐户）：我正在等待 Oracle 批准它，并将在收到后更新此问题。

Answer 2

快速刷新只会将更改的数据插入/更新/删除到物化视图中。完全刷新将清空物化视图，然后复制所有行。

“提交时”意味着只要在主表中提交更改，物化视图就会刷新。所以你当前的语法将非常低​​效。每次有人更改 foo 中的任何行时，m_foo 都会被截断，然后 foo 表中的每一行都会被插入。

您可以通过快速刷新做得更好，其中只有 foo 中修改的行才会发送到 m_foo。这为您提供了一致性，而无需太多开销。

使用主键在 foo 上创建物化视图日志； -- 假设你有一个主键，你应该
创建物化视图 m_foo 在提交时快速刷新为 \;

如果您使用数据库链接，或者拥有 foo 的模式不是拥有 m_foo 的模式，那么授权和同义词还有一些额外的微妙之处。