一、创建物化视图的权限
CREATE
MATERIALIZED
VIEW、
CREATE
TABLE
or CREATE
ANY
TABLE
,对master table 有 select或者select any table的权限
如果创建refresh-on-commit materialized view ,还需要
ON
COMMIT
REFRESH
权限
如果创建的视图query rewrite enabled,还需要GLOBAL
QUERY
REWRITE or
QUERY
REWRITE
If you are defining the materialized view on a prebuilt container (ON
PREBUILT
TABLE
clause), then you must have the SELECT
privilege WITH
GRANT
OPTION
on the container table.
二、本地物化视图的类型
-
Materialized aggregate views, including materialized aggregate views on a single table
-
Materialized join views
-
Primary-key-based and rowid-based single table materialized views
-
UNION
ALL
materialized views, where eachUNION
ALL
branch is one of the above materialized view types
三、基本语法
CREATE MATERIALIZED VIEW schema.materialized_view 【build IMMEDIATE | bulid DEFERRED 】
as
select * From table(master table);
四、解释
build方式:
BUILD IMMEDIATE:在创建物化视图的时候就生成数据。如何忽略,不知道build 方式,默认是按照build immediate方式创建物化视图
BUILD DEFERRED :在创建物化视图的时候不生成数据,在第一次刷新的时候产生数据。
刷新方式:
fast:根据主表的变化增量刷新物化视图。使用REFRESH
FAST 物化视图 必须在master table上建立materialized view logs,否则会创建失败
如果物化视图的select查询中包含分析函数,对于fast refresh 来说不合法的。
COMPLETE :
指对物化视图中的数据实现完全刷新。即使可以使用fast刷新方式。
FORCE :当刷新出现,会首先以fast增量刷新的方式刷新,如果fast刷新不可用,然后以complete方式尝试刷新.
刷新时间:
ON COMMIT:当master table 表里的数据数据出现commit时候,物化视图就会以fast方式刷新,这也会增加commit的时间,因为数据库执行刷新操作作为commit操作的一部分。 You cannot specify both ON
COMMIT
and ON
DEMAND
. If you specify ON
COMMIT
, then you cannot also specify START
WITH
or NEXT
.
on commit 刷新方式的限制:
1、不支持on commit物化视图中包含 object types or Oracle-supplied type
2、不支持on commit物化视图中有remote tables
3、on commit 物化视图创建后,不能向 物化视图中所包含的master table执行分布式事物,例如你不能向master中插入select remote table的数据。 on demand 支持分布式事物。
ON DEMAND:指定物化视图的刷新方式是手动刷新,不会自动刷新。你不能同时指定物化视图的刷新方式是on commit 、on demand。如果在创建物化视图的时候既没有指定on commit刷新方式,也没有指定on demand 刷新方式,那么默认按照on demand 刷新方式进行刷新。 start with and next 刷新方式会覆盖 on demand 刷新方式。因为start with and next 刷新方式优先级高于on demand
START WITH:自定自动刷新的起点时间
NEXT:指定自动刷新的间隔,start with and next合起来确定下次刷新的时间,如果在忽略了sart with ,只有next,那么刷新时间依据物化视图创建的时间和next来确定下次刷新的时间,如果只有start with 没有next,那么就会刷新一次。如果忽略了start with and next 、on commit,那么不会自动刷新
WITH PRIMARY KEY:创建主键物化视图,这是默认的创建物化视图的方式,除了小部分master table没有主键创建rowid物化视图,大部分创建主键物化视图。主键物化视图在以fast 刷新方式下,让物化视图的master table 被识别,不影响物化视图的合法性。master table必须包含enable primary key 约束。在创建物化视图的时候必须指明主键列,并且主键不能使用函数,例如upper();
WITH
OBJECT
ID刷新物化视图
WITH ROWID:创建rowid 物化视图,在物化视图中不包含所有master table 的主键列的时候,创建rowid物化视图时非常有用的。rowid物化视图只能基于单表创建物化视图。rowid物化视图不能包含如下限制
-
Distinct or aggregate functions
-
GROUP
BY
or
CONNECT
BY
clauses -
Subqueries
-
Joins
-
Set operations
Rowid materialized views are not eligible for fast refresh after a master table reorganization until a complete refresh has been performed.
rowid物化视图的限制:不能隐式使用
WITH
OBJECT
ID刷新物化视图
QUERY REWRITE :查询重写是指当对实体化视图的基表进行查询时,Oracle会自动判断能否通过查询实体化视图来得到结果,如果可以,则避免了聚集或连接操作,而直接从已经计算好的实体化视图中读取数据,
默认是 disable,是创建的物化视图对于查询重写是否合法,物化视图在创建后要使用dbms_stats收集统计信息,数据库根据统计信息优化查询重写。
查询重写的限制:
1、查询重写物化视图中的select语句中不能包含rownum、user\sysdate、remote table、sequence、pl/sql函数
2、物化视图里的lselect 语句中不能包含远程物化视图,或者远程表。
打开查询重写的限制:
-
You can enable query rewrite only if all user-defined functions in the materialized view are
DETERMINISTIC
. -
You can enable query rewrite only if expressions in the statement are repeatable. For example, you cannot include
CURRENT_TIME
orUSER
, sequence values (such as theCURRVAL
orNEXTVAL
pseudocolumns), or theSAMPLE
clause (which may sample different rows as the contents of the materialized view change).
prebuild:把物化视图注册到现有的表上,master table 必须和物化视图同名,并且在同一个schema
在下列情况下使用
-
You have a table representing the result of a query. Creating the table was an expensive operation that possibly took a long time. You want to create a materialized view on the query. You can use the
ON
PREBUILT
TABLE
clause to avoid the expense of executing the query and populating the container for the materialized view. -
You temporarily discontinue having a materialized view, but keep its container table, using the
DROP
MATERIALIZED
VIEW
...PRESERVE
TABLE
statement. You then decide to recreate the materialized view and you know that the master tables of the view have not changed. You can create the materialized view using theON
PREBUILT
TABLE
clause. This avoids the expense and time of creating and populating the container table for the materialized view.
-
Each column alias in
subquery
must correspond to a column in the prebuilt table, and corresponding columns must have matching data types. -
If you specify this clause, then you cannot specify a
NOT
NULL
constraint for any column that is not referenced insubquery
unless you also specify a default value for that column.
物化视图select 语句的限制:
-
The defining query of a materialized view can select from tables, views, or materialized views owned by the user
SYS
, but you cannot enableQUERY
REWRITE
on such a materialized view.当物化视图的master table是sys下的物化视图的时候,不支持查询重写 -
You cannot define a materialized view with a subquery in the select list of the defining query. You can, however, include subqueries elsewhere in the defining query, such as in the
WHERE
clause.你不能在物化视图的select 列表中再定义查询。 -
You cannot use the
AS
OF
clause of theflashback_query_clause
in the defining query of a materialized view. -
Materialized join views and materialized aggregate views with a
GROUP
BY
clause cannot select from an index-organized table. join物化视图和累积物化视图中有group by时,master table 不能使索引组织表。 -
Materialized views cannot contain columns of data type
LONG
.物化视图不支持long 类型的 -
Materialized views cannot contain virtual columns. 物化视图不支持虚拟咧
-
You cannot create a materialized view log on a temporary table. Therefore, if the defining query references a temporary table, then this materialized view will not be eligible for
FAST
refresh, nor can you specify theQUERY
REWRITE
clause in this statement. 如果物化视图中涉及临时表,那么此物化视图是不合法的,不支持fast刷新方式,也不支持查询重写。 -
If the
FROM
clause of the defining query references another materialized view, then you must always refresh the materialized view referenced in the defining query before refreshing the materialized view you are creating in this statement. 如果物化视图的master table 中有物化视图,那么必须先更新依赖的物化视图,然后在更新物化视图。 -
Materialized views with join expressions in the defining query cannot have XML data type columns. The XML data types include
XMLType
and URI data type columns. join 物化视图中不能包含xml类型的数据类型
五、创建物化视图日志
物化视图以fast方式刷新物化视图,必须建立物化视图日志。
new_values_clause:
如上面的定义物化视图的日志的方式有五种:object id、primary key、 rowid、sequence、 commit scn;
如果不指定物化视图日志按照那种方式创建,默认 with primary 方式创建
COMMIT SCN:如果不指定commit scn方式,那么物化视图日志会基于 timestamps 方式和其他操作,来刷新物化视图,指定commit scn方式来会加快物化视图的刷新。
You can create the following types of local materialized views (including both ON
COMMIT
and ON
DEMAND
) on master tables with commit SCN-based materialized view logs:
-
Materialized aggregate views, including materialized aggregate views on a single table
-
Materialized join views
-
Primary-key-based and rowid-based single table materialized views
-
UNION
ALL
materialized views, where eachUNION
ALL
branch is one of the above materialized view types
You cannot create remote materialized views on master tables with commit SCN-based materialized view logs.
commit scn的限制:
1、 on commit scn 物化视图日志不能创建在含有lob列的master table 上,否则会报ORA-32421
.的错误;
2、在master table中既有基于timestamps 日志,又有commit scn的日志,那么此master表不支持创建物化视图,并且会报 ORA-32414
.错误。
上面with子句的限制:
1、你只能指定一个 PRIMARY KEY, one ROWID, one OBJECT ID, one SEQUENCE, and one column list给 每一个物化视图日志。
2、由于主键是隐式记录在物化视图日志的,所以在一个物化视图日志中不要组合多个主键列
sequence:Specify SEQUENCE to indicate that a sequence value providing additional ordering information should be recorded in the materialized view log. Sequence numbers are necessary to support fast refresh after some update scenarios.
NEW VALUES:物化视图的日志保存dml操作update的新值和旧值
INCLUDING :保存新值和旧值到日志中; If this log is for a table on which you have a single-table materialized aggregate view, and if you want the materialized view to be eligible for fast refresh, then you must specify INCLUDING.
EXCLUDING:不记录update后的新值到日志中, This is the default. You can use this clause to avoid the overhead of recording new values. Do not use this clause if you have a fast-refreshable single-table materialized aggregate view defined on the master table.
六、examlpe
创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON customers WITH PRIMARY KEY, ROWID;
CREATE MATERIALIZED VIEW LOG ON orders PCTFREE 5 TABLESPACE example STORAGE (INITIAL 10K) PURGE REPEAT INTERVAL '5' DAY
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, SEQUENCE(amount_sold, time_id, prod_id) INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON order_items WITH (product_id);
CREATE MATERIALIZED VIEW LOG ON product_information WITH ROWID, SEQUENCE (list_price, min_price, category_id), PRIMARY KEY INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW products_mv REFRESH FAST ON COMMIT AS SELECT SUM(list_price - min_price), category_id FROM product_information GROUP BY category_id;快速刷新的累积物化视图必须 创建含有
SEQUENCE
and
INCLUDING
NEW
VALUES的物化视图日志;
子查询物化视图:
CREATE MATERIALIZED VIEW foreign_customers FOR UPDATE AS SELECT * FROM sh.customers@remote cu WHERE EXISTS (SELECT * FROM sh.countries@remote co WHERE co.country_id = cu.country_id);
创建累积物化视图:
CREATE MATERIALIZED VIEW LOG ON times WITH ROWID, SEQUENCE (time_id, calendar_year) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON products WITH ROWID, SEQUENCE (prod_id) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW sales_mv BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales FROM times t, products p, sales s WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id GROUP BY t.calendar_year, p.prod_id;
创建join 物化视图
CREATE MATERIALIZED VIEW sales_by_month_by_state TABLESPACE example PARALLEL 4 BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT t.calendar_month_desc, c.cust_state_province, SUM(s.amount_sold) AS sum_sales FROM times t, sales s, customers c WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id GROUP BY t.calendar_month_desc, c.cust_state_province;
创建prebuild 物化视图:
CREATE TABLE sales_sum_table (month VARCHAR2(8), state VARCHAR2(40), sales NUMBER(10,2)); CREATE MATERIALIZED VIEW sales_sum_table ON PREBUILT TABLE WITH REDUCED PRECISION ENABLE QUERY REWRITE AS SELECT t.calendar_month_desc AS month, c.cust_state_province AS state, SUM(s.amount_sold) AS sales FROM times t, customers c, sales s WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id GROUP BY t.calendar_month_desc, c.cust_state_province;
创建主键物化视图:
CREATE MATERIALIZED VIEW catalog REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/4096 WITH PRIMARY KEY AS SELECT * FROM product_information;创建rowid物化视图
CREATE MATERIALIZED VIEW order_data REFRESH WITH ROWID AS SELECT * FROM orders;创建定期刷新物化视图:
CREATE MATERIALIZED VIEW LOG ON employees WITH PRIMARY KEY INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW emp_data PCTFREE 5 PCTUSED 60 TABLESPACE example STORAGE (INITIAL 50K) REFRESH FAST NEXT sysdate + 7 AS SELECT * FROM employees;创建快速刷新物化视图:
CREATE MATERIALIZED VIEW LOG ON inventories WITH (quantity_on_hand); CREATE MATERIALIZED VIEW warranty_orders REFRESH FAST AS SELECT order_id, line_item_id, product_id FROM order_items o WHERE EXISTS (SELECT * FROM inventories i WHERE o.product_id = i.product_id AND i.quantity_on_hand IS NOT NULL) UNION SELECT order_id, line_item_id, product_id FROM order_items WHERE quantity > 5;
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6002.htm#SQLRF01302
http://docs.oracle.com/cd/E11882_01/server.112/e25789/schemaob.htm#CFAIGHFC
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6003.htm#SQLRF01303
http://www.xifenfei.com/560.html