Re: Materialized View Refresh Question

Hi Arthur,

This is from the doco

"If a materialized view contains joins and aggregates, then it cannot be fast refreshed using a materialized view log. Therefore, for a fast refresh to be possible, only new data can be added to the detail tables and it must be loaded using the direct path method."

Have a look at the doco on materialised views it lists all things required to use fast refresh.

Have you looked at creating a composite index on PH_KEY and FIELDA on both tables? this might help get rid of the need to use a view. Put the column with the lowest distinct values (low cardinality) first as this will almost ensure uniqueness
i.e create index .. on table ... (fielda,ph_key)

Getting rid of the group by and making the materialized view a simple join should enable you to get fash refresh happening.

If you have time, check whether creating AUDIT_TRAIL as either a IOT or creating an index on FieldB,FieldA,PH_KEY (all columns required for join) enables a FAST FULL INDEX SCAN. just interested...

Have Fun

--
An Oracle DBAs undocumented init.ora parameters


_OVERCOME_POOR_DESIGN = TRUE

_DISABLE_BAD_PERFORMANCE = TRUE


Posted via dBforums
http://dbforums.com