Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

How to create an Oracle 9i Materialized View & Materialized View log?

ericfjchen
Regular Advisor

How to create an Oracle 9i Materialized View & Materialized View log?

1. Does any good document teach us create MVIEW step by step?
2. What kind of refresh type does MVIEW need MVIEW LOG?

Pls kindly share your opinion.

Thanks

Eric FJ
8 REPLIES
Christian Marquardt_1
Regular Advisor

Re: How to create an Oracle 9i Materialized View & Materialized View log?

Hi Eric,
check out this link:
http://www.databasejournal.com/features/oracle/article.php/2192071

Kind regards
Christian
Sanjay Kumar Suri
Honored Contributor

Re: How to create an Oracle 9i Materialized View & Materialized View log?

check this doc:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96567/repmview.htm#25271

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Indira Aramandla
Honored Contributor

Re: How to create an Oracle 9i Materialized View & Materialized View log?

Hi Eric,

The terms Materialized View (MVIEW) are synonymous and they will be used
interchangeably. A materialized view is a replica of a target master from a single point in time. It was introduced in oracle 7 and The most remarkable MVIEW enhancements in Oracle9 are the multitier materialized views and support for user-defined types.

A typical create MVIEW statement has the following form:

create materialized view snap_test
refresh fast
start with sysdate
next sysdate+1 as
select * from master_table@master_db;


This statement should be executed in snap_db. It will create

- A MVIEW base table called SNAP_TEST which has the same structure as
MASTER_TABLE.
- A new object namely SNAP_TEST of type materialized view
- A UNIQUE index on the PK columns or ROWID depending on the type of
MVIEW log at master site

Metalink Note:258227.1 explains â Overview of the types of MVIEW available in Oracle 8 and 9iâ . This will privide steps that you require.

Now your second query,
After the materialized view is created, changes can be made to the master table, and possibly also to the materialized view. To keep a materialized view's data relatively current with the data in the master table, the materialized view must be periodically refreshed.

You can choose between Complete, Fast, and Force refresh types. Complete refresh is performed by deleting (or truncating) the rows from the snapshot and inserting the rows satisfying the mview query.

Attached is a document for Oracle's materialized view fast refresh.


I hope this helps

Indira A
Never give up, Keep Trying
Indira Aramandla
Honored Contributor

Re: How to create an Oracle 9i Materialized View & Materialized View log?

Opps forgot the attachment.
Never give up, Keep Trying
Jean-Luc Oudart
Honored Contributor

Re: How to create an Oracle 9i Materialized View & Materialized View log?

Yogeeraj_1
Honored Contributor

Re: How to create an Oracle 9i Materialized View & Materialized View log?

hi Eric,

1. Documentations
please try: http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76994/mv.htm#721

hope this helps!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: How to create an Oracle 9i Materialized View & Materialized View log?

hi again,

2.

the docs says:

Materialized view logs are required if you want to use fast refresh. They are defined using a CREATE MATERIALIZED VIEW LOG statement on the base table that is to be changed. They are not created on the materialized view. For fast refresh of materialized views, the definition of the materialized view logs must specify the ROWID clause. In addition, for aggregate materialized views, it must also contain every column in the table referenced in the materialized view, the INCLUDING NEW VALUES clause and the SEQUENCE clause.


hope this helps too!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: How to create an Oracle 9i Materialized View & Materialized View log?

hi again,

also note that you need no mv logs to refresh, they are optional -- with them we might be able
to do an incremental refresh, without them -- a complete refresh is called for.

hope this helps too!

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)