Oracle Database 12.2 Statement-level Refresh for Materialized Views

    Aug 28, 2017 12:58:00 PM 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 youngest Oracle ACE and Oracle ACE Director in the world and the first Guatemalan with these awards. Deiby is the youngest Latin American with the highest certifications “Oracle Certified Master 11g” and “Oracle Certified Master 12c”. He received In Vegas, United States the "SELECT Journal Editor’s Choice Award 2016", he became the first Guatemalan with that award. He is a frequent speaker in several Oracle Events around the world like “Technology Network Latin American Tour 2013, 2014, 2015 and 2016 in several countries like Guatemala, Costa Rica, Nicaragua, El Salvador, Uruguay, Argentina, Mexico, Brazil, Ecuador, Colombia, Peru; Collaborate in Vegas, USA; Latin American Oracle Open World in Brazil (2015 and 2016) and Oracle Open World in San Francisco, USA (2015 and 2016). He is the first Guatemalan who was accepted by Oracle Corporation as “Beta Tester” for the version “12cR2” in 2015. He is the official Technical Reviewer of the Book “Oracle Database 12c Release 2 Multitenant (1st Edition, McGraw-Hill)" and Co-Author of the book "Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability", both can be found in Amazon. He is the first Guatemala who appeared as an outstanding expert in the official magazine of Oracle Corporation called “Oracle Magazine” in the Edition November/December in 2014, this magazine is delivered around the world in several countries. He has published several articles in the Official Website of Oracle (www.oracle.com) in Portuguese, Spanish and English. Currently he is President of Guatemalan Oracle Users Group (GOUG). Director of Support Quality en Latin American Oracle Users Group Community (LAOUC) for 2016-2017, founder of a very well known group in Oracle Community called “Oraworld Team” that has members from India, Guatemala, Brazil, France and Switzerland having in total 4 Oracle ACE Directors, 3 Oracle ACE, 5 Oracle Certified Masters (OCM) and 1 PhD. Deiby was part of Oracle ACE Hackaton in Amsterdam, Netherlands in April 2016 where he was building several solutions using Oracle Cloud Products. Currently Deiby Gómez is CEO in Nuvola Consulting Group, a company that provides excellence on Support and Consulting services with Oracle Technology like Databases, Middleware, Cloud and Engineered systems. Deiby is well known in the community because of his resilience, entrepreneurship and his availability to help and share his knowledge. Deiby loves to travel, to play chess and to enjoy a good cup of coffee with friends. Oracle ACE (at the age of 23) Oracle Certified Master 11g (at the age of 24) Oracle ACE Director (at the age of 25) Oracle Certified Master 12c (at the age 26) SELECT Journal Editor’s Choice Award 2016 Speaker in several Universities, OTN Tour 2013,2014,2015. Collaborate15, LA Oracle Open World. Technical Reviewer of the Book "Oracle Database 12c Release 2 Multitenant (Oracle Press) 1st Edition" Blogger. Oracle Certifications: Oracle Linux Certified Implementation Specialist. Oracle Database 11g Administrator Certified Professional. Oracle Database 11g Administrator Certified Master (OCM 11g) Oracle Database 12c Administrator Certified Master (OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Master (MAA OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Expert (MAA OCE 12c) Oracle Database 12c Administrator Certified Professional. Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert. Oracle Exadata Database Machine Administrator. Oracle RAC 11g and Grid Infraestructure Administrator Oracle RAC 12c and Grid Infraestructure Administrator Oracle Real Application Clusters 12c Certified Implementation Specialist Oracle Database 12c: Data Guard Administrator