物化视图

一、创建物化视图的权限

 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 each UNION 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 物化视图中包含多个master table，那么此物化视图无效。

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 or USER, sequence values (such as the CURRVAL or NEXTVAL pseudocolumns), or the SAMPLE 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 the ON PREBUILT TABLE clause. This avoids the expense and time of creating and populating the container table for the materialized view.

prebuild的限制：

• 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 in subquery 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 enable QUERYREWRITE 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 the flashback_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 the QUERY 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 each UNION 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