CREATE MATERIALIZED VIEW
file:///D:/oracle10gr2官方文档/B19306_01/server.102/b14200/statements_6002.htm#i2063793
1,存储查询结果的对象
2,from可以是表,视图,其它的物化视图
3,from其后这些对象叫作master tables或detail tables
4,包含这些master tables的数据库叫作master database
5,snapshot用于之前的版本,对应materialized view
物化视图在本地存储远程数据的拷贝
物化视图可以用高级复制技术进行更新或者仅读
物化视图使用如同自一个表或视图查询一样
在复制环境下,创建的物化视图可以是primary key,rowid,object,subquery物化视图
在数据仓库环境下,创建的物化视图是materilized aggregate views,single-table materialized aggregate views,materilized join views
这三类物化视图采用查询重写技术,即根据一系列master tables的真实情况把用户请求转变为同语义的等同写法,包含一或多个物化视图
创建物化视图的一些必备条件:
1,为了在自己用户创建物化视图
1,必须被授予create materialized view和create table or create any table 系统权限
2,如master table不在当前用户,必须授予select on master table或select any table系统权限
2,为了创建(on commit refresh clause)一提交即刷新的物化视图,除了上述的系统权限,必须具备on commit refresh on master table的对象权限或
者on commit refresh object privilge;
3,为了创建具备查询重写功能的物化视图,还要授予如下的权限:
1,如master tables不在当前用户,授予global query rewrite权限或query rewrite on table的对象权限
2,如基于prebuilt container创建物化视图,必须授予基于prebuilt container的select,以及选项with grant option
4,一旦创建物化视图,数据库同步创建一个内部表及至少一个索引,也可能创建一个视图.oracle使用这些辅助对象维持物化视
图的数据.
--创建物化视图语法
CREATE MATERIALIZED VIEW
[ schema. ]materialized_view
[ column_alias [, column_alias]... ]
[ OF [ schema. ]object_type ]
[ (scoped_table_ref_constraint) ]
{ ON PREBUILT TABLE
[ { WITH | WITHOUT } REDUCED PRECISION ]
| physical_properties materialized_view_props
}
[ USING INDEX
[ physical_attributes_clause
| TABLESPACE tablespace
]
[ physical_attributes_clause
| TABLESPACE tablespace
]...
| USING NO INDEX
]
[ create_mv_refresh ]
[ FOR UPDATE ]
[ { DISABLE | ENABLE }
QUERY REWRITE
]
AS subquery ;
子项
materialized_view_props::=
--上述各子项组成部分
(column_properties ::=, table_partitioning_clauses ::=--part of CREATE TABLE syntax, parallel_clause::=, build_clause::=)
build_clause::=
BUILD { IMMEDIATE | DEFERRED }
scoped_table_ref_constraint ::=
{ SCOPE FOR
({ ref_column | ref_attribute })
IS [ schema. ] { scope_table_name | c_alias }
}
[, SCOPE FOR
({ ref_column | ref_attribute })
IS [ schema. ] { scope_table_name | c_alias }
]...
create_mv_refresh::=
fast|
complete|
force
on demand|commit
start with|next date
refresh
with primary key|rowid
using default master|local rollback segment
master|local rollback segment rollback_segment
using enforced|trusted constraints
never refresh
各子项语义分解
OF object_type
创建类型为object_type的对象物化视图
The OF object_type clause lets you explicitly create an object materialized view of type object_type.
scoped_table_ref_constraint
用scope for子句限制引用每个对象表的范围.可以用scope_table_name或column_alias进行引用
Use the SCOPE FOR clause to restrict the scope of references to a single object table. You can refer either
to the table name with scope_table_name or to a column alias. The values in the REF column or attribute point
to objects in scope_table_name or c_alias, in which object instances of the same type as the REF column are stored.
If you specify aliases, then they must have a one-to-one correspondence with the columns in the SELECT list of
the defining query of the materialized view.
SCOPE REF Constraints
In a table with a REF column, each REF value in the column can conceivably reference a row in a different object table.
The SCOPE clause restricts the scope of references to a single table, scope_table. The values in the REF column or attribute
point to objects in scope_table, in which object instances of the same type as the REF column are stored.
Specify the SCOPE clause to restrict the scope of references in the REF column to a single table. For you to specify this clause,
scope_table must be in your own schema or you must have SELECT privileges on scope_table or SELECT ANY TABLE system privileges.
You can specify only one scope table for each REF column.
Restrictions on Scope Constraints Scope constraints are subject to the following restrictions:
?
You cannot add a scope constraint to an existing column unless the table is empty.
?
You cannot specify a scope constraint for the REF elements of a VARRAY column.
?
You must specify this clause if you specify AS subquery and the subquery returns user-defined REF datatypes.
?
You cannot subsequently drop a scope constraint from a REF column.
ON PREBUILT TABLE Clause
on prebuilt table子句登记一个已存在的表为预初始化的物化视图;特别适用于在数据仓库环境下注册一个大型的
物化视图.要注册的表必须和物化视图同名及同用户;
如物化视图删除,已存在的表重新恢复为正常的表类型;
with reduced precision允许物化视图的列数据类型与子查询返回的匹配列的数据类型有一定程序的精度差异
WITH REDUCED PRECISION Specify WITH REDUCED PRECISION to authorize the loss of precision that will result
if the precision of the table or materialized view columns do not exactly match the precision returned by subquery.
而without reduced precision为默认选项,子查询与物化视图匹配列的数据类型必须相同,否则创建失败
WITHOUT REDUCED PRECISION Specify WITHOUT REDUCED PRECISION to require that the precision of the table or materialized
view columns match exactly the precision returned by subquery, or the create operation will fail. This is the default.
使用prebuilt table的一些操作限制 .
Restrictions on Using Prebuilt Tables Prebuilt tables are subject to the following restrictions:
子查询的列别名必须与prebuilt table的列一一匹配,且数据类型要匹配
Each column alias in subquery must correspond to a column in the prebuilt table, and corresponding columns
must have matching datatypes.
如使用此子句,那么在子查询未使用的任何列,你不能为这些列指定not null约束,除非你为这些列设置了默认值
If you specify this clause, then you cannot specify a NOT NULL constraint for any column that is not
referenced in subquery unless you also specify a default value for that column.
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott@orcl
SQL> create table t_mv(a int);
Table created
SQL> create materialized view t_mv on prebuilt table with reduced precision
2 enable query rewrite
3 as
4 select a
5 from t_mv;
create materialized view t_mv on prebuilt table with reduced precision
enable query rewrite
as
select a
from t_mv
ORA-32349: cannot reference prebuilt table in definition query for materialized view
file:///D:/oracle10gr2官方文档/B19306_01/server.102/b14200/statements_6002.htm#i2092101