物化视图

年前项目中跟一个Domain的数据查询数据没及时刷新的问题。后来就考虑使用物化视图，然后每天刷新两次来保持同步。这里整理下。

视图

视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定。比如下边代码就是创建一个视图，然后每次查询都直接基于视图，查询语句也会简单化。

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

优点：
1. 减少程序中的SQL。复杂的查询可以被限定在数据库内部，对使用者透明。

2. 安全。可以通过视图授权给不同用户，而且多个表关联只允许查看，不允许修改。（单表可以设置WITH READ ONLY）。

物化视图

我们知道，普通视图是虚拟表，只有结构没有数据。而基于视图的查询，最后都是变成SQL的查询。因此在SQL性能优化中，我们都只能是优化SQL语句。
物化视图（material view）其实是一个物理表，能存储数据，因此在一些复杂或者费时的查询中，利用它可以加快查询速度。

简单来说，物化视图就是一个查询结果的数据库对象，是一些查询结果的副本，或者说快照。然后在这个副本上进行查询，速度会快一些。

创建一个简单物化视图：

CREATE MATERIALIZED VIEW MY_MATERIALIZED_VIEW AS SELECT * FROM MY_TABLE;

然后就可以基于上边这个物化视图进行查询。

SELECT * FROM  MY_MATERIALIZED_VIEW;

然后执行下边的SQL，发现已经是我们的视图了。

SELECT * FROM USER_MVIEWS WHERE mview_name = 'MY_MATERIALIZED_VIEW';

在执行下边的SQL，就会发现物化视图也是个物理表：

SELECT t.TABLE_NAME, t.* FROM USER_TABLES t where t.TABLE_NAME = 'MY_MATERIALIZED_VIEW';

现在我们向原始表插入数据，然后分别在原始表和物化视图上查询：

SELECT * FROM  MY_MATERIALIZED_VIEW;
SELECT * FROM MY_TABLE;

此时就会发现：物化视图查到的数据比原始表查到的数据少了最新的一条。也就是说：物化视图保留了查询结果的副本。原始表的更新没有同步到物化视图中。

现在，通过以下SQL在物化视图上执行更新：

UPDATE SPSI_CMS_CHANGE_MONITOR_VIEW SET REC_UPD_DT = SYSDATE -3 ;

此时会出现以下错误：

SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 -  "data manipulation operation not legal on this view"
*Cause:    

*Action:

也就是说，物化视图是查询结果的只读副本。

语法

先看下创建物化视图的语法，不过在这之前，要给当前用户赋予创建物化视图的权限：

GRANT CREATE MATERIALIZED VIEW TO user_name; 

此时就可以利用这个用户创建物化视图了。

创建语法如下：

CREATE MATERIALIZED VIEW mv_name [选项n] AS SELECT * FROM table_name;

[选项n]是参数，解释如下：

选项	解释
BUILD [immediate,deferred]	是否在创建视图时生成数据，默认生成。deferred为不生成数据，然后在需要的时候生成。
refresh [fast|complete|force|never]	fast是增量刷新，或者叫快速刷新; complete为全表刷新; force为如果增量刷新可以使用则使用增量刷新，否则全表刷新; never则是不进行刷新（不使用）
on [demand,commit]	即手工刷新和提交时刷新。commit表示自动刷新，原表有数据提交就刷新。
start with varDate next varDate2	通知数据库完成从主表到本地表第一次复制的时间为varDate。 指明了刷新的时间间隔。时间间隔 = 下次刷新的时间 -上次执行完成的时间
with	例如：with rowid 创建基于rowid的物化视图，对应的是 primary key

知道语法了，我们将上边的刷设置为自动刷新，在这之前，先打开SQL/Plus将物化视图刷新：

dbms_mview.REFRESH(TAB=>'SPSI_CMS_CHANGE_MONITOR_VIEW', 'complete');

否则会因为数据不一致报以下错误：

32337. 00000 -  "cannot alter materialized view with pending changes refresh on commit"
*Cause:    There are some pending changes in the detail tables
*Action:   Execute an on-demand refresh on the materialized view to
           synchronize the data between the materialized view and the detail
           tables and then issue an ALTER MATERIALIZED VIEW statement.

刷新了物化视图的数据后，修改为每天自动刷新：

ALTER MATERIALIZED VIEW MY_MATERIALIZED_VIEW REFRESH FORCE ON DEMAND START WITH SYSDATE NEXT SYSDATE +1 ;

特征

我们知道，在表上边是可以建索引的。同样，物化视图上边也可以建索引，语法与在表上创建索引相同。

CREATE UNIQUE INDEX MY_MATERIALIZED_VIEW_INDEX ON MY_MATERIALIZED_VIEW (UPDATE_IODT) ;

删除

物化视图也可以被删除，不过与表有点不一样。我在表上右键选择‘drop’时，不能删除物化视图。只能用SQL删除：

DROP MATERIALIZED VIEW MY_MATERIALIZED_VIEW;