Materialized View Help

From: Arthur <amerar_at_iwc.net>
Date: 30 Apr 2002 12:07:04 -0700
Message-ID: <8b622eae.0204301107.38f8da5a@posting.google.com>

Hello,

For some reason, I cannot find any detail documentation that truely goes into detail about materialized view and logs. I am having trouble understanding some of these concepts.

When creating a materialized view, there are many parameters that you can include as part of your create statement. I do not understand the following:

1. ON PREBUILT TABLE
2. QUERY REWRITE

Regarding the Materialized View Logs, I'm totally lost. I do not understand the following:

1. WITH (primiary key / rowid)
2. NEW VALUES

Here is my situation. We have a view that is basically an outer join of 2 tables. This view is called PH_VIEW. The underlying tables contain more than 3 million records. So, I wanted to create a materialized view in order to speed up some of the reports run off of this view.

I created a simple view and it sped everything up. Wonderful. Now, I want to re-create the view with a 'FAST ON COMMIT' refresh. In order to do this, I need a materialized view log. When I issue this statement:

             CREATE MATERIALIZED VIEW LOG ON ph_view;

I get this error: ORA-12014: table 'PH_VIEW' does not contain a primary key

Basically I want to create this materialized view with a fast on commit refresh. But I'm having trouble understanding all of the parameters and such.

Does anyone feel like taking the time to explain some of these things, or pointing me to a source of reliable documentation? Oracle documentation is not helping....

Thanks.

Please send a copy or your reply to my email: amerar_at_iwc.net

Arthur Received on Tue Apr 30 2002 - 14:07:04 CDT