To improve the performance of an application, you can make local copies of remote tables that use distributed data or create summary tables based on GROUP BY operations. Oracle provides materialized views to store copies of data or aggregations. Materialized views can be used to replicate all or part of a single table or to replicate the result of a query against multiple tables; the database can automatically refresh the replicated data at time intervals that you specify.
Functionality of Materialized Views
Materialized views are copies (also known as replicas or snapshots) of data based on queries. In its simplest form, a materialized view is similar to a table created by a command such as the following:
In this example, a regular table named LOCAL_BOOKSHELF is created in the local database and is populated with data from a remote database (defined by the database link named REMOTE_ CONNECT). Once the LOCAL_BOOKSHELF table is created, though, its data may immediately become out of sync with the master table (BOOKSHELF@REMOTE_CONNECT). Also, LOCAL_ BOOKSHELF may be updated by local users, further complicating its synchronization with the master table.
Benefits of Materialized Views
Despite these synchronization problems, there are benefits to replicating data in this way. Creating local copies of remote data may improve the performance of distributed queries, particularly if the master table’s data does not change frequently. You may also use the local table creation process to restrict the rows returned, restrict the columns returned, or generate new columns (such as by applying functions to selected values). This strategy is common in decision-support environments, in which complex queries are used to periodically “roll up” data into summary tables for use during analyses.
Materialized views automate the data replication and refresh processes. When materialized views are created, a refresh interval is established to schedule refreshes of replicated data. Local updates can be prevented, and transaction-based refreshes can be used. Transaction-based refreshes, available for many types of materialized views, send from the master database only those rows that have changed for the materialized view. This capability, described later in this chapter, may significantly improve the performance of your refreshes.
Leave a Reply