应用环境：
操作系统： RedHat EL55
Oracle： Oracle 10gR2
一、物化视图概述
Oracle的物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。
物化视图可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。
物化视图可以查询表，视图和其它的物化视图。
通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。
对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。
对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。
在复制环境下，创建的物化视图通常情况下主键，rowid和子查询视图。
物化视图由于是物理真实存在的，故可以创建索引。
二、物化视图刷新
物化视图，根据不同的着重点可以有不同的分类:
1) 按刷新方式分：FAST/COMPLETE/FORCE
2) 按刷新时间的不同：ON DEMAND/ON COMMIT
3) 按是否可更新：UPDATABLE/READ ONLY
4) 按是否支持查询重写：ENABLE QUERY REWRITE/DISABLEQUERY REWRITE
默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。
注意：设置REFRESH ON COMMIT的物化视图不能访问远端对象。
在建立物化视图的时候可以指定ORDER BY语句，使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。
物化视图有三种刷新方式：COMPLETE、FAST和 FORCE。
1) 完全刷新（COMPLETE）会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。
2) 快速刷新（FAST）采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。
对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。
3) 采用FORCE方式，Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。
Oracle物化视图的快速刷新机制是通过物化视图日志完成的。Oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。
物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。
三、创建物化视图命令
-
create materialized view [view_name]
-
refresh [fast|complete|force]
-
[
-
on [commit|demand] |
-
start with (start_time) next (next_time)
-
]
-
as
-
{创建物化视图用的查询语句}
案例分析：
本案例架构
1）在test1库上建立db link
tnsnames.ora:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
[oracle@rh6 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/
11.2.
0
/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TEST1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rh6.cuug.net)(PORT =
1521
))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rh6.cuug.net)(PORT =
1521
))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
在prod库上建立tom用户，并授权
15
:
18
:
08
SYS@ prod >create user tom identified by tom;
User created.
15
:
18
:
27
SYS@ prod >grant connect,resource to tom;
Grant succeeded.
15
:
18
:
49
SYS@ prod >grant all
on
scott.emp1 to tom;
Grant succeeded.
在test1库上建立db link
15
:
12
:
12
SYS@ test1 >grant create database link,create
public
database link to tom;
Grant succeeded.
15
:
13
:
59
TOM@ test1 >create database link db_link_prod connect to tom identified by tom using
'prod'
;
Database link created.
测试：
15
:
19
:
10
TOM@ test1 >select *
from
scott.emp1@db_link_prod;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- --------
7369
SMITH CLERK
7902
1980
-12
-17
00
:
00
:
00
800
20
7499
ALLEN SALESMAN
7698
1981
-02
-20
00
:
00
:
00
1600
300
30
7521
WARD SALESMAN
7698
1981
-02
-22
00
:
00
:
00
1250
500
30
7566
JONES MANAGER
7839
1981
-04
-02
00
:
00
:
00
2975
20
7654
MARTIN SALESMAN
7698
1981
-09
-28
00
:
00
:
00
1250
1400
30
7698
BLAKE MANAGER
7839
1981
-05
-01
00
:
00
:
00
2850
30
7782
CLARK MANAGER
7839
1981
-06
-09
00
:
00
:
00
2450
10
7788
SCOTT ANALYST
7566
1987
-04
-19
00
:
00
:
00
3000
20
7839
KING PRESIDENT
1981
-11
-17
00
:
00
:
00
5000
10
7844
TURNER SALESMAN
7698
1981
-09
-08
00
:
00
:
00
1500
0
30
7876
ADAMS CLERK
7788
1987
-05
-23
00
:
00
:
00
1100
20
7900
JAMES CLERK
7698
1981
-12
-03
00
:
00
:
00
950
30
7902
FORD ANALYST
7566
1981
-12
-03
00
:
00
:
00
3000
20
7934
MILLER CLERK
7782
1982
-01
-23
00
:
00
:
00
1300
10
14
rows selected.
|
2）在prod库的emp1表上建立物化视图日志
|
1
2
3
4
5
6
7
8
|
15
:
06
:
49
SCOTT@ prod >create table emp1
as
select *
from
emp;
Table created.
15
:
09
:
07
SCOTT@ prod >alter table emp1 add constraint pk_emp1 primary key(empno);
Table altered.
15
:
09
:
26
SCOTT@ prod >create materialized view log
on
emp1;
Materialized view log created.
|
3）在test1上建立物化视图
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
在prod库上对tom授权
15
:
19
:
07
SYS@ prod >grant select any table to tom;
Grant succeeded.
在test1库上对tom授权
15
:
22
:
11
SYS@ test1 >grant create materialized view to tom;
Grant succeeded.
TOM@ test1 >create materialized view mv1_emp1
refresh fast
on
demand
as
select *
from
scott.emp1@db_link_prod;
测试：
15
:
33
:
15
TOM@ test1 >select *
from
mv1_emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- --------
7369
SMITH CLERK
7902
1980
-12
-17
00
:
00
:
00
800
20
7499
ALLEN SALESMAN
7698
1981
-02
-20
00
:
00
:
00
1600
300
30
7521
WARD SALESMAN
7698
1981
-02
-22
00
:
00
:
00
1250
500
30
7566
JONES MANAGER
7839
1981
-04
-02
00
:
00
:
00
2975
20
7654
MARTIN SALESMAN
7698
1981
-09
-28
00
:
00
:
00
1250
1400
30
7698
BLAKE MANAGER
7839
1981
-05
-01
00
:
00
:
00
2850
30
7782
CLARK MANAGER
7839
1981
-06
-09
00
:
00
:
00
2450
10
7788
SCOTT ANALYST
7566
1987
-04
-19
00
:
00
:
00
3000
20
7839
KING PRESIDENT
1981
-11
-17
00
:
00
:
00
5000
10
7844
TURNER SALESMAN
7698
1981
-09
-08
00
:
00
:
00
1500
0
30
7876
ADAMS CLERK
7788
1987
-05
-23
00
:
00
:
00
1100
20
7900
JAMES CLERK
7698
1981
-12
-03
00
:
00
:
00
950
30
7902
FORD ANALYST
7566
1981
-12
-03
00
:
00
:
00
3000
20
7934
MILLER CLERK
7782
1982
-01
-23
00
:
00
:
00
1300
10
14
rows selected.
|
4）测试物化视图数据刷新
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
在基表上更新数据
15
:
33
:
10
SYS@ prod >conn scott/tiger
Connected.
15
:
35
:
59
SCOTT@ prod >select *
from
emp1
where
empno=
7788
;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- -------
7876
ADAMS CLERK
7788
23
-MAY
-87
1100
20
15
:
36
:
04
SCOTT@ prod >update emp1 set deptno=
40
where
empno=
7788
;
1
row updated.
15
:
36
:
23
SCOTT@ prod >commit;
Commit complete.
15
:
36
:
35
SCOTT@ prod >select *
from
emp1
where
empno=
7788
;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- -------
7788
SCOTT ANALYST
7566
19
-APR
-87
3000
40
在物化视图上查看数据更新
15
:
35
:
13
TOM@ test1 >select *
from
mv1_emp1
where
empno=
7788
;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- --------
7788
SCOTT ANALYST
7566
1987
-04
-19
00
:
00
:
00
3000
20
默认物化视图不会自动更新，需手工更新
15
:
38
:
12
TOM@ test1 >exec dbms_mview.refresh(
'mv1_emp1'
,
'fast'
);
PL/SQL procedure successfully completed.
15
:
38
:
41
TOM@ test1 >select *
from
mv1_emp1
where
empno=
7788
;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- --------
7788
SCOTT ANALYST
7566
1987
-04
-19
00
:
00
:
00
3000
40
|
至此，物化视图建立完成 ！
5）查看数据和日志更新信息
|
1
2
3
4
5
6
7
8
9
10
11
|
在test1上查看数据刷新信息
15
:
39
:
02
TOM@ test1 >select mview_name, last_refresh_date, staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ------------------- -------------------
MV1_EMP1
2014
-
08
-
22
15
:
38
:
41
UNDEFINED
在prod上查看物化视图日志更新信息
15
:
40
:
41
SCOTT@ prod >select log_owner,master,log_table,PRIMARY_KEY,LAST_PURGE_DATE,LAST_PURGE_STATUS from user_mview_logs
LOG_OWNER MASTER LOG_TABLE PRI LAST_PURG LAST_PURGE_STATUS
------------------------------ ------------------------------ ---------
SCOTT EMP1 MLOG$_EMP1 YES
22
-AUG-
14
0
|
6）在物化视图上创建索引
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
15
:
39
:
39
TOM@ test1 >CREATE index mv1_ind
on
mv1_emp1(ename) tablespace indx;
Index created.
16
:
39
:
15
TOM@ test1 >select index_name,index_type,table_name,BLEVEL,leaf_blocks FROM user_indexes
16
:
39
:
30
2
where
index_name=
'MV1_IND'
;
INDEX_NAME INDEX_TYPE TABLE_NAME BLEVEL LEAF_BLOCKS
------------------------------ --------------------------- ------------
MV1_IND NORMAL MV1_EMP1
0
1
16
:
40
:
02
TOM@ test1 >select *
from
mv1_emp1
where
ename=
'scott'
;
no rows selected
Elapsed:
00
:
00
:
00.02
Execution Plan
----------------------------------------------------------
Plan hash value:
720877713
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
1
|
87
|
1
(
0
)|
00
:
00
:
01
|
|
1
| MAT_VIEW ACCESS BY INDEX ROWID| MV1_EMP1 |
1
|
87
|
1
(
0
)|
00
:
00
:
01
|
|*
2
| INDEX RANGE SCAN | MV1_IND |
1
| |
1
(
0
)|
00
:
00
:
01
|
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2
- access(
"ENAME"
=
'scott'
)
Note
-----
- dynamic sampling used
for
this
statement (level=
2
)
Statistics
----------------------------------------------------------
310
recursive calls
0
db block gets
44
consistent gets
0
physical reads
0
redo size
695
bytes sent via SQL*Net to client
408
bytes received via SQL*Net
from
client
1
SQL*Net roundtrips to/
from
client
2
sorts (memory)
0
sorts (disk)
0
rows processed
|
案例错误信息：
建立物化视图出错
15:31:27 TOM@ test1 >create materialized view mv1_emp1
15:32:36 2 refresh fast on demand
15:32:36 3 as
15:32:36 4 select * from scott.emp1@db_link_prod;
create materialized view mv1_emp1
*
ERROR at line 1:
ORA-12018: following error encountered during code generation for "TOM"."MV1_EMP1"
ORA-00942: table or view does not exist
解决：
在基表所在的库上，进行授权：
15:19:07 SYS@ prod >grant select any table to tom;
Grant succeeded.
