Wednesday, October 2, 2013

Out-of-Place Materialized View Refresh Option in DB12c

There is new refresh option which has been introduced to improve materialized view refresh performance and availability in DB12c.
This is called out-of-place refresh. It uses outside tables during refresh instead of the existing in-place refresh that directly applies changes to the materialized view container table.

Out-of-place refresh is most useful in situations where there are large amounts of data changes and conventional DML statements do not scale well.

With this refresh option, the entire or affected portions of a materialized view are computed into one or more outside tables.

There are three types of out-of-place refresh:

  • Out-of-place Fast Refresh
  • Out-of-place PCT Refresh
  • Out-of-place Complete Refresh

Using DBMS_MVIEW package, with method = ? and out_of_place = true, out-of-place fast refresh are attempted first, then out-of-place PCT refresh, and finally out-of-place complete refresh.
Here is an example:

SQL> select banner from v$version where rownum=1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> create table my_objects as select * from all_objects;

Table created.

SQL> create materialized view my_objects_mv
  2  build immediate
  3  refresh on demand
  4  as
  5  select * from my_objects where owner<>'SCOTT';

Materialized view created.

SQL> begin
  2  dbms_mview.refresh('MY_OBJECTS_MV',
  3  method => '?',
  4  atomic_refresh => FALSE,
  5  out_of_place => TRUE);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select MVIEW_NAME, REFRESH_MODE, REFRESH_METHOD, FAST_REFRESHABLE, LAST_REFRESH_DATE
  2  FROM all_mviews
  3  WHERE MVIEW_NAME='MY_OBJECTS_MV';

MVIEW_NAME REFRESH_MODE  REFRESH_METHOD FAST_REFRESHABLE   LAST_REFRESH_DATE
----------------------------------------------------------- ------ -------- ------------------ --
MY_OBJECTS_MV DEMAND FORCE    NO                 02-OCT-13

Reference:

No comments:

Post a Comment