CREATE MATERIALIZED VIEW
官方文件/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

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

官方文件/B19306_01/server.102/b14200/statements_6002.htm#i2092101

oracle物化檢視系列(一)

相關文章