Differences Between Rowid & Primary Key Materialized Views

Doc ID: Note:254593.1 Type: BULLETIN
Last Revision Date: 18-JAN-2005 Status: PUBLISHED

Differences Between Rowid & Primary Key Materialized Views
==========================================================

PURPOSE
=======

The purpose of this article is to explain the differences between rowid and
primary key materialized views and materialized view logs.
This article also discusses when to use rowid or primary key materialized
views/logs in a distributed environment or a data warehouse environment.

This note does not cover different types of materialized views in a
datawarehouse environment, please refer to Oracle9i Data Warehousing Guide
Release 2.

The terms snapshot and materialized view are synonymous. This article will
use the term "materialized view".

Additional notes are referenced in this article that address specific issues
or provide additional information.

SCOPE & APPLICATION
=====================

To be used by anyone interested in understanding the differences between rowid
and primary key materialized views in distributed and datawarehousing
environments.

CONTENTS
========

1- Introduction
2- Materialized View Log
2-1 Description
2-2 Using rowid materialized view log in distributed environment
2-3 Using rowid materialized view log in summary management
2-4 Using primary key materialized view log
2-5 Using primary key and rowid materialized view log
3- Materialized Views
3-1 Types of Materialized Views
3-2 Materialized views in a distributed environment
3-3 Primary key materialized view in distributed environment
3-4 Rowid materialized view in distributed environment
4- Converting a rowid materialized view to primary key materialized view
4-1 Converting a rowid materialized view to primary key materialized view
4-2 Avoiding complete refresh on Oracle8i rowid to primary key conversion
4-3 Avoiding complete refresh on Oracle7 rowid to Oracle8i primary key
conversion


1- Introduction
===============

Materialized views are schema objects that can be used to summarize, compute,
replicate, and distribute data. A materialized view stores data that has been
obtained from a query, this data remains until next refresh on the materialized
view occurs. They are suitable for distributed and data warehousing
environments.

In distributed environments, materialized views are used to replicate data at
distributed sites and synchronize updates done at several sites. The
materialized views as replicas provide local access to data that otherwise has
to be accessed from remote sites.

In data warehouses, since Oracle 8i, materialized views are used to compute and
store aggregated data such as sums and averages. Materialized views in these
environments are typically referred to as summaries because they store
summarized data. Only a few restrictions limit what can be specified. Any
number of tables can be joined together. However, they cannot be remote tables
if you wish to take advantage of query rewrite feature.

When a materialized view is created, the information stored is up to date to
the moment that it is created, but to keep it up to date we need to refresh
the materialized view regularly.

There are different methods to refresh a materialized view, and it should be
selected at materialized view creation time. One of these methods is called
FAST REFRESH. In summary, this mechanism will refresh only those rows that
have been modified since last refresh or since materialized view creation.
This mechanism is able to know which rows have experienced any modification
because we are going to record modified rows in a object called materialized
view log

For additional information describing materialized view types and refresh
mechanisms and methods, please refer to .


2- Materialized View Log
========================

2-1 Description
---------------

When a materialized view log is created on a master table, Oracle creates a
table named mlog$_. This table is the materialized view log.

Oracle uses this table to store a record of each change made to the master
table. When a materialized view that is mastered by that master table is
refreshed in fast mode, it queries the change records stored in the
materialized view log to determine which rows to pull from the master table
and replace in the materialized view.

Each change record uses a unique identifier to indicate for which row in
the master table the change was made. This unique identifier is either rowid
or primary Key. Rowid will use the rowid of the record in the master table to
locate the changed row. Primary key will use the primary key of the record to
locate the changed row.

Either, or both can be specified when the materialized view log is created. If
neither is specified, it will default to primary key in Oracle8/9i. If both
are specified, it only means that the materialized view log will capture both
the rowid and the primary key values for the changed row. The actual value
used as the unique identifier during the refresh depends on which was specified
at the creation of the actual materialized view.

In order to find out what materialized view type(s) are supported by a
materialized view log, use view dba_mview_logs:

select log_owner, master, log_table, rowids, primary_key
from dba_mview_logs;

LOG_OWNER MASTER LOG_TABLE ROWIDS PRIMARY_KEY
--------------- ---------- ----------- --------- -----------
SCOTT DEPT MLOG$_DEPT NO YES


2-2 Using rowid materialized view log in distributed environment
----------------------------------------------------------------

In Oracle7 only rowid materialized view logs are available, and since Oracle8
in distributed environments rowid materialized view logs exist for backward
compatibility with Oracle7, this is having materialized view sites in release
Oracle7.

In Oracle7, the rowid materialized view log structure is as follows:

SQL>create table M1 (C1 number(4) , dname varchar(14));
SQL>create materialized view log on M1;
SQL>desc mlog$_M1
Column Name Null? Type
------------------------------ -------- ----
M_ROW$$ VARCHAR2(18)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)

The rowid of the new/modified row is stored in materialized view log M_ROW$$
column.

In Oracle8/Oracle9i, we can also use rowid materialized view log if the master
table does not contain a Primary key. As the default in oracle8/oracle9i for
materialized view log is primary key, creating materialized view log without
the WITH ROWID option on a master table without a primary key will result
ora-12014:

SQL> create table M1 (C1 number(4) , dname varchar(14));
SQL>create materialized view log on M1;
ERROR at line 1:
ORA-12014: table 'M1' does not contain a primary key constraint

In Oracle8/Oracle9i, the rowid materialized view log structure is as follows:

SQL>create materialized view log on M1 with ROWID;
SQL> desc mlog$_M1
Column Name Null? Type
------------------------------ -------- ----
M_ROW$$ VARCHAR2(255)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)

If master table is in Oracle8/Oracle9i and materialized view site in Oracle7,
you have to create materialized view log with rowid option for fast refresh.


2-3 Using rowid materialized view log in summary management
-----------------------------------------------------------

Rowid materialized view logs in data warehouse/summary management are used
to support fast refresh of join view (MJV), aggregate (MAV), and nested
materialized views; this is the only materialized view log type supported
in this environment. These materialized view logs are often created with
filter columns. These columns are additional, non-primary key columns for
which the materialized view log change record captures values.

In relation to materialized view logs, when creating a materialized view in a
Datawarehouse environment and the materialized view contains aggregates with a
single table, a materialized view log must contain all columns referenced
in the materialized view and must have been created with the INCLUDING NEW VALUES clause:

CREATE TABLE FACT (store_key number(8), prod_key number(6)
, time_key number(8), dollar_sales number(12));

If columns store_key , time_key and dollar_sales have to be referenced in the
materialized view:

CREATE MATERIALIZED VIEW log on FACT
with rowid (store_key,time_key,dollar_sales)
including new values;


2-4 Using Primary key materialized view log
-------------------------------------------

When creating a primary key materialized view log, you must have a valid
primary key on master table. In case you try to create a primary key
materialized view when master table do not have primary key, error ORA-12014
will be raised.

A materialized view log structure is as follow:

Name Null? Type
----------------------------------------- -------- ---------------
EMPNO NUMBER(4)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)

EMPNO is the primary of the master table.

Since Oracle8 Primary key is the default for materialized view logs. In
replication environments it should be used unless any of the exceptions
signalled at "Using rowid materialized view log in distributed environment"
section.

Primary key materialized view log do have applications on a summary management
environments.


2-5 Using Primary key and rowid materialized view log
-----------------------------------------------------

We can create materialized view log with both primary key and rowid

SQL>create materialized view log on dept with rowid,primary key;
SQL> desc mlog$_dept;

Column Name Null? Type
------------------------------ -------- ----
DEPTNO NUMBER(2)
M_ROW$$ VARCHAR2(255)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)

We can see that both rowid and primary key of new/modified/deleted row will
be recorded. This configuration may be used when we have materialized view
site in different version ( Oracle7 and Oracle8+).

3- Materialized Views
=====================

3-1 Types of Materialized Views
-------------------------------

There are different ways to distinguish one type of materialized views from
another. One of this classification is based on the environment where the
materialized view is involved and there we can distinguish different types:

- Distributed environments

* Primary Key: The materialized view includes the columns that conform the
master table primary key. The materialized view will identify
rows modified at master site by their primary key.

* Rowid : The materialized view includes a column that will stores
master table rowid. The materialized view will identify rows
modified at master site by their primary key.

- Datawarehousing environment

* Materialized Views with Aggregates (Aggregate): The columns that conforms
the query used to create the materialized view contains
aggregate.

* Materialized Views Containing Only Joins (Join View): The query used to
create the query is a join used to precalculate costs joins.

* Nested Materialized Views (Complex) : A materialized view whose definition
is based on another materialized view.

This note does not cover different types of materialized views in a
datawarehouse environment, please refer to Oracle9i Data Warehousing Guide
Release 2.

To check what type of Materialized view we are using we can query to
dba_snapshots view:

select owner, name, refresh_method from dba_snapshots;

This classification could be applied to materialized views but it is not
applicable to materialized view logs.


3-2 Materialized views in a distributed environment
---------------------------------------------------

When a materialized view is created, several additional mechanisms are created
at the materialized view site to support the materialized view. Specifically,
a base table, at least one index, and possibly a view are created. If you
create an updatable materialized view, then an internal trigger and a local log
(the updatable materialized view log) are also created at the materialized view
site.

For Oracle8i and higher a base table and a materialized view objects are going
to be created. These objects are going to have the same name, but it won't be
possible to access directly to the base table.

Example:

SQL> DROP TABLE S_DEPT;
DROP TABLE S_DEPT
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "SCOTT"."S_DEPT"

to drop this objects we will have to drop the materialized view.

In addition to this an index called I_SNAP$_ will be
created for base table.

For releases lower than Oracle8i a base table is created with the name
SNAP$_ and a view with the name of the materialized
view on top of this table is created. Also the index
I_SNAP$_ is going to be created if the materialized
view is a primary key materialized view.

If an updatable materialized view is created, an updatable materialized view
log (USLOG$_) is used to determine which rows must be
overwritten or removed from a materialized view during a fast refresh.
A read-only materialized view does not create this log, and Oracle does not
use this log during a complete refresh because, in this case, the entire
materialized view is replaced.


3-3 Primary key materialized view in distributed environment
------------------------------------------------------------

This type of materialized views appeared for first time in release 8.0 and they
became the default type. To create a primary key materialized view, a primary
key must exist on the master (source) tables, and the materialized view log
must be created with primary key (default value).

For a fast refresh of a primary key materialized view, it is required that
the materialized view log contains the primary key of the master table.
In case the materialized view log does not contain primary key information
an error ORA-12031 will be raised.

Also, in case we are trying to create a materialized view primary key
based on an Oracle7 master table, it is possible to get an error ORA-12028
at creation time. This is because the Oracle7 does Oracle7 knows nothing
about primary key feature. This is the usual way to get an error ORA-12028
BUT There are also some unusual ways to get it, where basic troubleshooting
won't help:

- Bug 703287 , Schema Manager (Oracle Enterprise Manager) does not support
WITH option. Fixed in Schema Manager 2.1.0.
- Having GLOBAL_NAMES = TRUE more than once in the init.ora has caused
ORA-12028 and ORA-2067 errors.
- Forcing illegal global name for the database by directly updating data
dictionary table instead of using ALTER DATABASE RENAME GLOBAL NAME...
command can cause ORA-12028. Note that the ONLY supported way to change
the global name is the ALTER DATABASE command above.
- Invalid DBMS_UTILITY package can cause ORA-12028. Ensure that this package
is valid.
- Note 1059092.6 , ORA-23346 DURING GENERATE_REPLICATION_SUPPORT
If we pay attention to the description of the objects created by the
materialized, we will see:

For the base table:

SQL> desc s_dept
Name Null? Type
----------------------------------------- -------- -------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

For USLOG$ table in case the materialized view is updatable:

SQL> desc uslog$_s_dept;
Name Null? Type
----------------------------------------- -------- ------------
DEPTNO NUMBER(2)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)

And the index I_SNAP$_S_DEPT is going to be defined on primary key columns
(DEPTNO) of master table.

The main benefit of primary key materialized views is that they allow the reorganization
of the master tables without requiring a complete refresh from master tables after the reorganization,
unlike the rowid materialized view. This is because the primary key record
identifier does not change during master table reorganization, where as the rowid
does. An example of master table reorganization with a primary key
materialized view follows:

1) This test is done with the scott.dept table

connect scott/tiger

2) If the dept table do not have primary key, add it using the following command:

alter table dept add constraint pk_dept primary key(deptno);

3) As a primary key exists on master table (dept), default materialized view log is
created with primary key. In case a primary key does not exist on the master table
an error ORA-12014 will be raised.

create materialized view log on dept;

4) Creation of materialized view ( default is primary key):
ORA-23415 will be raised in case the materialized view log do not record the
primary key


create materialized view s_dept refresh fast as select * from dept;

5) Perform insert in materialized view and commit it:

insert into dept (deptno) values (49);
commit;

If you select from the materialized view log you should see a row count of 1.

select count(*) from mlog$_dept;
1 row selected

6) Now we want to perform a reorganization of the master table
The below procedure will devalidate internal triggers which populates
the materialized view log when modification are done on the master table:

execute DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION('SCOTT','DEPT');

7) Now export the dept table:

exp scott/tiger tables=dept


8) Truncate the table to prepare it for import.

truncate table dept;

9) Import table dept.

imp scott/tiger tables=dept ignore=y

10) Turn on internals triggers.

execute DBMS_MVIEW.END_TABLE_REORGANIZATION('SCOTT','DEPT');

11) Refresh the materialized view, default is refresh method indicated in DBA_MVIEWS.

execute dbms_mview.refresh('S_DEPT','f');

12) If the materialized view is a rowid materialized view, then the error
ORA-12034 will appear at refresh time, and the materialized view will need
a complete refresh.

execute dbms_mview.refresh('S_DEPT','c');


3-4 Rowid materialized view in distributed environment
------------------------------------------------------

In Oracle7 the materialized views are always rowid materialized views, primary
key materialized views do not exist. That's why rowid materialized views
provide compatibility with master tables in releases of Oracle prior to 8.0.
You can also use rowid materialized views if the materialized view does not
include all primary key columns of the master tables or master table does not
contain a primary key.

For a fast refresh of a rowid materialized view, it is required that the
materialized view log contains the rowids of the master table rows. In case
the materialized view log does not contain rowid information an error ORA-12032
will be raised.

If we pay attention to the description of the objects created by the
materialized, we will see:

For the base table in releases Oracle8i and higher:

SQL> desc s_dept
Name Null? Type
----------------------------------------- -------- -------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

For the base table in releases Oracle8 and lower:

SQL> desc snap$_s_dept
Name Null? Type
----------------------------------------- -------- -------------
M_ROW$$ VARCHAR2(255)
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

For USLOG$ table in case the materialized view is updatable:

SQL> desc uslog$_s_dept;
Name Null? Type
----------------------------------------- -------- --------------
M_ROW$$ VARCHAR2(255)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)

And the index I_SNAP$_S_DEPT is going to be defined on column M_ROW$$,
regardless release that we are working with.

As explained before, rowid materialized views are not eligible for fast refresh
after a master table reorganization until a complete refresh has been performed.
If you try to refresh fast a rowid materialized view after a table
reorganization, you'll get an error ORA-12034.

On distributed environments, rowid materialized views must be based on a single
table and cannot contain any of the following:

- Distinct or aggregate functions
- GROUP BY or CONNECT BY clauses
- Subqueries
- Joins
- Set operations


4. Converting a rowid materialized view to primary key materialized view
=========================================================================

The majority of customers perform an incremental upgrade of their distributed
environments from Oracle7 to Oracle8 and above. Once the incremental upgrade
is complete Oracle recommends customers switch from rowid to primary key
materialized views, to take advantage of the performance and maintenance
improvements they provide.

It is possible to convert a rowid materialized views to primary key
materialized view without performing a complete refresh in oracle8 and higher.

4-1 Converting a rowid materialized view to primary key materialized view
-------------------------------------------------------------------------

Converting a rowid materialized view to primary key materialized view, requires
you to drop and recreate the materialized view log and initially to perform a
COMPLETE refresh on the materialized view site before being able to perform
fast refreshes.

a. On the master site, drop the existing rowid materialized view log and
recreate it so it tracks by primary key:

SQL> drop materialized view log on dept;
SQL> create materialized view log on dept with primary key;

b. On the materialized view site, convert materialized view and perform a
complete refresh:

SQL> alter materialized view s_dept refresh fast with primary key;
SQL> execute dbms_mview.refresh('s_dept','c');

c. From now onwards, you will be able to perform fast refreshes:

SQL> execute dbms_mview.refresh('s_dept','f');

4-2. Avoiding complete refresh on Oracle8i rowid to primary key conversion
--------------------------------------------------------------------------

The steps described in this section can only be followed if the master site
and materialized view sites involved are already running Oracle8 or
above. This procedure is not applicable if the materialized view has been
upgraded from Oracle7.

a. First, on the master site, alter the materialized view log to track by
primary key.

SQL> alter materialized view log on dept add primary key;

b. Next, at each materialized view site, perform a fast refresh of the
materialized view.

Note that this step is necessary to guarantee that a fast refresh will
continue to work AFTER the materialized view is altered. If this step is
not run, ORA-12034 will be returned the next time that a fast refresh is
executed for the snapshot and a complete refresh will be necessary to
keep on refreshing this materialized view. See the end of this section
for more details.

SQL> execute dbms_mview.refresh ('dept','f');

c. Alter the materialized view to refresh fast with Primary Key.

SQL> alter materialized view dept refresh fast with primary key;

d. After altering the materialized view, you will continue to be able to perform
fast refreshes:

SQL> execute dbms_mview.refresh('dept','f');

Note that now the materialized view log is tracking both rowid and primary key.
Tracking both will increase the size of the materialized view log. If you do not
have materialized views that are fast refreshing with Rowid then you should
consider tracking only the primary key.


4-3 Avoiding complete refresh on Oracle7 rowid to Oracle8i primary key conversion
----------------------------------------------------------------------------------

This section is intended for use as a guide to avoiding a complete refresh
after migrating Oracle7 read only materialized view environments to Oracle8i or
above. It uses the new Oracle8i clause 'on prebuilt table' with the 'create
materialized view' command. These steps are only appropriate if ALL
materialized view of the affected table are being converted.

The on prebuilt table clause lets you register an existing table as a
preinitialized materialized view. This is particularly useful for registering
large materialized views. The table must have the same name and be in the same
schema as the resulting materialized view. The resulting materialized view must
be a primary key materialized view, if you try to create a rowid materialized
view an error ORA-12058 will be raised.

Below there are a set of steps that show how you can convert Oracle7 read only
rowid materialized view to 8i primary key materialized view without having to
perform a complete refresh.

This method will be useful in situations where the materialized views are very
large or a slow network link exists between the master and the materialized
view site.

*** Please Note that the procedure detailed here is not formally documented
or supported. It has been tested internally, however, and works as documented.
It is recommended that the steps are performed in a test environment before
relying on them.

a. Migrate the databases involved to Oracle8i using the documented migration
methods. Once the migration is complete use the following steps to convert
the read only materialized view.

NOTE: make sure no changes are made to the master environment during
this process.

b. On the master site, drop all materialized view logs and recreate them so
that they are primary key based.

SQL> drop materialized view log on dept;
SQL> create snapshot log on dept;

c. On the materialized view site perform the following:

- Drop all the views associated with the materialized views:

SQL> drop view dept;

- Rename all SNAP$_ tables to
:

SQL> rename snap$_dept to dept;

- Drop the M_ROW$$ column from each of these tables:

SQL> alter table dept drop column M_ROW$$;

- Issue the drop materialized view command for each materialized view to
clean up the data dictionary references to them:

SQL> drop materialized view dept;

- Issue the create materialized view command using the new Oracle8i
clause 'on prebuilt table'. It is not possible to create the
materialized view as rowid, an error ORA-12058 will be raised in case
you try to create the materialized view as rowid:

SQL> create materialized view dept
on prebuilt table
refresh force with primary key
as select * from scott.dept@


References
==========
Note 258227.1 Overview of the types of MVIEW available in Oracle 8 and 9i Note 179466.1 Diagnosing ORA-12015 fast refresh materialized view /
complex queries Note 179466.1 Diagnosing ORA-12034 Materialized View Log Younger than
Last Refresh Note 179469.1 Diagnosing ORA-12004 Refresh Fast Cannot be Used

Oracle8 Replication Release 2
Oracle8i SQL Reference Release 3
Oracle8i Replication Release 2
Oracle8i Data Warehousing Guide Release 2
Oracle9i SQL Reference Release 2
Oracle9i Advanced Replication Release 2 (9.2)
Oracle9i Data Warehousing Guide Release 2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/193161/viewspace-50263/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/193161/viewspace-50263/