CODEWITHSUNDEEP
oracle-database
Rajkumar Vasan
Rajkumar Vasan

Reputation: 712

Why do we need Materialized View in oracle if Oracle Data Pump utility can refresh the data?

We have two schema in different oracle server. We are planning to use one schema as a Transaction database and other schema for Reporting.

As Transaction database is the entry point for any user generated data, we want to have this data to be replicated/sent to Reporting schema periodically. We thought of creating Materialized View Logs in Transaction Database and Materialized View in Reporting database. Then we planned to do a scheduled Fast refresh using a Db Link.

However, a DBA suggested us to use Data Pump, which will export and import the entire schema. The refresh needs to be done once in a day. Which one is an optimal solution in terms of performance and network usage?

Upvotes: 3

Views: 1553

Answers (1)

stefan.schwetschke
stefan.schwetschke

Reputation: 8932

Summary

Both approaches have their pro and cons. There is no general answer. You have to benchmark.

Details

Materialized Views over Database Link

You have two possibilities to make this work.

The first possibility is using the fast refresh feature. With this feature Oracle updates the materialized view on each change, using an efficient delta mechanism. This can save a lot of a data volume when only small parts of tables are changed during each day. And you data is always fresh. But this mechanism does not work with all kinds of tables (e.g. problems with certain joins and with LOB columns). And your when one database goes down, the materialized view will be out of sync and has to be rebuilt. And last: The bookkeeping for the delta changes puts additional pressure on all write operations on the source table.

The second possibility is using a full refresh, e.g. with materialized view groups (DBMS_REFRESH package). This will always trigger a full refresh, but the won't be additional write pressure for the book keeping.

In both cases, the two database are now tightly coupled: Changes on one database will also trigger changes on the other database. And you cannot move the databases at will: They will always need a fast connection with low latency and without interfering firewalls. And in the long run, the databases have to be up at the same time, or you risk to loose the materialized view.

ETL tool

As an alternative, you can always use some kind of ETL tool, that extracts the data from one database, transforms the data according to some given rules and the loads the result in the other database. You can (mis-)use oracle Datapump for this task, or use any third party tool. An ETL tool can cache the data, transfer it using any kind of WAN connection and transform it, when the database schemas of the source or the target has to be changed. Usually an ETL tool also provides some kind of delta mechanism.

With an ETL tool you are more flexible, but you are dependent on a component outside of your database, which must be maintained.

Upvotes: 2

Related Questions