[oracle-l] Re: MVs
- From: Vladimir Begun <Vladimir.Begun@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 23 Jan 2004 16:45:10 -0800
Oracle9i Data Warehousing Guide Release 2 (9.2)
8 Materialized Views
If you specify REFRESH FAST, Oracle performs further verification of the
query definition to ensure that fast refresh can be performed if any of
the detail tables change. These additional checks are:
* A materialized view log must be present for each detail table.
>>>>* The rowids of all the detail tables must appear in the SELECT list
>>>> of the materialized view query definition.
* If there are no outer joins, you may have arbitrary selections and
joins in the WHERE clause. However, if there are outer joins, the
WHERE clause cannot have any selections. Further, if there are outer
joins, all the joins must be connected by ANDs and must use the
equality (=) operator.
* If there are outer joins, unique constraints must exist on the join
columns of the inner table. For example, if you are joining the fact
table and a dimension table and the join is an outer join with the
fact table being the outer table, there must exist unique constraints
on the join columns of the dimension table.
If some of these restrictions are not met, you can create the materialized
view as REFRESH FORCE to take advantage of fast refresh when it is possible.
If one of the tables did not meet all of the criteria, but the other tables
did, the materialized view would still be fast refreshable with respect to
the other tables for which all the criteria are met.
A materialized view log should contain the rowid of the master table. It is
not necessary to add other columns.
To speed up refresh, you should create indexes on the materialized view's
columns that store the rowids of the fact table.
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
RamСn Estevez wrote:
> I checked metalink and the documentation either is not clear or I don't
> understand, choose that one, but just are two tables with a single join.
Other related posts:
