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