Pro Oracle Database 11g Administration (68 page)

CHAPTER 15 ■ MATERIALIZED VIEWS

Here’s some sample output that shows two MVs connected to one MV log: MOWNER BASE_TABLE SNAPID SNAPTIME

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

INV_MGMT CMP_GRP_ASSOC 651 05-AUG-10

INV_MGMT CMP_GRP_ASSOC 541 02-JAN-08

The next query displays information regarding all MVs that have been created that tie into an MV

log. Run this query on the master site:

select

a.log_table

,a.log_owner

,b.master mast_tab

,c.owner mv_owner

,c.name mview_name

,c.mview_site

,c.mview_id

from dba_mview_logs a

,dba_base_table_mviews b

,dba_registered_mviews c

where b.mview_id = c.mview_id

and b.owner = a.log_owner

and b.master = a.master

order by a.log_table;

Here’s some sample output:

LOG_TABLE LOG_OWNE MAST_TAB MV_OWN MVIEW_NAME MVIEW_S MVIEW_ID

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

MLOG$_CMP_GRP_ASSOC INV_MGMT CMP_GRP_ASSOC REP_MV CMP_GRP_ASSOC_MV DWREP 651

MLOG$_CMP_GRP_ASSOC INV_MGMT CMP_GRP_ASSOC TSTDEV CMP_GRP_ASSOC_MV ENGDEV 541

When you drop a remote MV, it should de-register from the master database. However, this doesn’t always happen. A remote database may get wiped out (maybe a short-term development database), and the MV doesn’t get a chance to un-register itself (via the DROP MATERIALIZED VIEW statement). In this situation, the MV log is unaware that a dependent MV is no longer available, and therefore it keeps records indefinitely.

To purge unwanted MV information from the database that contains the MV log, execute the PURGE_MVIEW_FROM_LOG procedure of DBMS_MVIEW. This example passes in the ID of the MV to be purged: SQL> exec dbms_mview.purge_mview_from_log(541);

This statement should update the data dictionary and remove information from the internal table SLOG$ and DBA_REGISTERED_MVIEWS. If the MV being purged is the oldest MV associated with the MV log table, the associate old records are also deleted from the MV log.

If a remote MV is no longer available but still registered with the MV log table, you can manually unregister it at the master site. Use the UNREGISTER_MVIEW procedure of the DBMS_MVIEW package to unregister a remote MV. To do this, you need to know the remote MV owner, MV name, and MV site (available from the output of the previous query in this section):

SQL> exec dbms_mview.unregister_mview('TSTDEV','CMP_GRP_ASSOC_MV','ENGDEV'); If successful, the previous operation removes a record from DBA_REGISTERED_MVIEWS.

407

CHAPTER 15 ■ MATERIALIZED VIEWS

Managing Materialized Views in Groups

An MV group is a useful feature that enables you to refresh a set of MVs at a consistent transactional point in time. If you refresh MVs based on master tables that have parent/child relationships, then you should most likely use a refresh group. This method guarantees that you won’t have any orphaned child records in your set of refreshed MVs. The following sections describe how to create and maintain MV

refresh groups.

■
Note
You use the DBMS_REFRESH package to accomplish most of the tasks in this section. This package is fully documented in the Oracle Advanced Replication Management API Reference guide (available on OTN).

Creating a Materialized View Group

You use the MAKE procedure of the DBMS_REFRESH package to create an MV group. When you create an MV

group, you must specify a name, a comma-separated list of MVs in the group, the next date to refresh, and the interval used to calculate the next refresh time. Here’s an example of a group that consists of two MVs:

begin

dbms_refresh.make(

name => 'INV_GROUP'

,list => 'INV_MV, REGION_MV'

,next_date => sysdate-100

,interval => 'sysdate+1'

);

end;

/

When you create an MV group, Oracle automatically creates a database job to manage the refresh of the group. You can view the details of an MV group by querying from DBA/ALL/USER_REFRESH: select

rname

,job

,next_date

,interval

from user_refresh;

Here’s some sample output:

RNAME JOB NEXT_DATE INTERVAL

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

INV_GROUP 34 26-APR-10 sysdate+1

I hardly ever use the internal database job as a refresh mechanism. Notice that the NEXT_DATE value specified in the previous SQL is sysdate-100. That means the only way the database job will kick off this job is if the date somehow gets set to 100 days in the past. This way, the job scheduler never initiates the refresh.

In most environments, the refresh needs to start at a specific time. In these scenarios, you use a cron job or some similar utility that has job-scheduling capabilities.

408

CHAPTER 15 ■ MATERIALIZED VIEWS

Altering a Materialized View Refresh Group

You can alter characteristics of a refresh group such as the refresh date and/or interval. If you rely on a database job for your refresh mechanism, then you may need to occasionally tweak your refresh characteristics. Use the CHANGE function of the DBMS_REFRESH package to achieve this. This example changes the INTERVAL calculation:

SQL> exec dbms_refresh.change(name=>'CCIM_GROUP',interval=>'SYSDATE+1'); Again, you need to change refresh intervals only if you’re using the internal database job to initiate the materialized group refresh. You can verify the details of a refresh group’s interval and job information with this query:

select

a.job

,a.broken

,b.rowner

,b.rname

,b.interval

from dba_jobs a

,dba_refresh b

where a.job = b.job

order by a.job;

Here’s the output for this example:

JOB B ROWNER RNAME INTERVAL

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

104 N REP_MV CCIM_GROUP SYSDATE+1

Refreshing a Materialized View Group

After you’ve created a group, you can manually refresh it using the REFRESH function of the DBMS_REFRESH

package. This example refreshes the group that you previously created: SQL> exec dbms_refresh.refresh('INV_GROUP');

If you inspect the LAST_REFRESH_DATE column of USER_MVIEWS, you’ll notice that all MVs in the group have the same refresh time. This the expected behavior because the MVs in the group are all refreshed at a consistent transaction point in time.

DBMS_MVIEW vs. DBMS_REFRESH

You may have noticed that you can use the DBMS_MVIEW package to refresh a group of MVs. For example, you can refresh a set of MVs in a list as follows using DBMS_MVIEW:

SQL> exec dbms_mview.refresh(list=>'INV_MV,REGION_MV');

This refreshes each MV in the list as a single transaction. It’s the equivalent of using an MV group.

However, when you use DBMS_MVIEW, you have the option of setting the ATOMIC_REFRESH parameter to TRUE

(default) or FALSE. For example, here the ATOMIC_REFRESH parameter is set to FALSE: SQL> exec dbms_mview.refresh(list=>'INV_MV,REGION_MV',atomic_refresh=>false); 409

CHAPTER 15 ■ MATERIALIZED VIEWS

Setting ATOMIC_REFRESH to FALSE instructs DBMS_MVIEW to refresh each MV in the list as a separate transaction. It also instructs complete refreshes of MV to consider using the TRUNCATE statement. The previous line of code is equivalent to the following two lines:

SQL> exec dbms_mview.refresh(list=>'INV_MV', atomic_refresh=>false); SQL> exec dbms_mview.refresh(list=>'REGION_MV', atomic_refresh=>false); Compare that to the behavior of DBMS_REFRESH, which is the package you should use to set up and maintain an MV group. The DBMS_REFRESH package always refreshes a group of MVs as a consistent transaction.

If you always need a set of MVs to be refreshed as a transactionally consistent group, use DBMS_REFRESH. If you need some flexibility as so whether a list of MVs is refreshed as a consistent transaction (or not), use DBMS_MVIEW.

Determining Materialized Views in a Group

When you’re investigating issues with an MV refresh group, a good starting point is to display which MVs the group contains. Query the data-dictionary views DBA_RGROUP and DBA_RCHILD to view the MVs in a refresh group:

select

a.owner

,a.name mv_group

,b.name mv_name

from dba_rgroup a

,dba_rchild b

where a.refgroup = b.refgroup

and a.owner = b.owner

order by a.owner, a.name, b.name;

Here’s a snippet of the output:

OWNER MV_GROUP MV_NAME

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

DARL INV_GROUP INV_MV

DARL INV_GROUP REGION_MV

In the DBA_RGROUP view, the NAME column represents the name of the refresh group. The DBA_RCHILD

view contains the name of each MV in the refresh group.

Adding an MV to a Refresh Group

As your business requirements change, you occasionally need to add an MV to a group. Use the ADD

procedure of the DBMS_REFRESH package to accomplish this task:

SQL> exec dbms_refresh.add(name=>'INV_GROUP',list=>'PRODUCTS_MV,USERS_MV'); You must specify a name and provide a comma-separated list of the MV names to add. The newly added MVs are refreshed the next time the group is refreshed.

The other way to add an MV to a group is to drop the group and re-create it with the new MV.

However, it’s usually preferable to add an MV.

410

CHAPTER 15 ■ MATERIALIZED VIEWS

Removing Materialized Views from a Refresh Group

Sometimes you need to remove an MV from a group. To do so, use the SUBTRACT function of the DBMS_REFRESH package. This example removes one MV from a group:

SQL> exec dbms_refresh.subtract(name=>'INV_GROUP',list=>'REGION_MV'); You have to specify the name of the MV group and provide a comma-separated list containing the names of the MVs you want to remove.

The other way to remove an MV from a group is to drop the group and re-create it without unwanted MV(s). However, it’s usually preferable to remove an MV.

Dropping a Materialized View Refresh Group

If you need to drop an MV refresh group, use the DESTROY procedure of the DBMS_REFRESH package. This example drops the MV group named INV_GROUP:

SQL> exec dbms_refresh.destroy('INV_GROUP');

This only drops the MV refresh-group object—it doesn’t drop any of the actual MVs. If you need to also drop the MVs, use the DROP MATERIALIZED VIEW statement.

Summary

Sometimes the term
materialized view
confuses people who are new to the technology. Perhaps Oracle should have named this feature “periodically purge and repopulate a table that contains the results of a query,” but that’s probably too long a phrase. Regardless, when you understand the power of this tool, you can use it to replicate and aggregate large amounts of data. You can greatly improve the performance of queries by periodically computing and storing the results of complex aggregations of data.

MVs can be
fast-refreshable
, which means they only copy over changes from the master table that have occurred since the last refresh. To use this type of MV, you must create an MV log on the master table. It’s not always possible to create an MV log; in these scenarios, the MV must be completely refreshed.

If need be, you can also compress and encrypt the data with an MV. This allows for better space management and security. Additionally, you can partition the underlying table used by an MV, to allow for greater scalability, performance, and availability.

MVs provide an efficient method for replicating data. In some cases, you need to replicate entire databases or just certain database objects and portions of data. The next couple of chapters focus on Data Pump, which you can use to unload, transport, and load entire databases or subsets of objects and data.