Materialized View Refresh Option

The following examples require the materialized logs that are created in the "Examples" section of CREATE MATERIALIZED VIEW.

Creating Materialized Aggregate Views: Example The following statement creates and populates a materialized aggregate view on the sample sh.sales table and specifies the default refresh method, mode, and time. It uses the materialized view log created in "Creating a Materialized View Log: Examples", as well as the two additional logs shown here:

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;

Creating Materialized Join Views: Example The following statement creates and populates the materialized aggregate view sales_by_month_by_state using tables in the sample sh schema. The materialized view will be populated with data as soon as the statement executes successfully. By default, subsequent refreshes will be accomplished by reexecuting the defining query of the materialized view:

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;

Creating Prebuilt Materialized Views: Example The following statement creates a materialized aggregate view for the preexisting summary table, sales_sum_table:

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;

In this example, the materialized view has the same name and also has the same number of columns with the same datatypes as the prebuilt table. The WITH REDUCED PRECISION clause allows for differences between the precision of the materialized view columns and the precision of the values returned by the subquery.

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. The default refresh method is FORCE. The defining query contains a UNION operator, which is not supported for fast refresh, so the database will automatically perform a complete refresh.

The preceding statement also establishes storage characteristics for both the materialized view and the index that the database uses to maintain it:

• The first STORAGE clause establishes the sizes of the first and second extents of the materialized view as 50 kilobytes each.

• The second STORAGE clause, appearing with the USING INDEX clause, establishes the sizes of the first and second extents of the index as 25 kilobytes each.

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; 

The NEW VALUES clause lets you determine whether Oracle Database saves both old and new values for update DML operations in the materialized view log.

See Also:

"Including New Values in Materialized View Logs: Example"

INCLUDING Specify INCLUDING to save both new and old values in the log. 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 Specify EXCLUDING to disable the recording of new values in the log. 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.

Examples

Creating a Materialized View Log: Examples The following statement creates a materialized view log on the oe.customers table that specifies physical and storage characteristics:

CREATE MATERIALIZED VIEW LOG ON customers 
   PCTFREE 5 
   TABLESPACE example 
   STORAGE (INITIAL 10K NEXT 10K); 

This materialized view log supports fast refresh for primary key materialized views only. The following statement creates another version of the materialized view log with the ROWID clause, which enables fast refresh for more types of materialized views:

CREATE MATERIALIZED VIEW LOG ON customers WITH PRIMARY KEY, ROWID; 

This materialized view log makes fast refresh possible for rowid materialized views and for materialized join views. To provide for fast refresh of materialized aggregate views, you must also specify the SEQUENCE and INCLUDING NEW VALUES clauses, as shown in the next statement.

Specifying Filter Columns for Materialized View Logs: Example The following statement creates a materialized view log on the sh.sales table and is used in "Creating Materialized Aggregate Views: Example". It specifies as filter columns all of the columns of the table referenced in that materialized view.

CREATE MATERIALIZED VIEW LOG ON sales 
   WITH ROWID, SEQUENCE(amount_sold, time_id, prod_id)
   INCLUDNG NEW VALUES; 

Specifying Join Columns for Materialized View Logs: Example The following statement creates a materialized view log on the order_items table of the sample oe schema. The log records primary keys and product_id, which is used as a join column in "Creating a Fast Refreshable Materialized View: Example".

CREATE MATERIALIZED VIEW LOG ON order_items WITH (product_id);

Including New Values in Materialized View Logs: Example The following example creates a materialized view log on the oe.product_information table that specifies INCLUDING NEW VALUES:

CREATE MATERIALIZED VIEW LOG ON product_information 
   WITH ROWID, SEQUENCE (list_price, min_price, category_id), PRIMARY KEY
   INCLUDING NEW VALUES;

You could create the following materialized aggregate view to use the product_information log:

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;

 

 

안녕하세요. 박정진님 Materialized view는 기존의 snapshot의 synonym으로서의 기능에 Data warehousing의 기능을 upgrade하여 만들어졌습니다. 그러나 많은 장점이 있지만 refresh와 query rewrite의 두가지 주요한 특징이 있습니다. 그러나 on commit refresh는 log 생성시 많은 부담으로 많은 건의 DML작업이 발생하는 경우에는 ON COMMIT REFRESH를 피하는 경우가 많습니다. 회원님께서 1) QUERY REWRITE 권한 부여 2) BASE TABLE(point_log_tblw)에 대한 LOG 생성 3) 아래의 Materialized view생성하신 것으로 판단이 됩니다. CREATE MATERIALIZED VIEW point_daily_mvw BUILD IMMEDIATE REFRESH ON COMMIT ENABLE QUERY REWRITE AS SELECT chaNo,to_char(regDate, 'YYYYMMDD') As daily, count(*) cnt, sum(markPoint) As dailyPoint FROM point_log_tblw GROUP BY chaNo, to_char(regDate, 'YYYYMMDD'); Materialized view는 오라클 version별로 약간씩 다르지만 위와 같은 Single-Table Aggregate Materialized View의 경우에는 아래와 같은 제약사항이 있습니다. < Single-Table Aggregate Materialized View에서 aggregate function 간 상관관계> aggregate X 존재시 aggregate Y 는 필수, aggregate Z 는 optional ----------------------------------------------------------- | X Y Z | |----------------------------------------------------------| | COUNT(expr) | - | | | SUM(expr) | COUNT(expr) | - | | AVG(expr) | COUNT(expr) | SUM(expr) | | STDDEV(expr) | COUNT(expr) | SUM(expr * expr) | | VARIANCE(expr) | COUNT(expr) | SUM(expr * expr) | ------------------------------------------------------------ 즉 sum(expr)이 생성하려면 count(expr)도 필수조건입니다. 아래와 같이 count(markPoint)를 추가해서 생성하시기 바랍니다. CREATE MATERIALIZED VIEW point_daily_mvw BUILD IMMEDIATE REFRESH ON COMMIT ENABLE QUERY REWRITE AS SELECT chaNo,to_char(regDate, 'YYYYMMDD') As daily, count(*) cnt, sum(markPoint) As dailyPoint, count(markPoint) dailyCnt FROM point_log_tblw GROUP BY chaNo, to_char(regDate, 'YYYYMMDD'); 제약사항에 대한 내용은 버젼별 매뉴얼을 참조하시고 추가적인 의문사항이 있으면 연락주세요. (질문시 오라클 환경(version등)을 표기해 주시기 바랍니다) 도움이 되셨길 바랍니다. >> 박정진 님께서 작성한 글입니다 --------------------------------------------------------------------------------- 특정제품에 대한 자문은 이곳에 올리는게 아닌줄은 알지만-_-;; 따로 조언을 구할 곳이 없어서 실례_ _를 무릅쓰고 질문을 드립니다. 얼마전에 열린기술광장에 MATERIALIZED VIEW에 대한 사용법에 대한 질문을 올리고 고맙게 답변해주신 분이 있어 해 보라는 대로 해보고 며칠동안 OTN 등의 자료도 더 많이 찾아보 았지만.. 이상하게도 MATERIALIZED VIEW 의 좋은 장점에도 불구하고 대부분 쓰는 곳이 없어서.. 온라인 메뉴얼 이상의 자료(이런저런 장점 나열, 이렇게 만드면 된다고 함 -> 해보면 안됨-_-) 의 를 찾기가 어려군요.. CREATE MATERIALIZED VIEW point_daily_mvw BUILD IMMEDIATE REFRESH ON COMMIT ENABLE QUERY REWRITE AS SELECT point_log_tblw.chaNo AS chaNo , to_char(regDate, 'YYYYMMDD') As daily, count (*) cnt, sum(markPoint) As dailyPoint FROM point_log_tblw GROUP BY chaNo, to_char(regDate, 'YYYYMMDD'); 적절한 권한설정(any snapshot 권한뿐 아니라 DBA 권한도 줘 보고 -_-, 스케쥴프로세스는 5개가 떠 있습니다. ) 그러나 위와 같은 문을 실행하면 ORA-12054 : 구체화된 뷰에 ON COMMIT 재생 속성을 설정할수 없습니다. 오류가 납니다. 결국 MATERIALIZED VIEW를 사용하는 대부분의 이유가 OLAP 라고는 하지만 한번 만들고 기초테이블과 싱크가 맞지 않는다면.. 그 효용성은 크게 반감되 리라는.. 쿨럭 -_- 얘기가 샜군요.. 대체 ON COMMIT 재생 속성은 언제 적용이 되고 적용하는 스크립트를 어떻게 만들어야 하는지요... ---------------------------------------------------------------------------------

Reference :  
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm
http://www.dator.co.kr/know_site/53893 

which : 어느, …하는, 어느 것이든지, 어떤, 어느 쪽의