Materialized Views - Pro Oracle Database 12c Administration - page 424

Now, modify the underlying table:

SQL> alter table sales_mv add(sales_loc varchar2(30));

Next, create the MV, using the ON PREBUILT TABLE clause:

create materialized view sales_mv
on prebuilt table
refresh with primary key
complete on demand as
select sales_id, sales_amt, sales_dtt, sales_loc
from sales;

This allows you to redefine the MV without dropping and completely refreshing the data. Be aware that if there
is any DML activity against the base table during the MV rebuild operation, those transactions aren't reflected in the
MV when you attempt to refresh it. In data warehouse environments, you typically have a known schedule for loading
base tables and therefore should be able to perform the MV alteration during a maintenance window in which no
transactions are occurring in the base table.

Altering an MV Created on a Prebuilt Table
If you originally created an MV using the ON PREBUILT TABLE clause, then you can perform a procedure similar to the
one shown in the previous section when preserving the underlying table. Here are the steps for modifying an MV that
was created using the ON PREBUILT TABLE clause:
1. Alter the base table.

2. Drop the MV. For MVs built on prebuilt tables, this doesn't drop the underlying table.

3. Alter the prebuilt table.

4. Recreate the MV on the prebuilt table.

Here is a simple example to illustrate this process. First, the base table is altered:

SQL> alter table sales add(sales_loc varchar2(30));

Then, drop the MV:

SQL> drop materialized view sales_mv;

For MVs created on prebuilt tables, this doesn't drop the underlying table—only the MV object. Next, add
a column to the prebuilt table:

SQL> alter table sales_mv add(sales_loc varchar2(30));

Now, you can rebuild the MV, using the prebuilt table with the new column added:

create materialized view sales_mv
on prebuilt table
refresh with primary key
complete on demand as
select sales_id, sales_amt, sales_dtt, sales_loc
from sales;