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

2018-10-23 本文已影响0人莺时_mona

一、工作状况

环境：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.基本概念

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

数据
物化视图创建时生成数据分为两种： build immediate 和 build deferred:
- build immediate是在创建物化视图的时候就生成数据
- build deferred 则在创建时不生成数据，以后根据需要在生成数据
如果不指定，则默认为 build immediate
刷新模式
物化视图在创建时有二种刷新模式(refresh mode)：on demand和on commit：
- on demand 顾名思义，仅在该物化视图需要被刷新了，才进行刷新，更新物化视图，以保证和基表数据的一致性
- on commit 提交触发，一旦基表有了commit，即事务提交，则立刻刷新，更新物化视图，使得数据和基表一致。一般用这种方法在操作基表时速度会比较慢。
创建时未指定，则Oracle按 on demand 模式来创建。
如何刷新
上面说的是 刷新模式 ，针对于如何刷新，则有如下三种 刷新方法 ：
- 完全刷新（COMPLETE）：会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。
- 快速刷新（FAST）：采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。
- FORCE方式：这是默认的数据刷新方式。Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。
关于快速刷新： Oracle物化视图的快速刷新机制是通过物化视图日志完成的。 Oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。
查询重写（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物化视图连接查询注意事项

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

一、工作状况

二、物化视图概述

三、官方文档语法

1.创建物化视图

2. 创建物化视图日志

四、引用

猜你喜欢

热点阅读