物化视图（Oracle与PostgreSQL对比）

前言

多多点赞，会变好看！

多多留言，会变有钱！

一、什么是物化视图

物化视图是一种特殊的物理表，“物化”(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 刷新模式

1. fast：一种增量刷新，在上次刷新的基础上，对此次在基表做的DML修改进行刷新。只刷新上次刷新以后做的修改;
2. complete：完全刷新。是对整个物化视图进行的刷新;
3. force：Oracle在刷新时会判断是否可以采用fast刷新，如果不可以会采用complete刷新（肯定会刷新。若上次刷新之后的操作丢失，无法做fast刷新，于是做全表刷新）
4. never：不会使用刷新机制刷新

5.1.2 刷新时机

1. on demand：在用户需要刷新的时候刷新，这里就要求用户自己动手去刷新数据了（也可以使用job定时刷新）;
2. on commit：当主表中有数据提交的时候，立即刷新MV中的数据；
3. start … next …：从指定的时间开始，每隔一段时间（由next指定）就刷新一次；

5.2 PostgreSQL

语法：REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name [ WITH [ NO ] DATA ]

（默认刷新方式是with data）

5.2.1 刷新模式

1. 全量更新： 直接去基础表里面查询数据，刷新过程中会对该物化视图的所有的select操作阻塞，但刷新效率快。
2. 增量更新（concurrently）：刷新要具备有唯一字段索引，将基础表查询出来的数据和现在物化视图表进行对比，填充差量数据，但刷新过程中不会对该物化视图的select操作进行阻塞，但刷新比全量更新慢。

5.2.2 刷新设置

1. with data：创建物化视图后，视图可查询数据;
2. with no data：创建物化视图后，如果无数据则视图不可查询；

六、注意事项

1. 不要处理运算结果，因为可能有其它功能需要做聚合运算；
2. 需要保存原SQL，如果数据库格式化SQL，会导致很难读懂和修改；

[参考文献]：

1. Basic Materialized Views
2. CREATE MATERIALIZED VIEW
3. 物化视图 materialized view
4. PostgreSQL视图和物化视图
5. PostgreSQL视图和物化视图
6. PG 物化视图