Oracle性能调优之物化视图用法简介

[TOC]

一、物化视图简介

物化视图分类 物化视图分类，物化视图语法和as后面的sql分为：

• (1) 基于主键的物化视图(主键物化视图)
• (2)基于Rowid的物化视图(Rowid物化视图)

本博客介绍一下Oracle的物化视图，物化视图(Materialized view)是相对与普通视图而已的，普通视图是伪表，功能没那么多，而物化视图创建是需要占用一定的存储空间的，物化视图常被应用与调优一些列表SQL查询，物化视图的基本语法：

 create materialized view [视图名称]
     		build immediate | deferred
     		refresh fase | complete | force
     		on demand | commit
          start with [start time]
          next [next time]
          with primary key | rowid //可以省略，一般默认是主键物化视图
          as [要执行的SQL]

ok，解释一下这些语法用意：

build immediate | deferred (视图创建的方式)：

• (1) immediate：表示创建物化视图的时候是生成数据的；
• (2) deferre：就相反了，只创建物化视图，不生成数据

refresh fase | complete | force (视图刷新的方式)：

• (1) fase：增量刷新，也就是距离上次刷新时间到当前时间所有改变的数据都刷新到物化视图，注意，fase模式必须创建视图日志
• (2) complete：全量更新的，complete方式相当于创建视图重新全部查一遍
• (3) force：视图刷新方式的默认方式，当增量刷新可用则增量刷新，当增量刷新不可用，则全量刷新，一般不要用默认方式

on demand | commit start with ... next ...(视图刷新时间)：

• (1) demand：根据用户需要刷新时间，也就是说用户要手动刷新
• (2) commit：事务一提交，就自动刷新视图
• (3) start with：指定首次刷新的时间，一般用当前时间
• (4) next：物化视图刷新数据的周期，格式一般为“startTime+时间间隔”

二、实践：创建物化视图

上面是物化视图主要语法的简介，下面可以实践一下，创建一个主键物化视图

ps:创建一个名称为MV_T的物化视图，视图创建完成是生成数据的，增量刷新，根据用户需要刷新，每隔两天刷新一次视图

create materialized view MV_T
  build immediate
  refresh fast
  on demand
  start with sysdate
  next sysdate + 2
  as select * from sys_user;

可能遇到问题： (1)、ORA-12014: 表 不包含主键约束条件

SQL> create materialized view mv_t
  2  build immediate
  3  refresh fast
  4  on demand
  5  start with sysdate
  6  next sysdate + 2
  7  as select * from sys_user;
as select * from sys_user;
第 7 行出现错误:
ORA-12014: 表 'SYS_USER' 不包含主键约束条件

这是因为as SQL语句的表没创建主键，而是使用的是基于表的物化视图，解决方法是新建主键

(2)、ORA-23413: 表不带实体化视图日志

SQL> create materialized view mv_t
  2  build immediate
  3  refresh fast
  4  on demand
  5  start with sysdate
  6  next sysdate + 2
  7  as select * from sys_user;
as select * from sys_user;
第 7 行出现错误:
ORA-23413: 表 "T_BASE"."SYS_USER" 不带实体化视图日志

这是因为refresh方式用fast方式，fast增量方式必须创建视图日志

create materialized view log on [表名];

删除视图日志：

drop materialized view log on [表名]

假如是基于Rowid的物化视图，就可以用这种方法：

create materialized view log on [表名] with rowid;

附录：物化视图常用SQL

• 删除物化视图：

drop materialized view [视图名称];

• 查看物化视图：

select mv.* from user_mviews mv where mv.MVIEW_NAME = [视图名称];

• 查看物化视图列：

select sg.segment_name, sg.bytes, sg.blocks from user_segments sg where sg.segment_name = [视图名称]; 

• 手动刷新物化视图：

exec dbms_mview.refresh([视图名称]);