Tài liệu ORACLE8i- P23 ppt

878 CHAPTER 19 • ORACLE8i DATA WAREHOUSING FEATURES

Here are some examples of altering Mviews:

ALTER MATERIALIZED VIEW mv_emp_date_agg

PCTFREE 10

PCTUSED 60

STORAGE (NEXT 100k);

ALTER MATERIALIZED VIEW mv_emp_date_agg

ADD PARTITION p5 VALUES LESS THAN (‘2003’)

TABLESPACE data_2002;

ALTER MATERIALIZED VIEW mv_emp_date_agg

DISABLE QUERY REWRITE;

When you want to get rid of an Mview that you’ve created, it’s as simple as using

the DROP MATERIALIZED VIEW command. Appendix E has the syntax for this com￾mand, and you’ve already seen it used here in this chapter.

DROP MATERIALIZED VIEW mv_emp_date_agg;

As you might expect, if you want to drop an Mview in a schema other than your

own, you need the DROP ANY MATERIALIZED VIEW privilege.

“But I Already Have Aggregate Tables!”

So, you were ahead of the curve, and you already have a warehouse full of tables that

act like materialized views. Oracle rewards you by allowing you to create an Mview on

an existing table. To do this, you use the ON PREBUILT TABLE clause of the CREATE

MATERIALIZED VIEW command.

There are a few rules to follow for using existing aggregate tables:

• The number of columns and column names in the table must match those in the

query used in the CREATE MATERIALIZED VIEW command.

• The table must be in the same schema as the materialized view being created.

• The table must have the same name as the materialized view being created.

Note: Keep in mind that the Mview you create with the ON PREBUILT TABLE clause will

replace the table of the same name. Thus, that table will be converted into an Mview.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

879

Here’s an example of use of the ON PREBUILT TABLE clause:

-- Create the table that we will convert to a MV

CREATE TABLE emp_agg as

SELECT TO_CHAR(hiredate,’yyyy’) Hireyear,

COUNT(*) “Number Hired”

FROM EMP

GROUP BY TO_CHAR(hiredate,’yyyy’);

Table created.

-- Now, convert this into a MV using the ON PREBUILT TABLE clause.

-- Note the name of the Mview is the same as the prebuilt table name.

CREATE MATERIALIZED VIEW

emp_agg

ON PREBUILT TABLE

REFRESH COMPLETE ON DEMAND

ENABLE QUERY REWRITE

AS

SELECT TO_CHAR(hiredate,’yyyy’) Hireyear,

COUNT(*) “Number Hired”

FROM EMP

GROUP BY TO_CHAR(hiredate,’yyyy’);

Data Dictionary Views for Mviews

Several data dictionary views are provided for use with Mviews. They’re listed in

Table 19.3.

TABLE 19.3: MVIEW DATA DICTIONARY VIEWS

View Name Description

DBA_MVIEWS General information on materialized views in the

database.

MATERIALIZED VIEWS

Beyond Simple

Database Management

PART

III

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

880 CHAPTER 19 • ORACLE8i DATA WAREHOUSING FEATURES

TABLE 19.3: MVIEW DATA DICTIONARY VIEWS (CONTINUED)

View Name Description

ALL_REFRESH_DEPENDENCIES The last refresh information for objects on which

Mviews are dependent.

DBA_MVIEW_ANALYSIS Detailed information on Mviews, such as last refresh

time and SCN.

DBA_MVIEW_DETAIL_RELATIONS Mviews and their dependent base tables.

DBA_MVIEW_JOINS Information on join conditions within Mviews.

DBA_MVIEW_KEYS Various information on Mview relationships. For use

with DBA_MVIEW_DETAIL_RELATIONS to get more

details on Mview relationships.

Refreshing Mviews

When you create a materialized view, you want to make sure that Oracle keeps it

updated to reflect the current data in the underlying tables. There are different ways

to do this, depending on the type of materialized view you are using. The refresh

mode available for a given Mview depends on whether it has joins and aggregates,

joins and no aggregates, or is an aggregate Mview without any joins.

There are two refresh options:

• REFRESH ON COMMIT causes the Mview to be refreshed upon the COMMIT of

any transaction that is changing the underlying base tables of the view.

• REFRESH ON DEMAND delays the refresh of the materialized view until a ware￾house refresh job is executed. We will discuss warehouse refresh later in this

chapter.

You also need to select the refresh method to be used by Oracle when actually

refreshing the view. There are five refresh method choices, each with its own use

restrictions: FAST, COMPLETE, FORCE, NEVER, and ALWAYS.

Let’s take a closer look at the refresh options and refresh methods.

Mview Refresh Options

As mentioned, two refresh options are made available when you create a materialized

view. You can also alter an Mview to change its refresh option.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

881

Refresh on Commit

The REFRESH ON COMMIT option of the CREATE MATERIALIZED VIEW command is

allowed in only two cases. The first is when the Mview is based on a single table (and

is likely aggregating one or more columns of that table). The second case is when the

Mview is created by joining two or more tables together, but the view contains no

aggregates. Other restrictions also apply. Generally when you are doing a refresh on

commit, you’ll want to be able to use the fast refresh method, and restrictions are

associated with that as well.

Refresh on Demand

The REFRESH ON DEMAND option of the CREATE MATERIALIZED VIEW command

is the default refresh option for any Mview created.

Mview Refresh Methods

The various refresh methods can be a bit confusing—in particular fast refresh and com￾plete refresh. Here we’ll examine their functionality and the rules associated with them.

Fast Refresh

A fast refresh, also known as an incremental refresh, causes only the changed rows in

the Mviews to be changed. Only rows that contain aggregates based on the changed

rows of the base view will be changed in the Mview. Fast refresh is probably the best

option, but there are a number of restrictions on it.

If you wish to do a fast refresh of your Mview upon commitment of changes to the

base table, an associated Mview log must be created on the base table. To do this, use

the CREATE MATERIALIZED VIEW LOG command, and in the WITH clause define

which columns you want to track in the log. In order to fast refresh the Mview, you

must include the columns involved in the primary key of the base table. (More infor￾mation on Mview logs can be found later in this chapter.)

Here are the general rules for employing fast refresh:

• The tables in the FROM clause cannot be views.

• Mviews cannot contain nonrepeating expressions such as SYSDATE or

ROWNUM, or nonrepeating PL/SQL functions.

• LONG RAW and LONG data types are not supported for fast refresh.

• You cannot use the HAVING and CONNECT BY clauses.

• The WHERE clause can only contain inner and outer equijoins. All joins must

be connected with ANDs.

• Fast refresh does not support subqueries, inline views, or use of the UNION,

UNION ALL, INTERSECT, or MINUS operations.

MATERIALIZED VIEWS

Beyond Simple

Database Management

PART

III

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com