Index Materialized View in Oracle

Materialized views can deliver significant performance improvements for queries, but that does not mean that a materialized view^[1] can not be sped up further with the addition of one or more indexes.

One of the most common uses of materialized views is as part of a data warehouse. Data warehouses frequently use a lot of bitmapped indexes. For retrieving data from a materialized view, it makes sense to define a bitmapped index on each relevant column in a materialized view key.

Although query operations may be the way that materialized views are used, these views also have to get their data from the underlying tables through a refresh operation. If you want to optimize a refresh operation by using the fast option, it makes sense to include a single concatenated index with all the columns used in the unique key for the view. The ROWID column is not only a unique identifier for every row in the database, but it is also the fastest way to access any row in the database. If you have a materialized view that contains only joins, it is highly recommended that you create an index for the ROWID column for each table used in the materialized view to improve the performance of the fast refresh.

[ refresh clause ]

{ refresh
{ { fast | complete | force }
| on { demand | commit }
| { start with | next } date
| with { primary key | rowid }
| using
{ default [ master | local ] rollback segment
| [ master | local ] rollback segment rollback_segment
}
[ default [ master | local ] rollback segment
| [ master | local ] rollback segment rollback_segment
]...
}
[ { fast | complete | force }
| on { demand | commit }
| { start with | next } date
| with { primary key | rowid }
| using
{ default [ master | local ] rollback segment
| [ master | local ] rollback segment rollback_segment
}
[ default [ master | local ] rollback segment
| [ master | local ] rollback segment rollback_segment
]...
]...
| never refresh
}

The refresh option specifies the mechanism Oracle should use when refreshing the materialized view. The three options available are 1) fast, 2) complete, and 3) force. Fast refreshes are only available if Oracle can match rows in the materialized view directly to rows in the base table(s); they use tables called materialized view logs to send specific rows from the master table to the materialized view. Complete refreshes truncate the data and re-execute the materialized view's base query to repopulate it. The force option for refreshes tells Oracle to use a fast refresh if it is available; otherwise, a complete refresh will be used. If you have created a simple materialized view but want to use complete refreshes, specify refresh complete in your create materialized view command. Within this section of the create materialized view command, you also specify the mechanism used to relate values in the materialized view to the master table, whether RowIDs or primary key values should be used. By default, primary keys are used.
If the master query for the materialized view references a join or a single-table aggregate, you can use the on commit option to control the replication of changes. If you use on commit, changes will be sent from the master to the replica when the changes are committed on the master table. If you specify on demand, the refresh will occur when you manually execute a refresh command.

When creating a materialized view, you can reference tables in a remote database via a database link. The account that the database link uses in the remote database must have access to the tables and views used by the database link. You cannot create a materialized view based on objects owned by the user SYS. Within the local database, you can grant SELECT privilege on a materialized view to other local users. Since most materialized views are read-only (although they can be updatable), no additional grants are necessary. If you create an updatable materialized view, you must grant users UPDATE privilege on both the materialized view and the underlying local table it accesses.

A read-only materialized view cannot pass data changes from itself back to its master table. An updatable materialized view can send changes to its master table. Although that may seem to be a simple distinction, the underlying differences between these two types of materialized views are not simple. A read-only materialized view is implemented as a create table as select command. When transactions occur, they occur only within the master table; the transactions are optionally sent to the read-only materialized view. Thus, the method by which the rows in the materialized view change is controlled, the materialized view's rows only change following a change to the materialized view's master table.
In an updatable materialized view, there is less control over the method by which rows in the materialized view are changed. Rows may be changed based on changes in the master table, or rows may be changed directly by users of the materialized view. As a result, you need to send records from the master table to the materialized view, and vice versa. Since multiple sources of changes exist, multiple masters exist (referred to as a multimaster configuration).
During the transfer of records from the materialized view to master, you need to decide how you will reconcile conflicts. For example, what if the record with ID=1 is deleted at the materialized view site, while at the master site, a record is created in a separate table that references (via a foreign key) the ID=1 record? You cannot delete the ID=1 record from the master site, since that record has a “child” record that relates to it. You cannot insert the child record at the materialized view site, since the parent (ID=1) record has been deleted. How do you plan to resolve such conflicts? Read-only materialized views let you avoid the need for conflict resolution by forcing all transactions to occur in the controlled master table. This may limit your functionality, but it is an appropriate solution for the vast majority of replication needs.
In the next lesson, you will learn how to handle a materialized view that has been made invalid by changes to the underlying tables.

[1] Bitmapped Index: Uses individual bits in an index entry to identify the value of an entry. A bitmapped index works best when there are a limited number of values for an index.

[1]