Toad World Blog

Oracle Database 12.2 Statement-level Refresh for Materialized Views

Aug 28, 2017 11:58:00 AM by Deiby Gomez

Introduction

Materialized views have been used for several years and they are being improved by Oracle with every database version or release. Up to Oracle Database 12cR1 Oracle Materialized Views supported the following refreshes:

  • ON DEMAND:You can control the time of refresh of the materialized views.
    • COMPLETE: Refreshes by recalculating the defining query of the materialized view.
    • FAST: Refreshes by incrementally applying changes to the materialized view.
    • For local materialized views, it chooses the refresh method that is estimated by optimizer to be most efficient. The refresh methods considered are log-based FAST and FAST_PCT.
    • FAST_PCT: Refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables.
    • FORCE: Attempts a fast refresh. If that is not possible, it does a complete refresh.
  • ON COMMIT: Whenever a transaction commits which has updated the tables on which a materialized view is defined, those changes are automatically reflected in the materialized view. The only disadvantage is that the time required to complete the commit will be slightly longer because of the extra processing involved.

Starting with Oracle 12cR2, Materialized views can be refreshed ON STATEMENT.

  • ON STATEMENT: With this refresh mode, any changes to the base tables are immediately reflected in the materialized view. There is no need to commit the transaction or maintain materialized view logs on the base tables. If the DML statements are subsequently rolled back, then the corresponding changes made to the materialized view are also rolled back.

In the following graphic we can see that in the syntax the option “ON STATEMENT” was introduced:

To use an ON STATEMENT materialized view the following restrictions must be cleared:

  • They are for materialized join view only.
  • Base tables referenced in the materialized view defining query must be connected in a join graph of star/snowflake shape.
  • An existing non-ON-STATEMENT materialized view cannot be converted to REFRESH ON STATEMENT.
  • Altering an existing ON STATEMENT materialized view is not allowed.
  • An ON STATEMENT materialized view cannot be created under SYS
  • AN ON STATEMENT materialized view needs to be fast refreshable. You must specify the clause ‘REFRESH FAST’ in the CREATE MATERIALIZED VIEW command. materialized view logs are not required.
  • The defining query needs to include the ROWID column of the fact table in the SELECT list.
  • Be careful with UPDATE operations, because these are not supported on any dimension table. It will make the ON STATEMENT materialized view unusable.
  • TRUNCATE operations on a base table are not supported. They will make the ON STATEMENT materialized view unusable.
  • The defining query should NOT include:
    • invisible column
    • ANSI join syntax
    • complex defining query
    • (inline) view as base table
    • composite primary key
    • long/LOB column

Every type of refresh mode has its own restrictions; it is difficult to memorize every single restriction for every refresh mode. If you are getting errors like “ORA-12052: cannot fast refresh materialized view” it’s likely that you are forgetting to clear a restriction. To make this task easier, you can always visit the note Materialized View Fast Refresh Restrictions and ORA-12052 (Doc ID 222843.1), where you will find every single restriction for all the refresh modes.

So enough of the basic concepts of materialized views; it’s time for an example. In the following example I am using Oracle Database Enterprise Edition 12.2.0.1 and creating four tables. Then I will create two materialized views, one ON COMMIT and one ON STATEMENT. I will insert some rows in each of the four tables without committing them. We will query the ON STATEMENT materialized view, analyze the result, and then we will commit the data to finally query the ON COMMIT materialized view and its result.

 

Creating the tables:

SQL> CREATE TABLE employee (
employee_id number,
name varchar2(20),
phone number,
position varchar2(20),
CONSTRAINT employee_pk PRIMARY KEY (employee_id));

Table created.

SQL> CREATE TABLE department (
department_id number,
name varchar2(20),
CONSTRAINT department_pk PRIMARY KEY (department_id));

Table created.

SQL> CREATE TABLE product (
product_id number,
name varchar2(20),
price number(*,2),
CONSTRAINT product_pk PRIMARY KEY (product_id));

Table created.

SQL> CREATE TABLE purchase (
purchase_code number,
department_id number,
employee_id number,
product_id number,
amount number,
purchase_date date,
CONSTRAINT purchase_pk PRIMARY KEY (purchase_code),
FOREIGN KEY (department_id) REFERENCES department (department_id),
FOREIGN KEY (employee_id) REFERENCES employee (employee_id),
FOREIGN KEY (product_id) REFERENCES product (product_id));

Table created.

The advantage of ON STATEMENT materialized views is that there is no need to create materialized view logs in order to create them:

SQL> CREATE MATERIALIZED VIEW onstatement_purchases
REFRESH FAST ON STATEMENT
AS
SELECT p.rowid rid, e.name, p.purchase_code, pr.product_id, p.amount
FROM department d, employee e, purchase p, product pr
WHERE d.department_id=p.department_id and
pr.product_id=p.product_id and
e.employee_id=p.employee_id;

Materialized view created.

One of the disadvantages of using ON COMMIT materialized views is that materialized view logs must be created with “INCLUDING NEW VALUES” and “WITH ROWID” as well as including all the columns that will be referenced inside the materialized view.

CREATE MATERIALIZED VIEW LOG ON purchase WITH PRIMARY KEY,ROWID,
SEQUENCE(department_id,employee_id,product_id,amount,purchase_date) INCLUDING NEW VALUES;


CREATE MATERIALIZED VIEW LOG ON department WITH PRIMARY KEY,ROWID, SEQUENCE(name) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON employee WITH PRIMARY KEY,ROWID, SEQUENCE(name,phone,position ) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON product WITH PRIMARY KEY,ROWID, SEQUENCE(name,price) INCLUDING NEW VALUES;

 

Creating the ON COMMIT materialized view:

SQL> CREATE MATERIALIZED VIEW oncommit_purchases
REFRESH FAST ON COMMIT
AS
SELECT e.name, p.purchase_code, pr.product_id, p.amount
FROM department d, employee e, purchase p, product pr
WHERE d.department_id=p.department_id and
pr.product_id=p.product_id and
e.employee_id=p.employee_id
group by e.name, p.purchase_code, pr.product_id, p.amount;

Materialized view created.

 

Verifying the refresh mode of each materialized view:

SQL> select owner, mview_name, REFRESH_MODE from dba_mviews where owner='DGOMEZ'

OWNER      MVIEW_NAME                REFRESH_MODE
---------- ------------------------- ------------
DGOMEZ     ONCOMMIT_PURCHASES        COMMIT
DGOMEZ     ONSTATEMENT_PURCHASES     STATEMENT

Now I will insert some rows without committing them:

SQL> Insert into employee values (1,'Jose',55555555,'Manager');
1 row created.

SQL> Insert into department values (1,'Sales');
1 row created.

SQL> Insert into product values (1,'Soda',100.50);
1 row created.

SQL> insert into purchase values (1,1,1,1,100,sysdate);
1 row created.

I will query the materialized view onstatement_purchases and we will see that It was populated:

NAME                 PURCHASE_CODE PRODUCT_ID AMOUNT
-------------------- ------------- ---------- ----------
Jose                             1          1       100

 

However the ON COMMIT materialized view oncommit_purchases is empty:

SQL> select name, purchase_code, product_id, amount from oncommit_purchases;
no rows selected

I will commit the rows:

SQL> commit;
Commit complete.

As soon as the rows are committed, the ON COMMIT materialized view is populated:

SQL> select name, purchase_code, product_id, amount from oncommit_purchases;

NAME                 PURCHASE_CODE PRODUCT_ID AMOUNT
-------------------- ------------- ---------- ----------
Jose                             1          1        100

 

Conclusion

Materialized views are frequently used to improve the performance of complex queries and are very popular. Oracle has been improving them, and with the introduction of ON STATEMENT materialized views, DBAs will have one more option they can use to meet client requirements or solve performance issues. In this article we looked at some basic concepts of materialized views, and two examples: an ON STATEMENT materialized view, where we saw that without to commit the data the materialized view was populated, and an ON COMMIT materialized  view, which needed the commit instruction to get populated.

Tags: Oracle

Deiby Gomez

Written by Deiby Gomez

Deiby Gómez is the first Oracle ACE Director of Guatemala. He has the highest technical certification in the world: "Oracle Certified Master 11g", "Oracle Certified Master 12c" and "Maximum Availability Architecture Oracle Certified Master 12c", he is the first person ever in Central America with all these certifications. Deiby likes to work with complex scenarios, huge and highly available critical databases where a deep knowledge of Oracle is needed. Deiby also has strong knowledge on Oracle Fusion Middleware and Oracle Cloud (PaaS & IaaS). Deiby was the winner of "IOUG SELECT Journal Editor’s Choice Award 2016" in Las Vegas, USA. He is a frequent speaker in Oracle Events around the World like OTN LAD Tour '13, '14, '15, '16, '17 (Colombia, Guatemala, El Salvador, Ecuador, Uruguay, Argentina, Brazil, Perú, Mexico, Costa Rica); Collaborate in Las Vegas, USA and Oracle Open World '15, '16, '17 (Brazil and USA). He was the first Guatemalan accepted as Beta Tester (12cR2) in San Francisco in 2015. Several articles have been published by him in English, Spanish and Portuguese in Oracle’s website, Toad World, and his own blog. Deiby appeared in the Official "Oracle Magazine" in Nov/Dec 2014 Edition as an outstanding expert. Deiby is the Technical Reviewer of the book “Oracle Database 12cR2 Multitenant - Oracle Press” and he is co-author of the book “Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability - Oracle Press”. He loves to share his knowledge, to help people, to solve problems, to make friends and to play Chess.

Certifications:

  • Oracle Database Cloud Administrator Certified Associate
  • Oracle Database Cloud Administrator Certified Professional
  • Oracle Database Cloud Service Operations Certified Associate
  • Oracle Certified Expert, Oracle Database 12c Maximum Availability Architecture
  • Oracle Certified Expert, Oracle Database 12c: RAC and Grid Infrastructure Administration
  • Oracle Database 12c Maximum Availability Architecture Certified Master
  • Oracle Database 12c: Data Guard Administrator
  • Oracle Database 12c Administrator Certified Master (OCM 12c)
  • Oracle Real Application Clusters 12c Certified Implementation Specialist
  • Oracle Database 11g Administrator Certified Master (OCM 11g)
  • Oracle Database 12c Administrator Certified Professional (OCP 12c)
  • Oracle RAC 11g and Grid Infraestructure Administrator
  • Oracle Certified Expert, Oracle Exadata X3 and X4 Administrator
  • Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert
  • Oracle Database 11g Administrator Certified Professional (OCP 11g)
  • Oracle Linux Certified Implementation Specialist
  • Oracle Database 11g Administrator Certified Associate