12 November, 2019

Basic Replication -- 11 : Indexes on a Materialized View

A Materialized View is actually also a physical Table (by the same name) that is created and maintained to store the rows that the MV query is supposed to present.

Since it is also a Table, you can build custom Indexes on it.

Here, my Source Table has an Index on OBJECT_ID :

SQL> create table source_table_1
  2  as select object_id, owner, object_name
  3  from dba_objects
  4  where object_id is not null
  5  /

Table created.

SQL> alter table source_table_1
  2  add constraint source_table_1_pk
  3  primary key (object_id)
  4  /

Table altered.

SQL> create materialized view log on source_table_1;

Materialized view log created.

SQL>


I then build Materialized View with  an additional Index on it :

SQL> create materialized view mv_1
  2  refresh fast on demand
  3  as select object_id as obj_id, owner as obj_owner, object_name as obj_name
  4  from source_table_1
  5  /

Materialized view created.

SQL> create index mv_1_ndx_on_owner
  2  on mv_1 (obj_owner)
  3  /

Index created.

SQL>


Let's see if this Index is usable.

SQL> exec  dbms_stats.gather_table_stats('','MV_1');

PL/SQL procedure successfully completed.

SQL> explain plan for
  2  select obj_owner, count(*)
  3  from mv_1
  4  where obj_owner like 'H%'
  5  group by obj_owner
  6  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2523122927

------------------------------------------------------------------------------------------
| Id  | Operation            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |     2 |    10 |    15   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|                   |     2 |    10 |    15   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | MV_1_NDX_ON_OWNER |  5943 | 29715 |    15   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("OBJ_OWNER" LIKE 'H%')
       filter("OBJ_OWNER" LIKE 'H%')



Note how this Materialized View has a column called "OBJ_OWNER"  (while the Source Table column is called "OWNER") and the Index ("MV_1_NDX_ON_OWNER") on this column is used.


You  would have also noted that you can run DBMS_STATS.GATHER_TABLE_STATS on a Materialized View and it's Indexes.

However, it is NOT a good idea to define your own Unique Indexes (including Primary Key) on a Materialized View.  During the course of a Refresh, the MV may not be consistent and the Unique constraint may be violated.   See Oracle Support Document # 67424.1



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.