28 October, 2019

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.


No comments: