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.

oracle物化檢視系列(二)

相關文章