I encountered strange behaviour while creating a Materialized View for an Join-Aggregate Query (MAJ): SQL & error see below.
If I change the SQL to produce an inner join instead of the left-outer join (by dropping the +) _and_ include the column "day_id" (which is the only missing column which is in the GroupBy but _not_ in the select) , the MV creation will succeed !!!
That is, is it right, that
(1) left-outer joins will result in "complex query" for MVs
(2) not including _all_ GroupBy columns in the selected columns will also result in a "complex query"
Or more general: what are the precise and exhaustive situations in which a query is considered "complex" in the context of MV creation ???
Many Thanks, Tobias
ORA-12015 cannot create a fast refresh snapshot from a complex query
Cause: Neither ROWIDs and nor primary key constraints are supported for complex queries.
Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE option or create a simple snapshot.
CREATE MATERIALIZED VIEW D_MV_BASE2
REFRESH FAST ON DEMAND WITH ROWID
ENABLE QUERY REWRITE
trafficbase2 tb, dim_stime st
tb.stime_id = st.stime_id(+)
Click Here to Expand Forum to Full Width