oracle物化视图系列(二)

oracle物化视图知识比较繁杂,本文为oracle物化视图系列(二)

--子查询的表名不能与物化视图的名称相同
Cause: The prebuilt container table of the materialized view was referenced in the definition query.
 
Action: Change the definition query to reference a non-prebuilt table.

SQL> desc t_mv;
Name Type    Nullable Default Comments
---- ------- -------- ------- --------
A    INTEGER Y                        
 
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

--经修正后,物化视图的名称可与非子查询的表名一样,其作用即根据业务逻辑加工几个源表转变为另一个表,
--另一个表与物化视图相同
SQL> create table t_same(a int);
 
Table created

SQL> create materialized view t_same on prebuilt table with reduced precision
  2  enable query rewrite
  3  as
  4  select a from t_mv
  5  /
 
Materialized view created    

--删除物化视图
SQL> drop materialized view t_same;
                                  
Materialized view dropped         

---如移除on prebuilt table选项则提示对象已存在
create materialized view t_same                     
enable query rewrite                                
as                                                  
select a from t_mv                                  
                                                    
ORA-00955: name is already used by an existing object 

物理特性子句
physical_properties_clause                                                                                                                                                                    
物化视图的物理特性子句和普通表的语义一样                                                                                                                                                                                              
The components of the physical_properties_clause have the same semantics for materialized views that they have for tables,
with exceptions and additions described in the sections that follow.

此子句不能指定organization external                                                                                                                                                                                              
Restriction on the physical_properties_clause You cannot specify ORGANIZATION EXTERNAL for a materialized view.   

 

segment_attributes_clause                                                                                                                                                                                                                                                                                                                                                       
使用segment_attributes子句指定pctfree,pctused,initrans参数的值;即配置物化视图的存储属性,即指定表空间,是否开
启日志功能.而在使用using index子句,不能指定pctfree,pctused                                                                                                                                                                                                                                                                                                    
Use the segment_attributes_clause to establish values for the PCTFREE, PCTUSED, and INITRANS parameters,
the storage characteristics for the materialized view, to assign a tablespace, and to specify whether
logging is to occur. In the USING INDEX clause, you cannot specify PCTFREE or PCTUSED.

索引组织表子句
index_org_table_clause                                                                                                                                                                                                                                                                 
此子句创建索引组织物化视图.基于物化视图的主键对应的索引存储每条记录,可以为如下几种类型的物化视图指定索引组织方式                                                                                                                                                                                                                                                                                       
The ORGANIZATION INDEX clause lets you create an index-organized materialized view. In such a materialized view,
data rows are stored in an index defined on the primary key of the materialized view. You can specify index
organization for the following types of materialized views:
只读且可更新的物化视图,必须确保master table具备主键                                                                                                                                                                                                                                                                                     
Read-only and updatable object materialized views. You must ensure that the master table has a primary key.                                                                                                                                                                            
                                                                                                                                                                                                                                                                                       
只读且可更新并具有主键的物化视图                                                                                                                                                                                                                                                                                     
Read-only and updatable primary key materialized views.                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                       
只读rowid的物化视图                                                                                                                                                                                                                                                                                     
Read-only rowid materialized views.                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                                       
索引组织表子句和create table具有相同的语法                                                                                                                                                                                                                                                                                       
The keywords and parameters of the index_org_table_clause have the same semantics as described in CREATE TABLE, with the restrictions that follow. 

索引组织物化视图一些操作限制
Restrictions on Index-Organized Materialized Views Index-organized materialized views are subject to the following restrictions:                                                                                                                                                                                                

不能指定cache,nocache,cluster,on prebuilt table                                                                                                                                                                                         
You cannot specify the following CREATE MATERIALIZED VIEW clauses: CACHE or NOCACHE, CLUSTER, or ON PREBUILT TABLE.                                                                         
                                                                                                                                                                                            
不能指定mapping_table子句                                                                                                                                                                                          
In the index_org_table_clause:                                                                                                                                                              
 ?                                                                                                                                                                                          
You cannot specify the mapping_table_clause.                                                                                                                                                
                                                                                                                                                                                            
仅可基于组合主键的物化视图指定compress,但可基于单一或组合主键的物化视图指定nocompress(因单列无须压缩,而多列可压缩)                                                                                                                                                                                         
You can specify COMPRESS only for a materialized view based on a composite primary key. You can specify NOCOMPRESS
for a materialized view based on either a simple or composite primary key.    

物化视图特性
materialized_view_props                                                                                                                                                                              
用此子句定制基于非已存在的表的物化视图.如为了基于已存在的表创建物化视图,使用on prebuilt table子句                                                                                                                                                                                                                                                                                                                                                                                                 
Use these property clauses to describe a materialized view that is not based on an existing table.
To create a materialized view that is based on an existing table, use the ON PREBUILT TABLE clause. 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-754475/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-754475/