Materialized Views
Introduced with Oracle8i, a materialized view is designed to improve performance of the database by doing some intensive work in advance of the results of that work being needed.In the case of a materialized view, the data for the view is assembled when the view is created or refreshed. Later queries that need this data automatically use the materialized view, thus saving the overhead of performing the work already done by the view.The work avoided by a materialized view is essentially two fold:
A materialized view can be used to pre-collect aggregate values.
A materialized view can be used to assemble data that would come from may different tables,which would in turn require many different joins to be performed.
A materialized view is like a view in that it represents data that is contained in other database tables and views; yet it is unlike a view in that it contains actual data. A materialized view is like an index in that the data it contains is derived from the data in database tables and views; yet unlike an index in that its data must be explicity refreshed. Finally, a materialized view is very much like a snapshot in that an administrator can specify when the data is to be refreshed; but it is unlike a snapshot in that a materialized view should either include summary data or data from many different joined tables.
CREATE MATERIALIZED VIEW : - are required keywords
Name : It is qualified name of the materialized view
physical attributes clause : It allows you to specify the physical attributes, such the tablespace name,for the materialized view
BUILD clause : The BUILD clause allows you to specify when you want to build the actual data in the table. Your options are BUILD IMMEDIATE, which calls for the view to be immediately built, BUILD DEFERRED, which calls for the view to be built when it is first refreshed (see explanation of REFRESH clause below) or ON PREBUILT TABLE, which indicates that you are identifying a table that is already built as a materialized view.
REFRESH clause : Since the materialized view is built on underlying data that is periodically changed, you must specify how and when you want to refresh the data in the view. You can specify that you want a FAST refresh, which will only update the values in the materializaed view, assuming that some preconditions are met, COMPLETE, which recreated the view completely, or FORCE, which will do a FAST refresh if possible and a COMPLETE refresh if the preconditions for a FAST refresh are not available.
The REFRESH caluse also contains either the keywords ON COMMIT, which will cause a refresh to occur whenever the underlying data is changed and the change is committed, or ON DEMAND, which will only perform a refresh when it is scheduled or explicity called. You can also use keywords in the REFRESH clause to create a schedule for recurring refresh operations.
AS subquery : The last clause of the CREATE MATERIALIZED VIEW command contains the sub-query that will be used to retrieve the data that will compose the materialized view.
Oralce uses materialized views (also known as snapshots in prior releases) to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment.
You can use materialized views to achieve one or more of the following goals:
- Ease Network Loads
- Create a Mass Deployment Environment
- Enable Data Subsetting
- Enable Disconnected Computing
More Explanation
A materialized view is a dataase object that contains the results of a query. They are local copies of data loated remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.
A materialized view can query tables, views, and other materialized views. Collectively these are called master tabes ( a replication term) or detail tables ( a data warehouse term).
For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.
For data warehousing purposes, the materialized views commonly created are primary key, rowid, and subquery materialized views
Primary Key Materialized Views
The following statement creaes the primary-key materialized view on the table emp located on a remote database.
SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE+1/48
WITH PRIMARY KEY AS SELECT * FROM emp@remote_db;
Materialized view created.
Note: When you created a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.
Rowid Materialized Views
The following statement creates the rowid materalized view on table emp located on a remote database:
SQL> CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH WITH ROWID AS SELECT * FROM emp@remote_db;
Materialized view log created.
Subquery Materialized Views
The following statement creates a subquery materialized view based on the emp and dept tables located on the remote database:
SQL> CREATE MATERIALIZED VIEW mv_empdept AS SELECT * FROM emp@remote_db E
WHERE EXISTS (SELECT * FROM dept@remote_db d WHERE e.deptno=d.deptno)
Refresh Clause in Materialized Views
REFRESH CLAUSE
[refresh[fast|complete|force]
[on demand | commit]
[start with date] [next date]
[with {primary key|rowid}]]
The refresh option specifies:
The refresh method used by Oracle to refresh data in materialized view Whether the view is primary key based or row-id based The time and interval at whic h the view is to be refreshed
Refresh Method - FAST Clause
The FAST refreshes use the materialized view logs ( as seen above) to send the rows that have changed from master tables to the materialized view.
You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.
Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.
Refresh Method - COMPLETE Clause
The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.
Refresh Method-FORCE Clause
When you specify a FORCE caluse, oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST,COMPLETE,or FORCE),FORCE is the default.
PRIMARY KEY and ROWID Clause WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.
Primary key materialized views allow materialized view master tables to be reorganized without affacedting the eligibility of the materialized view for fast refresh.
Rowid materialized views should have a single master table and cannot contain any of the following:
Distinct or aggregate functions GROUP BY Subqueries, Joins & Set perations Timing the refresh
The START WITH caluse tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT Clause specifies the interval between refreshed
SQL> CREATE MATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE NEXT SYSDATE+2 WITH PRIMARY KEY AS SELECT * FROM emp@remote_db;
Materialized view created.
In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days.
Summary
Materialized Views thus offer us flexibility of basing a view on Primary key or ROWID,specifying refresh methods and specifying time of automatic refreshes.
Scenario for Using Multi-tier Materialized Views
Consider a multinational company that maintains all employee information at headquarters,which is in the in the United States. The company uses the tables in the hr schema to maintain the employee information. This company has one main office in 14 countries and many regional offices fro cities in these countries.
For example, the company has one main office for all of the United Kingdomw, but it also has an office in the city of London. The United Kingdom office maintains employee information for all of the employees in the United Kingdom, while the London office only maintains employee information for the employees at the London office. In this scenario, the hr.employees master table is at headquarters in the United States and each regional office has a an hr.employees materialized view that only contains the necessary employee information.
The following statement creates the hr.employees materialized view for the United Kingdom office. The statement queries the master table in the database at headquarters,which is orc1.world.Notice that the statement uses subqueries so that the materialized view only containes employees whose country_id is UK.
CREATE MATERIALIZED VIEW hr.employees REFRESH FAST FOR UPDATE AS SELECT * FROM hr.employees@orc1.world e WHERE EXISTS (SELECT * FROM hr.department@orc1.world d WHERE e.department_id=d.department_id AND EXISTS (SELECT * FROM hr.locations@orc1.world l WHERE l.country_id= 'UK' AND d.location_id=l.locations_id));
Note:
To create this hr.employees materialized view, the following columns must be logged:
The department_id column must be logged in the materialized view log for the hr.employees master table at orc1.world.
The country_id must be logged in the materialized view log for the hr.locations master table at orc1.world.
The following statement creates the hr.employees materialized view for the London office based on the level 1 materialized view at the United Kingdom office. The statement queries the materialized view in the database at the United Kingdom office, which is reg_uk.world. Notice that the statement uses subqueries so that the materialized view only contains employees whose city is London.
CREATE MATERIALIZED VIEW hr.employees REFRESH FAST FOR UPDATE AS
SELECT * FROM hr. employees@reg_uk.world e WHERE EXISTS (SELECT * FROM hr.departments@reg_uk.world d WHERE e.department_id=d.department_id AND EXISTS (SELECT * FROM hr.locations@reg_uk.world l WHERE l.city = 'London' AND d.location_id=l.location_id));
Note:
To create this hr.employees materialized view, the following columns must be logged:
The department_id column must be logged in the materialized view log for the hr.employees master materialized view at reg_uk.world.
The country_id must be logged in the materialized view log for the hr.locations master materialized view at reg_uk.world
No comments:
Post a Comment