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
BUILD IMMEDIATE
REFRESH FAST ON DEMAND WITH ROWID
ENABLE QUERY REWRITE
AS
select
'D' agglevel,
min(st.stime_id) stime_id,
tb.originator_id,
tb.src_iprange_id,
tb.dest_iprange_id,
tb.service_id,
tb.module_id,
sum(tb.load) load,
sum(tb.ucount) ucount,
max(tb.max_bandwidth) max_bandwidth,
min(tb.min_bandwidth) min_bandwidth,
min(tb.stime) stime
-- ,st.day_id
from
trafficbase2 tb, dim_stime st
where
tb.stime_id = st.stime_id(+)
group by
st.day_id,
tb.originator_id,
tb.src_iprange_id,
tb.dest_iprange_id,
tb.service_id,
tb.module_id
/