'Selecting across a link vs Selecting within the database

We have a view in our transaction database that is used to pull information from a number of views (i.e. view built on views). When we access this view across a database link from our data warehouse the response time is really good. When we recreated the view within the data warehouse based on materialized views from the transaction database, the response time completely tanked. The first query (across the database link) ran in a few seconds. The second one (within the warehouse itself) ran in 45 minutes (!!). The explain plan cost for the first one is about 500. The explain plan cost for the second one is in the 5 millions. We have looked at indexes on the base tables (transactional database) vs the materialized views (data warehouse), and they are identical. Is it because we are creating our data warehouse resident views off materialized views as opposed to base tables in the transactional database?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source