sql - 表与物化 View

Question

这与我问的上一个问题Saved View with a timestamp expression有关，将问题存储在(非具体化的) View 中。用户执行以下操作时如何存储数据，然后如何检索数据:

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()表达式另存为值以存储，还是在查询时针对物化 View 评估了任何函数？物化 View 是否与表相同，表在存储级别进行了某种优化/刷新，还是我错过了表？
这里的这篇文章建议(至少从功能上来说)一个物化 View 可以被模拟为带有触发器的表: https://www.materialized.info/。

Answer 1

这将取决于您使用的数据库平台。看到您用'oracle'标记了它，这就是Oracle中发生的情况。
在实例化 View 实例化时评估“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.

无论您查询实例化 View 的时间如何，“D”列均不会更改。如果我现在为物化 View 发出刷新命令，则实际上是在重新运行定义查询，因此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物化 View (包括定义和物化 View 日志)的处理在此处介绍
https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/basic-materialized-views.html#GUID-A7AE8E5D-68A5-4519-81EB-252EAAF0ADFF
以及物化 View 的一些更高级的主题(分区，索引等)以及它们如何与自动查询重写有关
https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/advanced-materialized-views.html#GUID-F7394DFE-7CF6-401C-A312-C36603BEB01B