When you have a good understanding of the architecture of a fast refresh, you will not have difficulty learning advanced MV concepts. If this is the first time looking at MVs, it is important to realize that an MV’s data is stored in a regular database table. This will help you understand architecturally what is and is not possible. For the most part, because the MV and MV log are based on tables, most features available with a regular database table can also be applied to the MV table and MV log table. For instance, the following Oracle features are readily applied to MVs:

•     Storage and tablespace placement

•     Indexing

•     Partitioning

•     Compression

•     Encryption

•     Logging

•     Parallelism

Numerous MV features are available. Many are related to attributes that you can apply to any table, such as storage, indexing, compression, and encryption. Other features are related to the type of MV created and how it is refreshed.

Creating an Unpopulated MV

When you create an MV, you have the option of instructing Oracle whether to initially populate the MV with data. For example, if it takes several hours to initially build an MV, you may want to first define the MV and then populate it as a separate job.

This example uses the BUILD DEFERRED clause to instruct Oracle not to initially populate the MV with the results of the query:

SQL> create materialized view sales_mv tablespace users build deferred refresh complete on demand as select sales_id, sales_amt from sales;

At this point, querying the MV results in zero rows returned. At some later point, you can initiate a complete refresh to populate the MV with data.

Creating an MV Refreshed on Commit

You may be required, when data are modified in the master table, to have them immediately copied to an MV. In this scenario, use the ON COMMIT clause when you create the MV. The master table must have an MV log created on it for this technique to work:

SQL> create materialized view log on sales with primary key;

Next, an MV is created that refreshes on commit:

SQL> create materialized view sales_mv refresh on commit as select sales_id, sales_amt from sales;

As data are inserted and committed in the master table, any changes are also available in the MV that would be selected by the MV query.

The ON COMMIT refreshable MV has a few restrictions you need to be aware of:

•     The master table and MV must be in the same database.

•     You cannot execute a distributed transaction on the base table.

•     This approach is not supported with MVs that contain object types or Oracle-supplied types.

Also consider the overhead associated with committing data simultaneously in two places; this can affect the performance of a high-transaction OLTP system. Additionally, if there is any problem with updating the MV, then the base table cannot commit a transaction. For example, if the tablespace in which the MV is created becomes full (and cannot allocate another extent), you see an error such as this when trying to insert into the base table:

ORA-12008: error in materialized view refresh path

ORA-01653: unable to extend table MV_MAINT.SALES_MV by 16 in tablespace…

For these reasons, you should use this feature only when you are sure it would not affect performance or availability.

Note you cannot specify that an MV be refreshed with both ON COMMIT and

ON DEMAND. in addition, ON COMMIT is not compatible with the START WITH and NEXT clauses of the CREATE MATERIALIZED VIEW statement.