Oracle Database Discussions

I have a situation where I need to create a materialized view worth of 6 months of data with fast refresh option from the master table. Somehow whenever I have the where clause added with the date field then it craps out with "ORA-12015: cannot create a fast refresh materialized view from a complex query".

Here is what I am trying to do. Please let me know if there is any other way to accomplish this.

create table test (id number, date_time DATE);

CREATE MATERIALIZED VIEW LOG ON test WITH ROWID;

CREATE MATERIALIZED VIEW cms.scoreboard_statistics_mv
BUILD IMMEDIATE
REFRESH FAST
WITH ROWID
AS
SELECT * from test
WHERE date_time >= sysdate - 180;

ORA-12015: cannot create a fast refresh materialized view from a complex query


Thanks,
Raj