Materialized Views

[ThomVF]

Anyone have any useful experience with MVs in 9iR2? I cannot get a FAST refresh to work with an Outer-Join non-Aggregate MView.

The Explain_MView results say "the unique constraints does not exist on the join columns of the inner table" when I clearly have a Primay Key (Unique) constraint on the Inner columns.

Here is my Select for the MV:

select	f.rowid			fact_rid
,	f.ACCOUNT_IDENTIFIER	fact_acctid
,	f.balance_amount	fact_balance
,	d.rowid			dim_rid
,	d.account_identifier	dim_acctid
,	d.risk_rating		dim_risk_rating
,	d.name			dim_name
from		CREDIT_FACT		f
	,	ACCOUNT_DIMENSION	d
where		f.ACCOUNT_IDENTIFIER (+) =
 	        d.ACCOUNT_IDENTIFIER
	;

The Inner table would be "d" (Account_Dimension) and it has primary Key on the ACCOUNT_IDENTIFIER column.

Here are the MV Log files as well:

create materialized view log on CREDIT_FACT
tablespace users
logging, noparallel, nocache
WITH rowid, primary key, 
sequence(account_identifier, balance_amount)
including new values;

create materialized view log on ACCOUNT_DIMENSION
tablespace users
logging, noparallel, nocache
WITH rowid, PRIMARY KEY, sequence(risk_rating, name)
including new values;

Anyone know about bugs here?
V9.2.0.1.0

Tom
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com