Fast refresh materialized view based on non-fast refresh view

I've found that I can have two tables and create a fast refresh on commit materialized view based on those tables joined.

What I would like to do however is to make a fast refresh on commit materialized view based on the following:

(1) A table JOINED TO

(2) A complete refresh on demand materialized view which itself is based on other views (ordinary views, that is).

When I try to this I get error ORA-12053, which talked about entries in the from clause having dependencies on each other, even when they clearly don't.

I can work around this by replacing (2) with an ordinary table, and just doing 开发者_开发百科a bulk insert into this table instead of refreshing the materialized view. However, I'd rather not do this if not necessary.

I'll try to work on a minimal example that illustrates the error, but if you could give me an idea of whether what I want to do is possible (preferably by an example) or not possible that would be great.

Follow these facts to understand the error.

1. The Fast Refresh on Commit materialized view is refreshed row by row based on changes made on base tables.
2. The Refresh Complete on Demand materialized view is refreshed by truncate the destination table and reinsert everything.
3. The refresh of the upper materialized view won't be possible because oracle can't track changes on table 2 (that is, the refresh complete materialized view.)

There are restrictions on nested materialized views. I described them in this blogpost. The ORA-12053 is the result of not satisfying the first restriction of nested MV's. Making the underlying MV more complex (a join, aggregate or union all MV), is a solution.

Regards,
Rob.