oracle之二物化视图

物化视图

18.1、物化视图作用

1) 物化视图起源于数据仓库，早期的考虑是用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免在基表上进行这些耗时的操作，从而快速的得到结果。

2) 物化视图和表一样可以直接进行查询。物化视图还用于复制、移动计算等方面。

alter table t add primary key (empno);

create materialized view log on t;

CREATE MATERIALIZED VIEw mv1 refresh fast
as select * from t;

exec dbms_mview.refresh('mv1','F');

18.2 物化视图创建时的权限

如果创建基于主键的物化视图，则必须具有访问主表、访问主表的日志、create MATERIALIZED VIEW这三个权限。
如果创建基于rowid的物化视图，则必须具有访问主表、create MATERIALIZED VIEW这两个权限。

18.3 创建物化视图基本语法

create materialized view [view_name]
refresh [fast|complete|force]
[
on [commit|demand] |
start with (start_time) next (next_time)
]
as
{创建物化视图用的查询语句}

18.4 物化视图基本功能

1) 查询重写（Query Rewrite）：查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果。建立物化视图时可以指定Enable Query Rewrite，默认为DISABLE。

2) 刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ON DEMAND和ON COMMIT。ON DEMAND指物化视图按照用户需要进行刷新。刷新的方法有四种：FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时会去判断是否可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。默认值是FORCE ON DEMAND。

3) 物化视图日志：提高刷新效率。

4) 基于主键和基于rowid的物化视图

18.5 基于主键的物化视图的两个范例

例1，在Linux本地完成表test和物化视图test_view1

1）SCOTT下建立基于主键的test表，然后建立test表的物化视图日志
scott:
SQL> create table test (id int primary key,name char(10));

SQL> create materialized view log on test;

2）SYS授权SCOOT用户建立物化视图权限
sys:
SQL> grant create materialized view to scott;

3）SCOTT建立基于commit的物化视图

SQL> create materialized view test_view1 refresh fast on commit as select * from test;

4） test表插入数据，检查test_view1刷新。

例2，在远程节点建立物化视图test_view2

1)本地（linux)和远程( win)建立link连接, database link是远程连接的基础，也是oracle 分布式数据库技术的组成部分。

win:

C:\Documents and Settings\timran>sqlplus sys/system@orcl as sysdba

sys:
SQL> create public database link my_link connect to scott identified by 123123 using 'dborcl';

//如果以前建立过，提示重名的话可以使用下面语句删掉 SQL> drop public database link my_link;

若想查看所有的数据库链接，进入系统管理员SQL>操作符下，运行命令：

SQL>select owner,object_name from dba_objects where object_type='DATABASE LINK';

OWNER OBJECT_NAME
---------------------------------- ---------------------------------------
PUBLIC MY_LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM

2）在远程（win)上测试与本地（linux,启动监听）的访问连接是否成功。

win:scott：
SQL> select * from tab; //看win自己

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
测试 TABLE

SQL> select * from tab@my_link; //看linux（别人）

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
MLOG$_TEST TABLE
RUPD$_TEST TABLE
SALGRADE TABLE
TEST TABLE
TEST_VIEW1 TABLE

3） 授权win端scott建立物化视图的权限
win：sys:
SQL>grant create materialized view to scott; （前面已经授予了，这步可免）

4）win端建立基于test主键的on demand（按一定时间间隔刷新）的物化视图test_view2
scott:
SQL>create materialized view test_view2 refresh
fast
start with sysdate
next sysdate+1/2880
with primary key
as select * from scott.test@my_link;

//1440分钟是24小时，1/1440是1分钟，1/2880就是30秒。

5) 测试win端物化视图test_view2刷新效果

SQL> select * from test_view2;

ID NAME
---------- ----------
1 a
2 b

linux: scott:

SQL>insert into test values(3,'c');
SQL>commit;

win: scott: ---30秒以内...

SQL> select * from test_view2;

ID NAME
---------- ----------
1 a
2 b
3 c

18.6 关于视图的几点说明：

1）基于主键的物化视图是首选，如果基表无主键，只能考虑基于rowid的物化视图, 基于rowid的快速刷新要在建立物化视图日志时做出说明。
如：SQL> create materialized view log on test with rowid;

2）一个基表只能有一个物化视图日志，其中可以分别应对多个物化视图的增量刷新。

3）物化视图的数据源可以是基表的结果集，select 语句还可以嵌套子查询。

4）物化视图会有segment,可以做index, partition, 一般用于只读，也可以在建立时说明for update，使物化视图支持DML操作（慎用）。
如：SQL> create materialized view test_view1 refresh fast for update as select * from test;

5）通过包可以对on demand方式进行手动刷新。
SQL> exec dbms_mview.refresh('scott.test_view1');