Pro Oracle Database 11g Administration (65 page)

385

CHAPTER 15 ■ MATERIALIZED VIEWS

1. Alter the base table.

2. Drop and re-create the MV to reflect the changes in the base table.

Here’s an example. Say you need to add a column INV_LOC to the base INV table: SQL> alter table inv add(inv_loc varchar2(30));

Drop and re-create the MV to include the column definition:

drop materialized view inv_mv;

--

create materialized view inv_mv

refresh fast on demand

as

select

inv_id

,inv_desc

,inv_loc

from inv;

This approach may take a long time if large amounts of data are involved. You have downtime for any application that accesses the MV while it’s being rebuilt. If you work in a large data-warehouse environment, then due to the amount of time it takes to completely refresh the MV, you may want to consider not dropping the underlying table. This option is discussed in the next section.

Altering a Materialized View but Preserving the Underlying Table

When you drop an MV, you have the option of preserving the underlying table and its data. You may find this approach advantageous when you’re working with large MVs in data-warehouse environments.

Here are the steps:

1.

Alter the base table.

2.

Drop the MV, but preserve the underlying table.

3.

Modify the underlying table.

4.

Re-create the MV using the ON PREBUILT TABLE clause.

Here’s a simple example to illustrate this procedure:

SQL> alter table inv add(inv_loc varchar2(30));

Drop the MV, but specify that you want to preserve the underlying table: SQL> drop materialized view inv_mv preserve table;

Now, modify the underlying table:

SQL> alter table inv_mv add(inv_loc varchar2(30));

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

create materialized view inv_mv

on prebuilt table

using index tablespace mv_index

as

select

386

CHAPTER 15 ■ MATERIALIZED VIEWS

inv_id

,inv_desc

,inv_loc

from inv;

This allows you to redefine the MV without dropping and completely refreshing the data. Be aware that if there is any Data Manipulation Language (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 schedule the MV alteration during a maintenance window when no transactions are occurring in the base table.

Altering a Materialized View 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 that 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. Re-create the MV on the prebuilt table.

Here’s a simple example to illustrate this process. For clarity, the original definition of the table and MV are shown. Here’s the base-table definition:

create table inv(

inv_id number primary key

,inv_desc varchar2(30));

To create an MV on a prebuilt table, you must first create a table:

create table inv_mv (

inv_id number

,inv_desc varchar2(30));

Here’s the definition of a simple MV that is created using a prebuilt table and uses INV as a base table:

create materialized view inv_mv

on prebuilt table

using index tablespace mv_index

as

select

inv_id

,inv_desc

from inv;

Suppose you alter the base table as follows:

SQL> alter table inv add (inv_loc varchar2(30));

387

CHAPTER 15 ■ MATERIALIZED VIEWS

Drop the MV:

SQL> drop materialized view inv_mv;

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

SQL> alter table inv_mv add(inv_loc varchar2(30));

Now you can rebuild the MV using the prebuilt table with the new INV_LOC column added: create materialized view inv_mv

on prebuilt table

using index tablespace mv_index

as

select

inv_id

,inv_desc

,inv_loc

from inv;

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 re-create 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 a Materialized View

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 normal 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 no logging, create the MV with the NOLOGGING

option:

create materialized view inv_mv

nologging

tablespace mv_data

using index tablespace mv_index

as

select

inv_id

,inv_desc

from inv;

You can also alter an existing MV into no-logging mode:

SQL> alter materialized view inv_mv nologging;

If you want to re-enable logging, then do as follows:

SQL> alter materialized view inv_mv logging;

To verify that the MV has been switched to NOLOGGING, query the USER_TABLES view: 388

CHAPTER 15 ■ MATERIALIZED VIEWS

select

a.table_name

,a.logging

from user_tables a

,user_mviews b

where a.table_name = b.mview_name;

The advantage of enabling no logging is that refreshes take place more quickly, because the database doesn’t have the overhead of logging redo information. 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 # 32, block # 131)

ORA-01110: data file 32: '/ora01/dbfile/O11R2/mvdata01.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option

If you get the previous error, then you’ll most likely have to completely refresh 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 completely refresh the MV.

Altering Parallelism

Sometimes an MV is created with a high degree of parallelism to improve the performance of the creation process. After you create the MV, you may not need the same degree of parallelism associated with the underlying table. You can alter an MV’s parallelism as follows: SQL> alter materialized view inv_mv parallel 1;

You can check on the degree of parallelism by querying USER_TABLES:

SQL> select table_name, degree from user_tables where table_name= upper('&mv_name'); TABLE_NAME DEGREE

------------------------------ ------

INV_MV 1

Moving a Materialized View

As the operating environment’s conditions change, you may need to move an MV from one tablespace to another. In these scenarios, use the ALTER MATERIALIZED VIEW...MOVE TABLESPACE statement. This example moves the table associated with an MV to a different tablespace: SQL> alter materialized view inv_mv move tablespace tbsp2;

If any indexes are associated with the MV table, the move operation renders them unusable. You can check the status of the indexes as follows:

select

a.table_name

,a.index_name

,a.status

from user_indexes a

,user_mviews b

where a.table_name = b.mview_name;

389

CHAPTER 15 ■ MATERIALIZED VIEWS

You must rebuild any associated indexes after moving the table. For example: SQL> alter index inv_mv_pk1 rebuild;

Managing Materialized View Logs

MV logs are required for fast-refreshable MVs. The MV log is a table that stores DML information for a master (base) table. It’s created in the same database as the master table with the same user that owns the master table. You need the CREATE TABLE privilege to create an MV log.

The MV log is populated by an Oracle internal trigger (that you have no control over). This internal trigger inserts a row into the MV log after an INSERT, UPDATE, or DELETE on the master table. You can view the internal triggers in use by querying DBA/ALL/USER_INTERNAL_TRIGGERS.

An MV log is associated with only one table, and each master table can have only one MV log defined for it. You can create an MV log on a table or on another MV. Multiple fast-refreshable MVs can use one MV log.

After an MV performs a fast refresh, any records in the MV log that are no longer needed are deleted.

In the event that multiple MVs are using one MV log, then records are purged from the MV log only after they aren’t required by any of the fast-refreshable MVs.

Table 15–3 describes terms used with MV logs. These terms are referred to in the following sections in this chapter that relate to MV logs.

Table 15–3.
Materialized View Log Terminology and Features
MV Log Term

Description

Materialized view log

Database object that tracks DML changes to MV base table. An MV

log is required for fast refreshes.

Primary key MV log

MV log that uses the base-table primary key to track DML changes.

ROWID MV log

MV log that uses the base table ROWID to track DML changes.

Commit SCN MV log

MV log based on the commit system change number (SCN) instead

of a timestamp. Available in Oracle Database 11
g
R2 or higher.

Object ID

Object identifier used to track DML changes.

Filter column

Non-primary-key column referenced by an MV subquery. Required

by some fast-refresh scenarios.

Join column

Non-primary-key column that defines a join in the subquery WHERE

clause. Required by some fast-refresh scenarios.

Sequence

Sequence value required for some fast-refresh scenarios.

New values

Specifies that old and new values be recorded in the MV log.

Required for single-table aggregate views to be eligible for fast

refresh.

390

CHAPTER 15 ■ MATERIALIZED VIEWS

Creating a Materialized View Log

Fast-refreshable views require an MV log to be created on the master (base) table. Use the CREATE

MATERIALIZED VIEW LOG command to create an MV log. This example creates an MV log on the USERS

table, specifying that the primary key should be used to identify rows in the MV log: SQL> create materialized view log on users with primary key;

You can also specify storage information such as the tablespace name: create materialized view log

on users

pctfree 5

tablespace mv_data

with primary key;

When you create an MV log on a table, Oracle creates a table to store the changes to a base table since the last refresh. The name of the MV log table follows this format: MLOG$_. You can use the SQL*Plus DESCRIBE statement to view the columns of the MV log: SQL> desc mlog$_users;

Name Null? Type

----------------------------------- -------- ------------------------

USER_ID NUMBER

SNAPTIME$$ DATE

DMLTYPE$$ VARCHAR2(1)

OLD_NEW$$ VARCHAR2(1)

CHANGE_VECTOR$$ RAW(255)

XID$$ NUMBER

You can query this underlying MLOG$ table to determine the number of transactions since the last refresh. After each refresh, the MV log table is purged. If multiple MVs use the MV log, the log table isn’t purged until all dependent MVs are refreshed.

If you create the MV log on a table with a primary key, then a RUPD$_ table is also created. This table is used for updatable MVs. If you’re not using the updatable MV feature, then this table is never used, and you can ignore it.

When you create an MV log, you can specify that it use PRIMARY KEY, ROWID, or OBJECT ID to uniquely identify rows in the MV log table. If the master table has a primary key, then use WITH PRIMARY KEY when you create the MV log. If the master table doesn’t have a primary key, then you have to use WITH ROWID to specify that a ROWID value is used to uniquely identify MV log records. You can use WITH OBJECT ID when you create an MV log on an object table.

For database versions prior to Oracle Database 11
g
release 2, the MV log table will contains a SNAPTIME$$ time column. This column is used to determine which records need to be applied to any dependent MVs.

As of Oracle Database 11
g
release 2, you have the option of creating a COMMIT SCN–based MV log.

This type of MV log uses the SCN of a transaction to determine which records need to be applied to any dependent MVs. COMMIT SCN–based MV logs are more efficient than timestamp-based MV logs. If you’re using Oracle Database 11
g
release 2 or higher, then you should consider using COMMIT SCN–based MV