Oracle Database Discussions

Hi,

I'm currently trying to create a materialized view that can be fast refreshed, but it complains each time. I think it's because I'm using a constraint substr(x,1,10) so complete refreshes are all that can be done?

My steps are :

create materialized view log on a_table with rowid;
create materialized view log on b_table with rowid;
create materialized view log on c_table with rowid;
create materialized view log on d_table with rowid;

and the materialized view is

create materialized view MY_VIEW
build immediate
enable query rewrite
REFRESH FAST
as
SELECT bt.mid AS mid, ct.imid AS imid, bt.iid AS iid, vendor_name AS make, at.dname AS dname, bt.timestamp AS timestamp
FROM b_table bt, c_table ct, d_table dt, a_table at
WHERE bt.mid=ct.mid
AND substr(bt.iid,1,5)=dt.val1
AND at.profile_id=dt.profile_id
order by bt.timestamp desc;

ORA-12052: cannot fast refresh materialized view MY_VIEW


I'm at a loss, I can make it without the fast refresh, but the table view is huge and updating it completely each time isn't going to work :(

Any help or suggestions would be greatly appreciated,

Regards,

Brian