I am usig Oracle 10g R2. We have a Dashboard schema (a small data warehouse) consisting of materialized views. Because of many restrictionis on fast refresh, we are using compolete refresh which run once at night. Right now, Dashboard MVs and master tables are in the same DB and we don't have performance issue. But, we are moving Dashboard to a separate DB which results in MVs defined on remote master tables. When testing creating MVs of remote masters, I experienced extreme bad performace, one of MV has not been created after few hours of run, although the two DBs are on the same server - no network traffic. That worries me more about daily refresh, especially in our production environment, Dashboard would be on a different box from the master DB.
Is my problem typical for MVs of remote masters? From performance point of view, is it even practical to use MVs of remote masters in a data warehouse environment? Do I need to consider a third party tool, like Infomatica? Or what would be a better alternative?