Materialized View Questions

From: Arthur <amerar_at_iwc.net>
Date: 1 May 2002 11:51:54 -0700
Message-ID: <8b622eae.0205011051.1df38d6c@posting.google.com>

Hello,

Continuing my research to understand these animals, I have a couple more questions. I read a document on Technet about Materialized Views, here is a cut of the text:

---

All restrictions from "General Restrictions on Fast Refresh".

They cannot have GROUP BY clauses or aggregates.

If the WHERE clause of the query contains outer joins, then unique constraints must exist on the join columns of the inner join table.

Rowids of all the tables in the FROM list must appear in the SELECT list of the query.

Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.

Materialized views from this category are FAST refreshable after DML or Direct Load to the base tables.

---

I'm unclear about the whole concept surrounding the necessary rowid and having a unique constraint on the inner joined table. I want to do a FAST ON COMMIT refresh of this view, since it takes early 3 hours to do a complete refresh......

Also, once refreshed, is the data purged from the materialized view log? Once applied, I do not need the data in the log anymore. We are running 8.1.5.

Thanks,

If you can, also cc a copy to my email.

Arthur
amerar_at_iwc.net Received on Wed May 01 2002 - 13:51:54 CDT