（原创）在Oracle中建立物化视图

一、工作状况

• 环境：oracle 11g 版本11.2
• 问题：ORA-12054:无法为实体化视图设置ON COMMIT刷新属性
• 处理时考虑的重点：
  • 分配权限
  • 现在建立物化视图日志，所有设计的表都需要建立
  • 建立物化视图及索引
  • 物化视图可以分为按时间更新，或者commit时更新。
  • 按照commit更新，限制更多，必须使用rowid, 不支持标准外连接，join要写成where的方式，外连接只能有一张表，且使用(+)方式

解决方案：

• 为用户testuser分配权限

grant CREATE MATERIALIZED VIEW to testuser;

• COMMIT更新
  在表空间test_view_data中的若干表上进行操作

-- 新建物化日志
create materialized view log on t_ent_base_info tablespace test_view_data with rowid including new values;
create materialized view log on t_ent_ext_info tablespace test_view_data with rowid including new values;
create materialized view log on t_ent_industry_info tablespace test_view_data with rowid including new values;
create materialized view log on t_cde_nation tablespace test_view_data with rowid including new values;
create materialized view log on t_cde_region tablespace test_view_data with rowid including new values;
-- 创建物化视图
create materialized view mv_test_outter_join tablespace test_view_data refresh force on commit 
as
  SELECT base.ent_id,
    base.ent_name,
    base.bank_code,
    base.ent_area,
    base.ent_province,
    industry.industry_id,
    nation.name_chs,
    base.rowid id1,
    industry.rowid id2,
    nation.rowid id3
  FROM t_ent_base_info base, t_ent_industry_info industry, t_cde_nation nation
      WHERE industry.ent_id = base.ent_id
      AND base.ent_area(+) = nation.nation_code

• DEMAND更新

-- 新建物化日志
create materialized view log on t_ent_base_info tablespace test_view_data with primary key;
create materialized view log on t_ent_ext_info tablespace test_view_data with primary key;
create materialized view log on t_ent_industry_info tablespace test_view_data with primary key;
create materialized view log on t_cde_nation tablespace test_view_data with primary key;
create materialized view log on t_cde_region tablespace test_view_data with primary key;
-- 创建物化视图
create materialized view mv_ent_base_ext tablespace test_view_data refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'02:05:00'),'dd-mm-yyyy hh24:mi:ss') as
select base.ent_id,
       base.ent_name,
       base.ent_name_trans,
       base.ent_area,
       base.ent_province,
       base.bank_code,
       ext.pic_others_1,
       ext.import_mode_id
  from t_ent_base_info base join t_ent_ext_info ext
on base.ent_id = ext.ent_id
   and ext.is_valid = '1';

• 创建物化视图索引

create index idx_mv_area on mv_ent_base_ext(ent_id,ent_area) tablespace test_view_idx;
create index idx_mv_province on mv_ent_base_ext(ent_id,ent_province) tablespace test_view_idx;
create index idx_mv_import_a on mv_ent_base_ext(ent_id,import_mode_id,ent_area) tablespace test_view_idx;
create index idx_mv_import_p on mv_ent_base_ext(ent_id,import_mode_id,ent_province) tablespace test_view_idx;
create index idx_mv_name_area on mv_ent_base_ext(ent_name,ent_area) tablespace test_view_idx;
create index idx_mv_base_im_a on mv_ent_base_ext(import_mode_id,ent_area) tablespace test_view_idx;
create index idx_mv_base_im_p on mv_ent_base_ext(import_mode_id,ent_province) tablespace test_view_idx

• 删除物化视图及日志

drop materialized view log on t_cde_region;
drop materialized view mv_ent_base_ext;

• 修改物化视图刷新时间

alter materialized view mv_ent_base_ext refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'02:05:00'),'dd-mm-yyyy hh24:mi:ss');

二、物化视图概述

1.基本概念

• 物化视图是存储了查询结果的本地副本，可以查询表、视图和其他物化视图。
• 物化视图可被认为是一种物理表，但不同于物理表，可根据远程数据刷新，会占据磁盘空间，可以创建索引。
• 物化视图可以用于预先连接多表存储处理耗时较多的操作结果，从而在执行查询时，快速得到结果。

2. 数据
   物化视图创建时生成数据分为两种： build immediate 和 build deferred:

   • build immediate是在 创建物化视图的时候就生成数据
   • build deferred 则在创建时不生成数据，以后根据需要在生成数据

   如果不指定，则默认为 build immediate

3. 刷新模式
   物化视图在创建时有二种刷新模式(refresh mode)：on demand和on commit：

   • on demand 顾名思义，仅在该物化视图需要被刷新了，才进行刷新，更新物化视图，以保证和基表数据的一致性
   • on commit 提交触发，一旦基表有了commit，即事务提交，则立刻刷新，更新物化视图，使得数据和基表一致。一般用这种方法在操作基表时速度会比较慢。

   创建时未指定，则Oracle按 on demand 模式来创建。

4. 如何刷新
   上面说的是 刷新模式 ，针对于如何刷新，则有如下三种 刷新方法 ：

   • 完全刷新（COMPLETE）： 会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。
   • 快速刷新（FAST）： 采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。
   • FORCE方式： 这是默认的数据刷新方式。Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。

   关于快速刷新： Oracle物化视图的快速刷新机制是通过物化视图日志完成的。 Oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的 。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

5. 查询重写（QueryRewrite）
   查询重写是指当对物化视图的基表进行查询时 ， oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。
   分为 enable query rewrite 和 disable query rewrite 两种，对应创建的物化视图是否支持查询重写，默认为 disable query rewrite 。

三、官方文档语法

1.创建物化视图

创建物化视图.png

代码如下：

CREATE MATERIALIZED VIEW [ schema. ] materialized_view
  [ OF [ schema. ] object_type ]
  [ ( { scoped_table_ref_constraint
      | column_alias [ENCRYPT [encryption_spec]]
      }
      [, { scoped_table_ref_constraint
         | column_alias [ENCRYPT [encryption_spec]]
         }
      ]...
    )
  ]
  { ON PREBUILT TABLE
    [ { WITH | WITHOUT } REDUCED PRECISION ]
  | physical_properties materialized_view_props
  }
  [ USING INDEX
    [ physical_attributes_clause
    | TABLESPACE tablespace
    ]...
  | USING NO INDEX
  ]
  [ create_mv_refresh ]
  [ FOR UPDATE ]
  [ evaluation_edition_clause ]
  [ query_rewrite_clause ]
AS subquery ;

2. 创建物化视图日志

创建物化视图日志.png

代码如下：

CREATE MATERIALIZED VIEW LOG ON [ schema. ] table
  [ physical_attributes_clause
  | TABLESPACE tablespace
  | logging_clause
  | { CACHE | NOCACHE }
  ]...
  [ parallel_clause ]
  [ table_partitioning_clauses ]
  [ WITH [ { OBJECT ID
         | PRIMARY KEY
         | ROWID
         | SEQUENCE
         | COMMIT SCN
         }
           [ { , OBJECT ID
             | , PRIMARY KEY
             | , ROWID
             | , SEQUENCE
             | , COMMIT SCN
             }
           ]... ]
    (column [, column ]...)
    [ new_values_clause ]
  ] [ mv_log_purge_clause ] 
;

四、引用

1.Oracle物化视图的创建及使用（一）
2.(转)oracle物化视图连接查询注意事项