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 :
I then build Materialized View with an additional Index on it :
Let's see if this Index is usable.
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
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