Databases Reference
In-Depth Information
This process has the advantage of allowing you to modify an MV definition without dropping the underlying
table. You have to drop the MV, alter the underlying table, and then recreate the MV with the new definition. If the
underlying table contains a large amount of data, this method can prevent unwanted downtime.
As mentioned in the previous section, you need to 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.
Toggling Redo Logging on an MV
Recall that an MV has an underlying database table. When you refresh an MV, this initiates transactions in the
underlying table that result in the generation of redo (just as with a regular database table). In the event of a database
failure, you can restore and recover all the transactions associated with an MV.
By default, redo logging is enabled when you create an MV. You have the option of specifying that redo not be
logged when an MV is refreshed. To enable nologging, create the MV with the
NOLOGGING
option:
create materialized view sales_mv
nologging
refresh with primary key
fast on demand as
select sales_id ,sales_amt, sales_dtt
from sales;
You can also alter an existing MV into nologging mode:
SQL> alter materialized view sales_mv nologging;
If you want to reenable logging, then do as follows:
SQL> alter materialized view sales_mv logging;
To verify that the MV has been switched to
NOLOGGING
, query the
USER_TABLES
view:
select a.table_name, a.logging
from user_tables a
,user_mviews b
where a.table_name = b.mview_name;
The advantage of enabling nologging is that refreshes take place more quickly. The refresh mechanism uses a
direct path insert, which, when combined with
NOLOGGING
, eliminates most of the redo generation. The big downside
is that if a media failure occurs soon after an MV has been refreshed, you can't recover the data in the MV. In this
scenario the first time you attempt to access the MV, you receive an error such as
ORA-01578: ORACLE data block corrupted (file # 5, block # 899)
ORA-01110: data file 5: '/u01/dbfile/o12c/users02.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
If you get the previous error, then you'll most likely have to rebuild the MV to make the data accessible again. In
many environments this may be acceptable. You save on database resources by not generating redo for the MV, but
the downside is a longer restore process (in the event of a failure) that requires you to rebuild the MV.
