一、工作状况
- 环境：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 ]
;
