基于表结构创建物化视图

目录

• 使用场景：
• 1. 源端
  • 1.1 创建表结构
  • 1.2 创建物化视图日志
• 2. 目标端
  • 2.1 创建空表
  • 2.2 基于空表创建物化视图
  • 2.3 刷新物化视图
  • 2.4 重建物化视图

使用场景：

1. 如果发现创建物化视图的DDL始终执行不通过，并且找不到执行计划。从v$session 中可以获取执行计划。

1. 源端

1.1 创建表结构

-- Create table
create table EMP_SOURCE
(
  empno    NUMBER(4) not null,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

alter table EMP_SOURCE
  add constraint PK_EMP_S primary key (EMPNO);

1.2 创建物化视图日志

CREATE MATERIALIZED VIEW LOG ON emp_source WITH PRIMARY key ; 

2. 目标端

2.1 创建空表

CREATE TABLE mv_emp_source AS select * from emp_source where 1=2 ; 

2.2 基于空表创建物化视图

需要满足：

1. 空表名称跟物化视图名称一致
2. 物化视图查询到的字段，跟空表字段一致

空表尽量包含主键，基于预定义表的物化视图不会主动添加主键和索引。但是支持增量刷新。

CREATE MATERIALIZED VIEW mv_emp_source 
ON PREBUILT TABLE REFRESH FORCE NEXT NULL 
AS 
SELECT * FROM emp_source; 

2.3 刷新物化视图

基于预创建表的物化视图，创建时不会刷新。手工刷新

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

2.4 重建物化视图

DROP MATERIALIZED VIEW MV_EMP_SOURCE; 

删除物化视图后：

1. 预定义的表不会删除
2. 预定义表中的数据不会删除