RelationalDBDesign RelationalDBDesign 


Performance Tuning   «Prev 

Replication of Data Using Materialized Views

You can manipulate and query data from remote databases. However, it is not desirable to have large volumes of data constantly sent from one database to another. To reduce the amount of data being sent across the network, you should consider different data replication options.
In a purely distributed environment, each data element exists in one database. When data is required, it is accessed from remote databases via database links. This purist approach is similar to implementing an application strictly in third normal form, an approach that will not easily support any major production application. Modifying the application's tables to improve data retrieval performance involves denormalizing data. The denormalization process deliberately stores redundant data in order to shorten users’ access paths to the data.
In a distributed environment, replicating data accomplishes this goal. Rather than force queries to cross the network to resolve user requests, selected data from remote servers is replicated to the local server. This can be accomplished via a number of means, as described in the following sections. Replicated data is out of date as soon as it is created. Replicating data for performance purposes is therefore most effective when the source data is very infrequently changed or when the business processes can support the use of old data.
Oracle’s distributed capabilities offer a means of managing the data replication within a database. Materialized views replicate data from a master source to multiple targets. Oracle provides tools for refreshing the data and updating the targets at specified time intervals.

Oracle Database12c DBA

Materialized View Match

If the list of columns, tables to be joined in the query match those in the view (and the GROUP BY clause if aggregates are used), the cost-based optimizer will use the materialized view.

If additional tables are joined in the query, buy the query includes all the joined tables in the materialized view, the cost-based optimizer will use the materialized view.

If additional columns are mentioned in the query, but they can either be calculated or easily retrieved through a constraint, the cost-based optimizer will use the materialized view.

If the materialized view is GROUPed at a finer level of detail than the query, and the other conditions for use are met, the cost-based optimizer will use the materialized view.