ORACLE物化视图

来源：互联网发布：高帮女帆布鞋知乎编辑：程序博客网时间：2024/04/28 08:16

一. 概念

视图是一个虚拟表(也可以认为是一条语句)，基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个物化视图(实体化视图)。

物化视图也是种视图。oracle的物化视图是包括一个查询结果的数据库对象，是远程数据的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以成为快照。

物化视图可以查询表，视图和其他的物化视图。

二. 特点

1. 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，可以通过user_table查询

2. 物化视图也是一种段(segment)，所以有其自己的物理存储属性

3. 物化视图会占用数据库磁盘空间，可以通过user_segments查询

因为物化视图是物理真实存在的，故可以创建索引。

三. 创建

create materialized view view_name
refresh [fast|complete|force]
[
on [commit|demand] |
start with (start_time) next (next_time)

]

AS subquery;

参数的含义：

1. refresh [fast|complete|force] 视图刷新的方式

fast：增量刷新，假设前一次刷新的时间为t1，那么使用fast模式刷新物化视图时，只向视图中添加t1到当前时间段内，主表变化过的数据。为了记录这种变化，建立增量刷新物化视图还需要一个物化视图日志表。create materialized view log on （主表名）。对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用，

complete：全部刷新。相当于重新执行一次创建视图的查询语句。刷新过程会删除表中所有的记录(如果是单表刷新，可能会使用TRUNCATE的方式)，然后根据物化视图中查询语句的定义重新生成物化视图。

force：是默认的数据刷新方式。当可以使用fast模式时，数据刷新将采用fast方式；否则使用complete方式。即oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。

2. MV数据刷新的时间

on demand：在用户需要刷新的时候刷新，就要求用户自己动手去刷新数据(也可以使用Job定时刷新)。为默认选项。

on commit：提交触发，即当主表中有数据提交时，立即刷新MV中的数据。一般这种方法在操作基表时速度会比较慢。

start......：从指定的时间开始，每隔一段时间(由next指定)就刷新一次

3.Build immediate

a.Build immediate：建立物化视图，并使用当前命令执行的数据马上填充视图数据。为默认选项。

b.Build defered：只建立物化视图，在第一次刷新之前不填充数据。

c.No prebuilt table：使用事先已存在的，已含有视图定义中有现有数据的表，而不是建立一个新结构来保存数据

如果是refresh fast on commit或refresh complete on commit创建的，则在基础表提交的时候都会得到刷新。启用或禁用物化视图，需要有query rewrite或global query resrite权限。

四. 查询重写

包括enable query rewrite和disable query rewrite两种

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

默认为disable query rewrite。

五. 具体操作

创建物化视图需要的权限：

grant create materialized view to user_name;

在源表建立物化视图日志：

create materialized view log on test_table

tablespace test_space -- 日志空间

with primary key; -- 指定为主键类型

在目标数据库上创建MATERIALIZED VIEW：

create materialized view mv_materialized_test refresh force on demand start with sysdate next

to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'10:25:00'),'dd-mm-yyyy hh24:mi:ss') as

select * from user_info; --这个物化视图在每天10：25进行刷新

修改刷新时间：

alter materialized view mv_materialized_test refresh force on demand start with sysdate

next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 23:00:00'),'dd-mm-yyyy hh24:mi:ss');

或

alter materialized view mv_materialized_test refresh force on demand start with sysdate

next trunc(sysdate,'dd')+1+1/24; -- 每天1点刷新

建立索引：

create index IDX_MMT_IU_TEST

on mv_materialized_test(ID,UNAME)

tablespace test_space;

删除物化视图及日志：

drop materialized view log on test_table; --删除物化视图日志：

drop materialized view mv_materialized_test; --删除物化视图

0 0