物化视图学习

物化视图目的

1复制，物化视图可以让你维护远处的数据拷贝在你的节点上，数据可以使用高级复制特点来更新，否则是只读的。

2数据仓库环境，物化视图通常创建物化聚合视图，单表物化聚合视图和物化连接视图，这三种物化视图可以用来查询重写。

当创建物化视图，oracle会创建一个内部表和至少一个索引，也许会创建一个视图，oracle用这些对象来维护物化视图。

物化视图日志：当master表的数据改动时，oracle存储描述这些改变的行到物化视图日志，然后使用物化视图日志来刷新物化视图。这个步骤叫增量或快速刷新。没有物化视图日志，oracle必须重新执行物化视图查询来刷新物化视图，这个过程叫做完全刷新，快速刷新通常比完全刷新要花更少时间。

物化视图日志与master在同一个schema下，一个master表只能有一个物化视图日志。为了快速刷新一个物化联合视图，你必须为物化视图涉及的每一个表创建一个物化视图日志。

物化视图类别

• Primary Key Materialized Views

• Object Materialized Views

• ROWID Materialized Views

• Complex Materialized Views

在创建物化视图的时候，建议加上对象的schema

主键物化视图是物化视图的默认类型，如果物化视图作为物化视图组一部分并且定义物化视图的时候指定了for update，则物化视图是可更新的。变动的传播是根据行及的改变传播的，下面是一个创建可更新主键物化视图

create materailized view oe.customers for update as select * from oe.customer@orcl.word;

主键物化视图可以包含子查询。

如果一个物化视图是基于一个对象表，在创建的时候使用了of，那这个就是对象物化视图，对象物化视图与对象表是相同的组织方式，也就是一个对象物化视图由行对象组成，每一个行对象被object identifier列标识。

一个rowid物化视图基于物理行标识rowid,rowid物化视图可以用在没有主键的表上，或是物化视图中不包含主键列的视图。

CREATE MATERIALIZED VIEW oe.orders REFRESH WITH ROWID AS SELECT * FROM oe.orders@orc1.world;

复杂的物化视图，为了快速刷新，物化视图的查询都有特定的限制，如果物化视图的查询没有限制那么这个就是复杂物化视图，复杂物化视图不能快速刷新。当定义的查询中包含下面的条件时，就是复杂视图：

1 connect by 从句

2intersect,minus or union all集合操作

3distinct 或unique关键字

4一些聚合函数

5一些情况下在子查询中的连接

6一些情况下的union操作。

创建物化聚合视图的例子：

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;
 

创建物化联合视图例子：

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;

创建prebuilt物化视图

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;

Creating Subquery Materialized Views: Example The following statement creates a subquery materialized view based on the customers and countries tables in the sh schema at the remote database:

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);

Creating Primary Key Materialized Views: Example The following statement creates the primary key materialized view catalog on the sample table oe.product_information:

CREATE MATERIALIZED VIEW catalog   
   REFRESH FAST START WITH SYSDATE NEXT  SYSDATE + 1/4096 
   WITH PRIMARY KEY 
   AS SELECT * FROM product_information;  

Creating Rowid Materialized Views: Example The following statement creates a rowid materialized view on the sample table oe.orders:

CREATE MATERIALIZED VIEW order_data REFRESH WITH ROWID 
   AS SELECT * FROM orders; 

Periodic Refresh of Materialized Views: Example The following statement creates the primary key materialized view emp_data and populates it with data from the sample table hr.employees:

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 NEXT 50K)
   REFRESH FAST NEXT sysdate + 7 
   AS SELECT * FROM employees; 

The statement does not include a START WITH parameter, so Oracle Database determines the first automatic refresh time by evaluating the NEXT value using the current SYSDATE. A materialized view log was created for the employee table, so Oracle Database performs a fast refresh of the materialized view every 7 days, beginning 7 days after the materialized view is created.

Because the materialized view conforms to the conditions for fast refresh, the database will perform a fast refresh. The preceding statement also establishes storage characteristics that the database uses to maintain the materialized view.

Automatic Refresh Times for Materialized Views: Example The following statement creates the complex materialized view all_customers that queries the employee tables on the remote and local databases:

CREATE MATERIALIZED VIEW all_customers
   PCTFREE 5 PCTUSED 60 
   TABLESPACE example 
   STORAGE (INITIAL 50K NEXT 50K) 
   USING INDEX STORAGE (INITIAL 25K NEXT 25K)
   REFRESH START WITH ROUND(SYSDATE + 1) + 11/24 
   NEXT NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 15/24 
   AS SELECT * FROM sh.customers@remote 
         UNION
      SELECT * FROM sh.customers@local; 

Oracle Database automatically refreshes this materialized view tomorrow at 11:00 a.m. and subsequently every Monday at 3:00 p.m.

默认的刷新方法是force，定义的查询包含了一个union，这个操作不被fast refresh支持，所以数据库会自动的做完全刷新。

Creating a Fast Refreshable Materialized View: Example The following statement creates a fast-refreshable materialized view that selects columns from the order_items table in the sample oe schema, using the UNION set operator to restrict the rows returned from the product_information and inventories tables using WHERE conditions. The materialized view logs for order_items and product_information were created in the "Examples" section of CREATE MATERIALIZED VIEW LOG. This example also requires a materialized view log on oe.inventories.

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; 

This materialized view requires that materialized view logs be defined on order_items (with product_id as a join column) and on inventories (with quantity_on_hand as a filter column). See "Specifying Filter Columns for Materialized View Logs: Example" and "Specifying Join Columns for Materialized View Logs: Example".

Creating a Nested Materialized View: Example The following example uses the materialized view from the preceding example as a master table to create a materialized view tailored for a particular sales representative in the sample oe schema:

CREATE MATERIALIZED VIEW my_warranty_orders
   AS SELECT w.order_id, w.line_item_id, o.order_date
   FROM warranty_orders w, orders o
   WHERE o.order_id = o.order_id
   AND o.sales_rep_id = 165;