I have created materialized view successfully but when i am trying to add filter condition on that materialized view along with refresh fast clause i’m getting this error:
ORA-12015: cannot create a fast refresh materialized view from a complex query
this my regular sample script of my mv:
[code]
create materialized view mv
build immediate
refresh fast
as
select
t1.AppId,
t1.LineOfBusiness,
t1.ClientId
from
t1,
t2,
t3,
t4
where
t2.AppId = t1.AppId and
t4.Brand(+) = t2.Brand and
t4.Account(+) = t2.Account and
t3.ProfileId(+) = t1.ProfileId
group by
t1.AppId,
t1.LineOfBusiness,
t1.ClientId;
[/code]
now i want to filter by:
[code]
t1.ClientId > 0
[/code]
how to solve this.
thanks in advance.
Hello Albert,
generally this the explanation for that error :
ORA-12015: cannot create a fast refresh materialized view 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
materialized view.
also to Diagnosing ORA-12015 fast refresh materialized view / complex queries that’s depend on the database version there is some restrictions ex:
Oracle10g – 10.2
——————–
Fast refreshable materialized views must be based on master tables, master materialized views, or synonyms of master tables
or master materialized views. Complete refresh must be used for a materialized view based on a view.
for further details about diagnosing refer to : Note.179466.1
hope this helpful.