test,测试物化视图新增时间戳字段

使用触发器维护一个时间戳字段，这是ETL取数据时的常用手段。能优化不少sql，下面演示详细过程。

• 使用老演员scott 创建mvlog和mv

SQL> CREATE MATERIALIZED VIEW LOG ON emp_source WITH PRIMARY KEY;
SQL> CREATE MATERIALIZED VIEW MV_EMP_SOURCE
BUILD IMMEDIATE REFRESH FAST
ON DEMAND
AS SELECT * FROM EMP_SOURCE; 

• 两个表数据都为空

SQL> set lines 200 pages 200
SQL> SELECT * FROM EMP_SOURCE;

no rows selected

SQL> SELECT * FROM MV_EMP_SOURCE;

no rows selected

• 添加新字段，并写入数据刷新

SQL> alter table MV_EMP_SOURCE add deal_time date ;

Table altered.

SQL> insert into emp_source select * from emp where empno=7839;

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM EMP_SOURCE;

     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81             5000                    10

SQL> SELECT * FROM MV_EMP_SOURCE;

no rows selected

SQL>
SQL> BEGIN
  dbms_mview.refresh(list => 'mv_emp_source');
END;
/ 

SQL> SELECT * FROM MV_EMP_SOURCE;

     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM     DEPTNO DEAL_TIME
---------- ---------- --------- ---------- --------------- ---------- ---------- ---------- ---------------
      7839 KING       PRESIDENT            17-NOV-81             5000                    10

• 创建触发器，重新写入数据并刷新mv

SQL> create or replace trigger trg_update_mv_emp
  before insert or update on mv_emp_source
  for each row
begin
  :new.deal_time := sysdate;
end trg_update_mv_emp;
/


SQL> insert into emp_source select * from emp where empno=7566;

1 row created.

SQL> BEGIN
  dbms_mview.refresh(list => 'mv_emp_source');
END;
/

• mv_emp_source的deal_time有一个有值，有一个没值。

SQL> SELECT * FROM EMP_SOURCE;

     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81             5000                    10
      7566 JONES      MANAGER         7839 02-APR-81             2975                    20

SQL> SELECT * FROM MV_EMP_SOURCE;

     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM     DEPTNO DEAL_TIME
---------- ---------- --------- ---------- --------------- ---------- ---------- ---------- ---------------
      7839 KING       PRESIDENT            17-NOV-81             5000                    10
      7566 JONES      MANAGER         7839 02-APR-81             2975                    20 19-NOV-22

SQL> exit;