LATEST TOPICS

Oracle Database 12c: The Out of Place Materialized View refresh

Introduction:

Prior to Oracle database 12c, Materialized View refresh was always an in place refresh, which means the changes were made in the existing materialized view table either by delete/insert, truncate/insert or append/insert method.

However, beginning with Oracle database 12c Release 1; we have the option of doing a materialized view refresh out of place. This means, we can refresh a materialized view without disturbing the existing Mview table. In this method, the refresh is performed by creating an interim table and then applying all the changes to the interim table and finally the interim table is switched with the existing materialized view table.

This mechanism of out of place refresh provides higher availability (as the base Mview table remains intact) as well as improves performance of Mview refresh.

Type of Out-Of-Place refresh

The out of place refresh works for all the existing Mview refresh methods such as COMPLETE (C), FAST (F), PCT (P) and FORCE (?). Based on the existing refresh methods, we have the following three type of out of place refresh types.

  • Out Of Place Complete Refresh
  • Out Of Place Fast Refresh
  • Out Of Place PCT Refresh

The out of place refresh is initiated using the refresh interface DBMS_MVIEW as shown below:

DBMS_MVIEW.REFRESH('MVIEW_NAME', method => 'REFRESH_METHOD', atomic_refresh => FALSE, out_of_place => TRUE);

----- where
----- MVIEW_NAME: Name of the Materialized View to be refreshed
----- REFRESH_METHOD: Complete ('C') or Fast ('F') or PCT ('P') or Force ('?')
----- atomic_refresh: This parameter must be always FALSE for out-of-place refresh to work. Out-Of-Place refresh doesn't comply with atomic refresh.
----- out-of-place: This parameter must be always TRUE to be able to run the Mview refresh in out-of-place method.

If the refresh method is specified as FORCE (‘?’) during the out of place refresh, then first attempt is made for an out of place fast refresh, then out of place PCT refresh and finally out of place complete refresh.

Demonstration

Here, I am creating a Materialized View and then attempting an out of place refresh.

SQL> show user
USER is "MYAPP_12C"
SQL> select version from v$instance;

VERSION
-----------------
12.1.0.2.0

SQL> create table my_source_t as select * from all_objects;

Table created.

SQL> select count(*) from my_source_t;

  COUNT(*)
----------
     11883

SQL> create materialized view my_op_mview
  2  build immediate
  3  refresh on demand
  4  as
  5  select * from myapp_12c.my_source_t
  6  ;

Materialized view created.

So, we have a source table, materialized view and the respective table for the created materialized view.

SQL> select owner,object_name,object_type,status from dba_objects where owner='MYAPP_12C'

OWNER         OBJECT_NAME                    OBJECT_TYPE             STATUS
------------- ------------------------------ ----------------------- -------
MYAPP_12C     MY_OP_MVIEW                    TABLE                   VALID
MYAPP_12C     MY_OP_MVIEW                    MATERIALIZED VIEW       INVALID
MYAPP_12C     MY_SOURCE_T                    TABLE                   VALID

Now, I am attempting an out of place refresh for the materialized view. with the FORCE (‘?’) method.

SQL> exec DBMS_MVIEW.REFRESH('my_op_mview', method => '?', atomic_refresh => FALSE, out_of_place => TRUE);

PL/SQL procedure successfully completed.

As per the oracle documentation, the out of place refresh uses an interim table during the refresh process. Keeping that in mind, I had queried the DBA_OBJECTS (while refresh was in progress) against the MVIEW schema to see if I can find anything there.

SQL> select owner,object_name,object_type,status from dba_objects where owner='MYAPP_12C'

OWNER         OBJECT_NAME                    OBJECT_TYPE             STATUS
------------- ------------------------------ ----------------------- -------
MYAPP_12C     RV$4E25                        TABLE                   VALID
MYAPP_12C     MY_OP_MVIEW                    TABLE                   VALID
MYAPP_12C     MY_OP_MVIEW                    MATERIALIZED VIEW       INVALID
MYAPP_12C     MY_SOURCE_T                    TABLE                   VALID

As we can observe, there is a new table RV$4E25 created under the MVIEW schema. This is the interim table where the refresh is actually performed. Once the refresh is completed, this interim table is switched with original materialized view table and the original table is dropped from the database. The interim table is created in the format RV$nnnn, where nnnn is the hexadecimal equivalent original Mview table’s Object ID.

To confirm this, I am going to again run the out of place refresh. However, this time I would make a note of the Hexa code for the Object ID.

Let us first, make a note of the Hexa code for the Mview table Object ID.

SQL> select object_id, TRIM(TO_CHAR(OBJECT_ID,'XXXXX')) Object_hex ,owner,object_name,object_type
  2  from dba_objects
  3  where owner='MYAPP_12C';

 OBJECT_ID OBJECT OWNER      OBJECT_NAME               OBJECT_TYPE
---------- ------ ---------- ------------------------- -----------------------
     20007 4E27   MYAPP_12C  MY_OP_MVIEW               TABLE
     20006 4E26   MYAPP_12C  MY_OP_MVIEW               MATERIALIZED VIEW
     20004 4E24   MYAPP_12C  MY_SOURCE_T               TABLE

So, the hexadecimal code of Mview table MY_OP_MVIEW is 4E27. I am expecting the out of place refresh to create a external table with name RV$4E27 while doing the refresh.

Lets, execute the out of place refresh.

SQL> exec DBMS_MVIEW.REFRESH('my_op_mview', method => '?', atomic_refresh => FALSE, out_of_place => TRUE);

PL/SQL procedure successfully completed.

While the refresh was running, I have queried the objects and found that the interim table RV$4E27 is indeed created.

SQL> select owner,object_name,object_type,status from dba_objects where owner='MYAPP_12C'

OWNER         OBJECT_NAME                    OBJECT_TYPE             STATUS
------------- ------------------------------ ----------------------- -------
MYAPP_12C     RV$4E27                        TABLE                   VALID
MYAPP_12C     MY_OP_MVIEW                    TABLE                   VALID
MYAPP_12C     MY_OP_MVIEW                    MATERIALIZED VIEW       INVALID
MYAPP_12C     MY_SOURCE_T                    TABLE                   VALID

Once, the out of place refresh is completed; I had again queried the objects along with the hexadecimal code.

SQL> select object_id, TRIM(TO_CHAR(OBJECT_ID,'XXXXX')) Object_hex ,owner,object_name,object_type
  2  from dba_objects
  3  where owner='MYAPP_12C';

 OBJECT_ID OBJECT OWNER        OBJECT_NAME               OBJECT_TYPE
---------- ------ ------------ ------------------------- -----------------------
     20022 4E36   MYAPP_12C    MY_OP_MVIEW               TABLE
     20006 4E26   MYAPP_12C    MY_OP_MVIEW               MATERIALIZED VIEW
     20004 4E24   MYAPP_12C    MY_SOURCE_T               TABLE

As, we can observe the Object ID as well as the Hexadecimal code is now changed for the Mview table ‘MY_OP_MVIEW’. The old table (20007:4E27) is now replaced with the interim table (20022:4E36).

Restrictions using Out-of-Place Refresh

As per Oracle documentation, out of place Materialized view refresh has all the restrictions that apply to the existing Mview refresh methods. For a complete list of restrictions, please refer here

Out of all of these restrictions, the one that has the most significant disadvantage is the remote materialized view not being supported by the out of place refresh.

In most cases, Materialized Views are created on remote database tables. However, there is no provision to refresh those Mviews using out of place refresh method.

Trying to refresh a Mview defined on remote database tables using out of place refresh method would result into errors as demonstrated below.

SQL> show user
USER is "MYAPP_12C"
SQL> select version from v$instance;

VERSION
-----------------
12.1.0.2.0

SQL> select count(*) from myapp.source_t@myapp_link;

  COUNT(*)
----------
     12685


SQL> create materialized view my_op_mview
  2  build immediate
  3  refresh on demand
  4  as
  5  select * from myapp.source_t@myapp_link
  6  ;

Materialized view created.


SQL> select count(*) from my_op_mview;

  COUNT(*)
----------
     12685

Here, I have created a Materialized view ‘my_op_mview’ on a remote database table ‘myapp.source_t@myapp_link’. Now, lets try to refresh this Mview using out of place refresh method.

SQL> exec DBMS_MVIEW.REFRESH('my_op_mview', method => '?', atomic_refresh => FALSE, out_of_place => TRUE);
BEGIN DBMS_MVIEW.REFRESH('my_op_mview', method => '?', atomic_refresh => FALSE, out_of_place => TRUE); END;

*
ERROR at line 1:
ORA-32354: cannot refresh materialized view MYAPP_12C.MY_OP_MVIEW using
out-of-place complete refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 1

As we can observe, the Mview out of place refresh errors out with ORA-32354; the description of which is mentioned below.

oerr ora 32354
32354, 00000, "cannot refresh materialized view %s.%s using out-of-place complete refresh"
// *Cause:  The materialized view did not qualify for out-of-place complete
//          refresh.
// *Action: Set the 'out_of_place' parameter to 'false'.

Therefore, we can not refresh Mview with remote database tables using out of place refresh method.

Conclusion

As an initial development, Out of place Materialized view refresh looks promising. However, it could be a great improvement over the existing in place method, if some of the significant restrictions can be eliminated, in particular; the restriction imposed on remote materialized view refresh.

2 Comments
  1. Nassyam Basha
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed