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

[ericfjchen]

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

[Christian Marquardt_1]

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

Kind regards
Christian

[Sanjay Kumar Suri]

check this doc:

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

sks

[Indira Aramandla]

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

[Indira Aramandla]

Opps forgot the attachment.

[Jean-Luc Oudart]

Hi Eric

check these links
http://www.oracle.com/technology/products/oracle9i/daily/oct24.html
http://www.oracle.com/technology/products/oracle9i/daily/oct17.html

and especailly white paper on Materialized views :
http://www.oracle.com/technology/products/oracle9i/pdf/o9i_mv.pdf

Regards
Jean-Luc

[Yogeeraj_1]

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

[Yogeeraj_1]

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

[Yogeeraj_1]

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