(原创)在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.基本概念

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

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

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

  2. 刷新模式
    物化视图在创建时有二种刷新模式(refresh mode):on demandon commit

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

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

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

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

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

  4. 查询重写(QueryRewrite)
    查询重写是指当对物化视图的基表进行查询时 , oracle会自动判断能否通过查询物化视图来得到结果,如果可以,则避免了聚集或连接操作,而直接从已经计算好的物化视图中读取数据。
    分为 enable query rewritedisable 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物化视图连接查询注意事项

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容

  • ORA-00001: 违反唯一约束条件 (.) 错误说明:当在唯一索引所对应的列上键入重复值时,会触发此异常。 O...
    我想起个好名字阅读 5,484评论 0 9
  • Oracle物化视图的用法与总结 物化视图(material view)是什么? 物化视图是包括一个查询结果的数据...
    Bobby0322阅读 20,380评论 0 13
  • MySQL逻辑架构 下面是一幅MySQL各组件之间如何协同工作的架构图,有助于我们深入理解MySQL服务器。 如图...
    骑小猪看流星阅读 4,838评论 2 135
  • 2018.8.26.晴 今天做了一天彻底的睡神。睡到中午妈妈的饭菜飘香,午后又开始睡到傍晚六点左右妈妈买菜回来。 ...
    叮铛之眼阅读 214评论 0 3
  • 文/小超人同学 没深夜痛哭过,不足以谈论生活 人都是有七情六欲,会笑就也会哭,只是我们大多时候把笑展现在人前,哭只...
    晴风Joy阅读 461评论 4 7