Oracle Database 12.2 Statement-level Refresh for Materialized Views

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