-
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
/
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|