数据库
2. Oracle 数据库
视图和物化视图

视图和物化视图

视图和物化视图

定义

视图

普通视图是虚拟表，应用的局限性大，任何对视图的查询，Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高，并没有实质上的好。

物化视图

物化视图是一种特殊的物理表，物化视图Materialized View是相对普通视图而言的。

普通视图和物化视图的区别

普通视图和物化视图根本就不是一个东西，说区别都是硬拼到一起的。 首先明白基本概念，普通视图是不存储任何数据的，他只有定义，在查询中是转换为对应的定义SQL去查询，而物化视图是将数据转换为一个表，实际存储着数据，这样查询数据，就不用关联一大堆表，如果表很大的话，会在临时表空间内做大量的操作。

普通视图的三个特征：

  1. 是简化设计，清晰编码的东西，他并不是提高性能的，他的存在只会降低性能。他的存在是为了在设计上的方便性。

    如一个视图7个表关联，另一个视图8个表，程序员不知道，觉得很方便，把两个视图关联再做一个视图，那就惨了

  2. 其次，是安全，在授权给其他用户或者查看角度，多个表关联只允许查看，不允许修改，单表也可以同WITH READ ONLY来控制，当然有些项目基于视图做面向对象的开发，即在视图上去做INSTAND OF触发器，虽然开发上方便，但是未必是好事。

  3. 从不同的角度看不同的维度，视图可以划分维度和权限，并使多个维度的综合，也就是你要什么就可以从不同的角度看，而表是一个实体的而已，一般维度较少（如：人员表和身份表关联，从人员表可以查看人员的维度统计，从身份看，可以看不同种类的身份有那些人或者多少人），其次另一个如系统视图USER_TABLE、TAB、USER_OBJECTS这些视图，不同的用户下看到的肯定是不一样的，看的是自己的东西。

物化视图呢，用于OLAP系统中，当然部分OLTP系统的小部分功能未了提高性能会借鉴一点点，因为表关联的开销很大，所以在开发中很多人就像把这个代价交给定期转存来完成，ORACLE当然也提供了这个功能，就是将视图（或者一个大SQL）的信息转换为物理数据存储，然后提供不同的策略：定时刷还是及时刷、增量刷还是全局刷等等可以根据实际情况进行选择，总之差的是表，不是视图。

1. 物化视图的类型：

ON DEMAND和ON COMMIT 二者的区别在于刷新方法的不同

  • ON DEMAND顾名思义，仅在该物化视图需要被刷新了，才进行刷新REFRESH，即更新物化视图，以保证和基表数据的一致性
  • ON COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。

1.1 ON DEMAND物化视图

物化视图的创建本身是很复杂和需要优化参数设置的，特别是针对大型生产数据库系统而言。 但Oracle允许以这种最简单的，类似于普通视图的方式来做，所以不可避免的会涉及到默认值问题。 也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的。

1.2 ON COMMIT物化视图

ON COMMIT物化视图的创建，和上面创建ON DEMAND的物化视图区别不大。因为ON DEMAND是默认的，所以ON COMMIT物化视图，需要再增加个参数即可。 需要注意的是，无法在定义时仅指定ON COMMIT，还得附带个参数才行。 创建ON COMMIT物化视图：

create materialized view mv_name refresh force on commit as select * from table_name

备注：实际创建过程中，基表需要有主键约束，否则会报错（ORA-12014）

2. 物化视图的特点：

  1. 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，这通过其可以被user_tables查询出来，而得到佐证；
  2. 物化视图也是一种段segment，所以其有自己的物理存储属性；
  3. 物化视图会占用数据库磁盘空间，这点从user_segment的查询结果，可以得到佐证； 创建语句：create materialized view mv_name as select * from table_name 默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。

3. 物化视图的刷新

刷新Refresh：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。 刷新的模式有两种：ON DEMAND和ON COMMIT。 刷新的方法有四种：FAST、COMPLETE、FORCE和NEVER。

  • FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。
  • COMPLETE刷新对整个物化视图进行完全的刷新。
  • 如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。
  • NEVER指物化视图不进行任何刷新。

3.1 物化视图的数据怎么随着基表而更新？

Oracle提供了两种方式，手工刷新 和 自动刷新，默认为手工刷新。也就是说，通过我们手工的执行某个Oracle提供的系统级存储过程或包，来保证物化视图与基表数据一致性。这是最基本的刷新办法了。

自动刷新，其实也就是Oracle会建立一个job，通过这个job来调用相同的存储过程或包，加以实现。 ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别，即前者不刷新(手工或自动)就不更新物化视图，而后者不刷新也会更新物化视图，——只要基表发生了COMMIT。

创建定时刷新的物化视图：

指定物化视图每天刷新一次:

create materialized view mv_name refresh force on demand start with sysdate next sysdate+1
-- 指定物化视图每天刷新一次

上述创建的物化视图每天刷新，但是没有指定刷新时间，如果要指定刷新时间：

create materialized view mv_name refresh force on demand start with sysdate next to_date(
  concat( to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'), 'dd-mm-yyyy hh24:mi:ss'
)
--每天晚上10：00定时刷新一次

4. 物化视图具有表一样的特征

所以可以像对表一样，我们可以为它创建索引，创建方法和对表一样。

5.物化视图的删除：

虽然物化视图是和表一起管理的，但是在经常使用的PLSQL工具中，并不能用删除表的方式来删除

drop materialized view mv_name
-- 在表上右键选择`drop`并不能删除物化视图，可以使用语句来实现：

参考文章：

Oracle中的逻辑地址和逻辑文件