oracle物化视图的刷新命令_物化视图增量刷新

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全家桶1年46，售后保障稳定

物化视图（MATERIALIZED VIEW）是一个包含查询结果的数据库对象。

普通视图仅包含其定义和被引用表的元数据，并不实际存储数据，查询数据时需要通过视图再去主表中获取数据。但是当需要查询的数据字段过多时，普通视图的效率会急剧下降。物化视图将经常使用的数据拷贝并存储下来，在查询时就可以直接返回数据。本质上是一个物理表，会占用磁盘空间。

物化视图第一个应用场景就是对本地经常使用数据的访问，通过构建物化视图会比直接访问原表或普通视图速度会有显著提升。其次可以使用物化视图在本地节点上维护远程数据的副本，从而大大提升数据读取速度。第三个应用场景是在数据仓库中，通过预先计算并保存表连接或聚集等耗时较多的操作的结果，避免在每次查询时进行这些耗时的操作，从而快速的得到结果。在数据仓库中，还经常使用查询重写机制（query rewrite），不需要修改原有的查询语句，Oracle会自动选择合适的物化视图进行查询，完全对应用透明。

可以通过如下方式创建：

create materialized view mv_name 
	as select * from table_name;

Jetbrains全家桶1年46，售后保障稳定

1、预制表

还可以通过ON PREBUILT TABLE 创建基于现有表的物化视图，有利在数据仓库环境中注册大型物化视图。使用该子句有如下两个条件：

1. 创建的物化视图的schema下必须要有一个与物化视图同名的表，更新物化视图，同名表也会被更新。
2. 与物化视图同名的表结构，必须与创建物化视图select语句字段名相同，且一一对应。

# 首先创建一张与物化视图同名的表
CREATE TABLE sales_sum_table
	(month VARCHAR2(8), state VARCHAR2(40), sales NUMBER(10,2));
# 创建物化视图和预制表，并且查询结果列的数量和名字都要与上面的物理表相同
CREATE MATERIALIZED VIEW sales_sum_table   ON PREBUILT TABLE 
	AS SELECT t.calendar_month_desc AS month,              
	c.cust_state_province AS state,             
	SUM(s.amount_sold) AS sales;.

指定 WITH REDUCED PRECISION 以授权允许在表或物化视图列的精度与子查询返回的精度不完全匹配时导致的精度损失

虽然通过物化视图可以快速地查询到数据，但是由于对主表的数据进行了复制，当基表发生了DML操作后，物化视图需要进行刷新从而和基表保持同步

2、物化视图日志

当对主表数据进行DML更改时，Oracle数据库将描述这些更改的行存储在物化视图日志中，然后使用日志对物化视图进行刷新。这个过程称为增量或快速刷新。如果没有物化视图日志，Oracle数据库必须重新执行物化视图查询以刷新物化视图，这个过程称为完全刷新。通常，快速刷新比完全刷新花费的时间少。

物化视图日志和主表存放在一起，一张主表对应一个日志，如果视图涉及到了join操作，那么涉及到的每张表都要创建对应的日志

2.1 主键物化视图

主键物化视图记录主表被更新记录的主键，允许在不影响FAST刷新的前提下，重新组织物化视图的主表

主键物化视图的主表必须包含启用中的主键约束。物化视图的定义查询必须直接指定所有主键列，并且不能将主键列指定为函数的参数，例如UPPER函数

对象物化视图不能使用主键。 Oracle 数据库会隐式刷新 WITH OBJECT ID 物化的对象。

# 在employees表上创建主键物化视图日志
CREATE MATERIALIZED VIEW LOG ON employees
	WITH PRIMARY KEY;

2.2 ROWID物化视图

ROWID物化视图记录主表被更新记录的ROWID，如果物化视图不包括主表的所有主键列，则需要使用 Rowid 实体化视图。

Rowid物化视图必须基于单表，如果定义查询中有多个主表，WITH ROWID 子句不起作用

在主表重组后，直到执行完完全刷新，Rowid 物化视图不能进行快速刷新(FAST)

使用rowid不能包含以下任何内容：

• distinct 或 aggregate函数
• GROUP BY或CONNECT BY子句
• 子查询
• 联接查询
• set操作

Oracle有两种记录日志的方式：

1. 默认使用基于timestamp的方式来记录操作提交的时间，在进行刷新时需要一些设置操作，因此执行速度慢一些
2. 基于SCN(system change number)来记录commit的先后顺序，系统通过累加递增的数字来记录操作执行的先后。通过COMMIT SCN来指定使用该日志。

3、刷新方法

有如下四种刷新的方法：

3.1 FAST

增量刷新，只刷新自上次刷新以后进行的修改。

常规DML的修改会被存储在与主表关联的物化视图日志中，direct-path INSERT操作的变化会被存储direct loader日志中

使用限制：

1. 在使用create语句前，必须在物化视图的主表中创建物化视图日志materialized view logs；而对于direct-path
   INSERT操作，Oracle会自动创建 direct loader log

2. 如果query包含分析函数或 XMLTable 函数，则无法使用快速刷新

3.2 COMPLETE

通过执行物化视图定义的query语句，对整个物化视图进行完全的刷新。即使使用了fast刷新，也可以指定进行complete刷新

3.3 FORCE

在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。

4、刷新时机

物化视图的刷新时机有ON COMMIT和ON DEMAND两种，二者不能同时使用，默认的刷新方式是ON DEMAND。也可以使用NEXT手动指定视图刷新频率

4.1 ON COMMIT

每当数据库提交对物化视图的主表进行操作的事务时就会发生刷新，更新物化视图，使得数据和基表一致。

由于刷新操作包含在事务提交过程中，所以采用ON COMMIT会增加事务提交的时间。

使用限制：

1. ON COMMIT 不能和 ON DEMAND、START WITH 或 NEXT同时使用
2. 不支持包含对象类型或 Oracle 提供的类型的物化视图
3. 不支持具有远程表(remote table)的物化视图
4. 不能和主表上的分布式事务同时使用

4.2 ON DEMAND

通过手动启动DBMS_MVIEW刷新程序来对物化视图进行刷新，共有三种DBMS_MVIEW。

使用限制：
ON DEMAND不能和ON COMMIT同时使用，并且会被START WITH 或 NEXT子句覆盖。

4.3 START WITH & NEXT

使用START WITH为第一次自动刷新时间指定一个日期时间表达式，然后通过NEXT指定自动刷新间隔的datetime表达式，计算结果必须为未来的时间。

如果省略 START WITH 值，则数据库通过评估与物化视图的创建时间相关的 NEXT 表达式来确定第一次自动刷新时间。 如果省略 NEXT 值，则数据库仅刷新物化视图一次

create materialized view mv_emp_pk
  2  build deferred
  3  refresh fast    				#增量刷新
  4  start with sysdate				#首次刷新的时间，当前时间开始
  5  next sysdate + 7				#刷新的周期，每7天一次
  6  with primary key
  7  as select * from emp;

4.4 NEVER REFRESH

NEVER REFRESH 防止物化视图被任何 Oracle 数据库刷新机制或打包过程刷新。 Oracle 将忽略这些操作对物化视图上的更新操作。如果要反转此子句，可以使用 ALTER MATERIALIZED VIEW … REFRESH

5、其他特性

USING CONSTRAINTS

USING … CONSTRAINTS 子句让 Oracle 数据库在刷新操作期间选择更多的重写选项，从而提高刷新执行的效率。

FOR UPDATE

主键物化视图如果指定了FOR UPDATE语句可以进行更新。当修改发生后，修改的数据以行级为单位被传播，每行数据由主键确定。

通过select子句选取所需要的数据行建立物化视图

CREATE MATERIALIZED VIEW foreign_customers FOR UPDATE
	AS SELECT * FROM sh.customers@remote cu
	WHERE EXISTS (
		SELECT * FROM sh.countries@remote co
		WHERE co.country_id = cu.country_id);

BUILD

通过build子句对物化视图的填充时机进行指定：

1. Build immediate:在创建物化视图的同时根据主表生成数据，默认选项
2. Bulid deferred:在创建时不生成数据，以后可以采用全量刷新

QUERY REWRITE

查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据，通过ENABLE QUERY REWRITE开启查询重写功能