一、什么是物化视图
物化视图是一种特殊的物理表，“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性大，任何对普通视图的查询，数据库都实际上转换为视图SQL语句的查询，这样对整体查询性能的提高，并没有实质上的好处。
二、与普通视图对比
| 类型\对比项 |
物理结构 |
时效性 |
有无索引 |
| 视图 |
虚拟表 |
实时 |
无索引 |
| 物化视图 |
物理表 |
非实时 |
可以创建索引 |
三、物化视图的作用
用于预先计算，并保存表连接或表聚集等耗时较多操作的结果。以便于在执行查询时，避免在基表上进行这些耗时的操作（基表上进行耗时操作是不是会影响其他用户使用这个基表的效率，毕竟基表查询肯定很频繁？或者单纯从Oracle性能角度出发，希望利用物化视图减少基表非常耗时的操作，从而提升数据库性能？），从而快速得到结果。
四、物化视图SQL脚本
4.1 Oracle
4.1.1 创建
-
-- 创建视图
-
create materialized
view [view_name]
-
refresh [fast
|complete
|force]
-
[
-
on [
commit
|demand]
|
-
start
with (start_time) next (next_time)
-
]
-
as
-
{创建物化视图时用的查询语句}
-
-
-- 创建索引
-
create
unique index index_name
on view_name (列名
1,列名
2...);
4.1.2 修改
-
-- 修改视图
-
alter materialized
view [view_name]
-
refresh [fast
|complete
|force]
-
[
-
on [
commit
|demand]
|
-
start
with (start_time) next (next_time)
-
]
-
-- 修改索引
-
alter materialized
view mv_name
add
constraint
4.1.3 删除
drop materialized view [if exists] view_name;4.1.4 查询
select * from mv_name;4.2 PostgreSQL
4.2.1 创建
-
-- 创建视图
-
CREATE MATERIALIZED
VIEW [ IF
NOT
EXISTS ] table_name
-
[ (column_name [, ...] ) ]
-
[
USING
method ]
-
[
WITH ( storage_parameter [
=
value] [, ... ] ) ]
-
[ TABLESPACE tablespace_name ]
-
AS query
-
[
WITH [
NO ] DATA ]
-
-
-- 创建索引
-
create
unique index index_name
on view_name (列名
1,列名
2...);
4.2.2 修改
-
-- 修改视图
-
ALTER MATERIALIZED
VIEW [ IF
EXISTS ] name
-
action [, ... ]
-
ALTER MATERIALIZED
VIEW name
-
[
NO ] DEPENDS
ON EXTENSION extension_name
-
ALTER MATERIALIZED
VIEW [ IF
EXISTS ] name
-
RENAME [
COLUMN ] column_name
TO new_column_name
-
ALTER MATERIALIZED
VIEW [ IF
EXISTS ] name
-
RENAME
TO new_name
-
ALTER MATERIALIZED
VIEW [ IF
EXISTS ] name
-
SET SCHEMA new_schema
-
ALTER MATERIALIZED
VIEW
ALL
IN TABLESPACE name [ OWNED
BY role_name [, ... ] ]
-
SET TABLESPACE new_tablespace [ NOWAIT ]
-
-
where action
is
one
of:
-
-
ALTER [
COLUMN ] column_name
SET STATISTICS
integer
-
ALTER [
COLUMN ] column_name
SET ( attribute_option
=
value [, ... ] )
-
ALTER [
COLUMN ] column_name RESET ( attribute_option [, ... ] )
-
ALTER [
COLUMN ] column_name
SET STORAGE { PLAIN
|
EXTERNAL
| EXTENDED
| MAIN }
-
ALTER [
COLUMN ] column_name
SET COMPRESSION compression_method
-
CLUSTER
ON index_name
-
SET
WITHOUT CLUSTER
-
SET ACCESS
METHOD new_access_method
-
SET TABLESPACE new_tablespace
-
SET ( storage_parameter [
=
value] [, ... ] )
-
RESET ( storage_parameter [, ... ] )
-
OWNER
TO { new_owner
|
CURRENT_ROLE
|
CURRENT_USER
|
SESSION_USER }
--
4.2.3 删除
DROP MATERIALIZED VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ];4.2.4 查询
select * from mv_name;五、物化视图刷新
5.1 Oracle
语法：DBMS_SNAPSHOT.REFRESH( 'v_materialized_foo_tbl','f');
（默认刷新方式是force on demand）
5.1.1 刷新模式
- fast：一种增量刷新，在上次刷新的基础上，对此次在基表做的DML修改进行刷新。只刷新上次刷新以后做的修改;
- complete：完全刷新。是对整个物化视图进行的刷新;
- force：Oracle在刷新时会判断是否可以采用fast刷新，如果不可以会采用complete刷新（肯定会刷新。若上次刷新之后的操作丢失，无法做fast刷新，于是做全表刷新）
- never：不会使用刷新机制刷新
5.1.2 刷新时机
- on demand：在用户需要刷新的时候刷新，这里就要求用户自己动手去刷新数据了（也可以使用job定时刷新）;
- on commit：当主表中有数据提交的时候，立即刷新MV中的数据；
- start … next …：从指定的时间开始，每隔一段时间（由next指定）就刷新一次；
5.2 PostgreSQL
语法：REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name [ WITH [ NO ] DATA ]
（默认刷新方式是with data）
5.2.1 刷新模式
- 全量更新： 直接去基础表里面查询数据，刷新过程中会对该物化视图的所有的select操作阻塞，但刷新效率快。
- 增量更新（concurrently）：刷新要具备有唯一字段索引，将基础表查询出来的数据和现在物化视图表进行对比，填充差量数据，但刷新过程中不会对该物化视图的select操作进行阻塞，但刷新比全量更新慢。
5.2.2 刷新设置
- with data：创建物化视图后，视图可查询数据;
- with no data：创建物化视图后，如果无数据则视图不可查询；
六、注意事项
- 不要处理运算结果，因为可能有其它功能需要做聚合运算；
- 需要保存原SQL，如果数据库格式化SQL，会导致很难读懂和修改；
[参考文献]：
- Basic Materialized Views
- CREATE MATERIALIZED VIEW
- 物化视图 materialized view
- PostgreSQL视图和物化视图
- PostgreSQL视图和物化视图
- PG 物化视图
转载：https://blog.csdn.net/qq_26230421/article/details/128318420
查看评论