Basic Replication -- 10 : ON PREBUILT TABLE

In my previous blog post, I've shown a Materialized View that is built as an empty MV and subsequently populated by a Refresh call.

You can also define a Materialized View over an *existing*  (pre-populated) Table.

Let's say you have a Source Table and have built a Replica of it it another Schema or Database.  Building the Replica may have taken an hour or even a few hours.  You now know that the Source Table will have some changes every day and want the Replica to be updated as well.  Instead of executing, say, a TRUNCATE and INSERT, into the Replica every day, you define a Fast Refresh Materialized View over it and let Oracle identify all the changes (which, on a daily basis, could be a small percentage of the total size of the Source/Replica) and update the Replica using a Refresh call.


Here's a quick demo.

SQL> select count(*) from my_large_source;

  COUNT(*)
----------
     72447

SQL> grant select on my_large_source to hr;

Grant succeeded.

SQL> connect hr/HR@orclpdb1
Connected.
SQL> alter session enable parallel dml;

Session altered.

SQL> create table my_large_replica
  2  as select * from hemant.my_large_source
  3  where 1=2;

Table created.

SQL> insert /*+ PARALLEL (8) */
  2  into my_large_replica
  3  select * from hemant.my_large_source;

72447 rows created.

SQL>

So, now, HR has a Replica of the Source Table in the HEMANT schema.  Without any subsequent updates to the Source Table, I create the Materialized View definition, with the "ON PREBUILT TABLE" clause.

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> create materialized view log on my_large_source;

Materialized view log created.

SQL> grant select, delete on mlog$_my_large_source to hr;

Grant succeeded.

SQL> connect hr/HR@orclpdb1
Connected.
SQL>
SQL> create materialized view my_large_replica
  2  on prebuilt table
  3  refresh fast
  4  as select * from hemant.my_large_source;

Materialized view created.

SQL> select count(*) from hemant.my_large_source;

  COUNT(*)
----------
     72447

SQL> select count(*) from my_large_replica;

  COUNT(*)
----------
     72447

SQL>

I am now ready to add data and Refresh the MV.

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> desc my_large_source
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID_COL                                    NOT NULL NUMBER
 PRODUCT_NAME                                       VARCHAR2(128)
 FACTORY                                            VARCHAR2(128)

SQL> insert into my_large_source
  2  values (74000,'Revolutionary Pin','Outer Space');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from mlog$_my_large_source;

  COUNT(*)
----------
         1

SQL>
SQL> connect hr/HR@orclpdb1
Connected.
SQL> select count(*) from hemant.my_large_source;

  COUNT(*)
----------
     72448

SQL> select count(*) from my_large_replica;

  COUNT(*)
----------
     72447

SQL>
SQL> execute dbms_mview.refresh('MY_LARGE_REPLICA','F');

PL/SQL procedure successfully completed.

SQL> select count(*) from my_large_replica;

  COUNT(*)
----------
     72448

SQL>
SQL> select id_col, product_name
  2  from my_large_replica
  3  where factory = 'Outer Space'
  4  /

    ID_COL
----------
PRODUCT_NAME
--------------------------------------------------------------------------------
     74000
Revolutionary Pin


SQL>
SQL> select count(*) from hemant.mlog$_my_large_source;

  COUNT(*)
----------
         0

SQL>

Instead of rebuilding / repopulating the Replica Table with all 72,448 rows, I used the MV definition and the MV Log on the Source Table to copy over that 1 new row.

The above demonstration is against 19c.

Here are two older posts, one in March 2009 and the other in January 2012 on an earlier release of Oracle.