表与物化视图

Question

这与我之前问过的关于将信息存储在(非物化)视图中的问题带有时间戳表达式的保存视图有关。当用户这样做时，如何存储并检索数据：

CREATED MATERIALIZED VIEW mv AS SELECT person_id, name, NOW() as now FROM table
# is this more-or-less the same as:
# CREATED TABLE tb AS SELECT person_id, name, NOW() as now FROM table
#  "AND UPDATE EVERY..."

NOW()表达式是作为值保存到存储中，还是在查询时为物化视图计算任何函数？物化视图是否与在存储级别进行某种优化/刷新的表相同，还是我错过了这方面的工作呢？

这里的文章建议(至少从功能上说)物化视图可以模拟为带有触发器的表：https://www.materialized.info/。

Answer 1

这将取决于您正在使用的数据库平台。当你用“甲骨文”给它贴上标签时，下面是甲骨文中发生的事情。

"now“值(在本例中为'sysdate')是在物化视图实例化时计算的。这很容易演示

SQL> create materialized view MV as select e.*, sysdate d from emp e;

Materialized view created.

SQL> select * from mv;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO D
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -------------------
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20 21/10/2020 12:18:26
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30 21/10/2020 12:18:26
      7521 WARD       SALESMAN        7698 22/02/1981 00:00:00       1250        500         30 21/10/2020 12:18:26
      7566 JONES      MANAGER         7839 02/04/1981 00:00:00       2975                    20 21/10/2020 12:18:26
      7654 MARTIN     SALESMAN        7698 28/09/1981 00:00:00       1250       1400         30 21/10/2020 12:18:26
      7698 BLAKE      MANAGER         7839 01/05/1981 00:00:00       2850                    30 21/10/2020 12:18:26
      7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10 21/10/2020 12:18:26
      7788 SCOTT      ANALYST         7566 09/12/1982 00:00:00       3000                    20 21/10/2020 12:18:26
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10 21/10/2020 12:18:26
      7844 TURNER     SALESMAN        7698 08/09/1981 00:00:00       1500                    30 21/10/2020 12:18:26
      7876 ADAMS      CLERK           7788 12/01/1983 00:00:00       1100                    20 21/10/2020 12:18:26
      7900 JAMES      CLERK           7698 03/12/1981 00:00:00        950                    30 21/10/2020 12:18:26
      7902 FORD       ANALYST         7566 03/12/1981 00:00:00       3000                    20 21/10/2020 12:18:26
      7934 MILLER     CLERK           7782 23/01/1982 00:00:00       1300                    10 21/10/2020 12:18:26

[wait 10 seconds]

14 rows selected.

SQL> select * from mv;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO D
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -------------------
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20 21/10/2020 12:18:26
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30 21/10/2020 12:18:26
      7521 WARD       SALESMAN        7698 22/02/1981 00:00:00       1250        500         30 21/10/2020 12:18:26
      7566 JONES      MANAGER         7839 02/04/1981 00:00:00       2975                    20 21/10/2020 12:18:26
      7654 MARTIN     SALESMAN        7698 28/09/1981 00:00:00       1250       1400         30 21/10/2020 12:18:26
      7698 BLAKE      MANAGER         7839 01/05/1981 00:00:00       2850                    30 21/10/2020 12:18:26
      7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10 21/10/2020 12:18:26
      7788 SCOTT      ANALYST         7566 09/12/1982 00:00:00       3000                    20 21/10/2020 12:18:26
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10 21/10/2020 12:18:26
      7844 TURNER     SALESMAN        7698 08/09/1981 00:00:00       1500                    30 21/10/2020 12:18:26
      7876 ADAMS      CLERK           7788 12/01/1983 00:00:00       1100                    20 21/10/2020 12:18:26
      7900 JAMES      CLERK           7698 03/12/1981 00:00:00        950                    30 21/10/2020 12:18:26
      7902 FORD       ANALYST         7566 03/12/1981 00:00:00       3000                    20 21/10/2020 12:18:26
      7934 MILLER     CLERK           7782 23/01/1982 00:00:00       1300                    10 21/10/2020 12:18:26

14 rows selected.

"D“列与查询物化视图的次数无关。如果我现在为物化视图发出一个refresh命令，我们实际上是在重新运行定义的查询，因此sysdate (因此列"D")将作为刷新的时刻。

SQL> exec dbms_mview.refresh('MV')

PL/SQL procedure successfully completed.

SQL> select * from mv;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO D
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -------------------
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20 21/10/2020 12:19:12
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30 21/10/2020 12:19:12
      7521 WARD       SALESMAN        7698 22/02/1981 00:00:00       1250        500         30 21/10/2020 12:19:12
      7566 JONES      MANAGER         7839 02/04/1981 00:00:00       2975                    20 21/10/2020 12:19:12
      7654 MARTIN     SALESMAN        7698 28/09/1981 00:00:00       1250       1400         30 21/10/2020 12:19:12
      7698 BLAKE      MANAGER         7839 01/05/1981 00:00:00       2850                    30 21/10/2020 12:19:12
      7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10 21/10/2020 12:19:12
      7788 SCOTT      ANALYST         7566 09/12/1982 00:00:00       3000                    20 21/10/2020 12:19:12
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10 21/10/2020 12:19:12
      7844 TURNER     SALESMAN        7698 08/09/1981 00:00:00       1500                    30 21/10/2020 12:19:12
      7876 ADAMS      CLERK           7788 12/01/1983 00:00:00       1100                    20 21/10/2020 12:19:12
      7900 JAMES      CLERK           7698 03/12/1981 00:00:00        950                    30 21/10/2020 12:19:12
      7902 FORD       ANALYST         7566 03/12/1981 00:00:00       3000                    20 21/10/2020 12:19:12
      7934 MILLER     CLERK           7782 23/01/1982 00:00:00       1300                    10 21/10/2020 12:19:12

14 rows selected.

SQL>

但每个平台都可能有自己的特点。

本文将讨论Oracle物化视图的处理，包括定义和物化视图日志。

https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/basic-materialized-views.html#GUID-A7AE8E5D-68A5-4519-81EB-252EAAF0ADFF

一些更高级的物化视图主题(分区、索引等)以及它们如何与自动查询重写相关。

https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/advanced-materialized-views.html#GUID-F7394DFE-7CF6-401C-A312-C36603BEB01B

Answer 2

我在Oracle中广泛使用了物化视图，我可以从这个角度回答。我可以想象，对于其他数据库来说，一般的原则也是一样的，但也会有微小的变化。

首先，与普通视图相比，物化视图是物理存储。普通视图只存储查询，并在运行时执行查询。因此，如果您将当前时间戳存储在Mat视图中，除非刷新视图，否则它将显示旧值。在oracle中，有几种物化视图的刷新策略，如下所述-

1.手动刷新：MView可以根据需要使用标准包dbms_snapshot.refresh_mview刷新

2.自动刷新：在使用“On”对MView的表进行任何更改后，可以立即刷新MView

自动刷新可以有多种类型-

2.1.完成(完全)--每当修改基表时，MView将首先被截断，并将与数据一起加载。顾名思义，它是完全更新的。

2.2.快速-每当修改基表时，只更新/插入MView中的更新记录。我们需要“mvlog”文件来进行“快速”刷新。

2.3.强制-它将首先尝试进行“快速”刷新。如果由于某些原因，“快速”刷新失败了，那么它将执行“完全”刷新。

因此，结构物化视图与物理表完全相似。那么问题是为什么我们要创建MV？&创建物化视图的好处到底是什么？有趣的部分来了。

通常，当基表包含大量数据时，计算所需的聚合或计算这些表之间的连接既昂贵又耗时。在这种情况下，查询可能需要几分钟甚至几个小时。因此，在物化视图中，您可以存储预计算的聚合和连接，当您尝试在查询中执行聚合SQL查询或使用这些相同的联接时，数据库引擎实际上不会在运行时执行查询，而是将获取存储在Mat视图上的预计算结果并将这些结果返回给客户端，这会更快，因为这样可以防止这些计算在运行时发生。那么，引擎是如何做到这一点的呢？- Oracle数据库使用了一个非常强大的进程，称为查询重写，可以使用物化视图快速地回答查询。

查询经过几次检查，以确定它是否是查询重写的候选项。如果查询失败，则查询将应用于详细表，而不是物化视图。这在响应时间和处理能力方面可能是昂贵的。

优化器使用两种不同的方法来识别何时根据物化视图重写查询。第一种方法是将查询的SQL文本与物化视图定义的SQL文本匹配。如果第一个方法失败，优化器将使用更通用的方法，在该方法中比较查询视图和物化视图之间的联接、选择、数据列、分组列和聚合函数。

这实际上是物化视图的一个非常有用的应用程序，可以加快查询5X-100X的次数。我可以给出一个真实的例子，在我从事的一个项目中，有几个聚合报告需要每周准备一次，如果我们在数据仓库表上使用聚合查询，我们需要花费几个小时来构建这些报告。相反，我们稍后所做的是，在每周一次的数据仓库负载之后，我们通常对Mat视图进行全面刷新(使用自动刷新功能)，比如每周五晚上，并启用对那些物化视图的查询重写(具有预聚合/预计算结果)。然后，我们的报告查询过去是通过一个自动化的过程在周末开始的，并且由于这种在Mat视图中使用查询重写功能的预受害结果的优化，我们的报告构建时间变得更快了，我们能够在周一早上之前验证并向涉众提交所有的报告，而当我们没有Mat视图的时候，我们总是保持警惕，因为构建过去需要花费几个小时/有时是几天时间，取决于报告。同样，对于非聚合报表，我们也使用了相同的过程，这涉及到许多维度和事实表的连接。因此，在那些物化视图中，我们用非规范化格式存储数据，这样在构建报表时，不需要在运行时执行联接。聚合前的优化获得了50倍-100倍的收益，而非规范化的物化视图用于提供5x -30倍的数据，这取决于几个因素。

您可以在Oracle文档中读取实现和语法级别的详细信息。如果需要的话，我们很乐意提供更多的细节，但只想分享一个真实的第一手用例，以了解如果我们能够在正确的场景中使用它，它会有多大的用处。下面是Oracle中的示例语法以供参考。

CREATE MATERIALIZED VIEW department_mv
refresh complete on commit --Automatic Complete refresh (as described above)
enable query rewrite -- this feature enables optimization (as described above)
as
SELECT deptno, dept_name, SUM(salary)
FROM department
GROUP BY deptno, dept_name;

Answer 3

简单地说，SQL中的物化视图是一个物理构造，是物理存储在磁盘上的。但是视图只是逻辑结构，是在查询中需要它的地方创建的。

创建物化视图时，Oracle数据库将在物化视图的架构中创建一个内部表和至少一个索引，并可能创建一个视图。

以下是物化视图的语法：

Create materialized view View_Name

Build [Immediate/Deffered]

Refresh [Fast/Complete/Force]

on [Commit/Demand]

as Select ..........;

当DML在主表中执行时，甲骨文会在MV日志中存储定义更改的行，并使用它刷新称为fast .Its的MV refresh.You可以检查mlog$_tablename中的行为。MV也被称为快照。使用select name,table_name,refresh_method from user_snapshots，您可以检查状态。

反映MV：execute DBMS_MVIEW.REFRESH('name_of the view');中母版表的变化

对于单个快照：execute DBMS_SNAPSHOT.REFRESH( 'v_materialized_foo_tbl','f')；

过程下面的刷新过程刷新快照列表。

语法

DBMS_SNAPSHOT.REFRESH (
   { list                 IN     VARCHAR2,
   | tab                  IN OUT DBMS_UTILITY.UNCL_ARRAY,},...);

列表:要刷新的以逗号分隔的快照列表。选项卡:指示如何刷新列出的快照的一系列刷新方法。F' or f‘表示快速刷新，?' indicates force refresh, C’或c' indicates complete refresh, and A‘或a表示始终刷新，

下图显示了总体构想：

​

因此，现在()的值是通过execute DBMS_MVIEW.REFRESH()或DBMS_SNAPSHOT.REFRESH对其进行刷新时调用的。

此外，在MV上使用触发器并不是一个好做法，尽管mysql可以在只读取MV的情况下对其进行.Be。Oracle不支持触发器内的Execute命令(刷新MV)，因为触发器本身是一个事务，而在触发器中提交过量的命令使它在一个事务中提交两个。

插入触发器是否需要提交语句？

只读物化视图上的触发器可能看起来工作正常，但您不能依赖它们工作。

对物化视图中的行的更新可以作为DELETE+INSERT进行。

物化视图的刷新可能涉及每一行的删除+插入。

刷新可能涉及截尾加上任何行的直接路径加载。

(最后两种情况可能会在某个时候发生，你会失去过去触发的任何东西)“- https://asktom.oracle.com/pls/apex/asktom.search%3Ftag%3Dtriggers-on-materialized-views#:~:text=Triggers%20on%20a%20read%20only,delete%20%2B%20insert%20of%20every%20row。