Materialized Views
Materialized views in Oracle Oracle materialized views were first introduced in Oracle8.
Materialized views are schema objects that can be used to summarize, precompute, replicate and distribute data. In mview, the query result is cached as a concrete table that may be updated from the original base tables from time to time. his e nables much more efficient access, at the cost of some data being potentially out!of!date. out!of!date. It is most useful in datawarehousing datawarehousing scenarios, where frequent queries of the actual base tables can be e"tremely e"pensive. Oracle uses materialized views #also $nown as snapshots in snapshots in prior releases% to replicate data to non!master sites in a replication environment and to cache e"pensive queries in a datawarehouse datawarehouse environment. & materialized view is a database object that contains the results of a query. hey are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. & materialized view is a replica of a target master from a single point in time. (e can define a materialized view on a base)master table #at a master site%, partitioned partitioned table, table, view, synonym or a master materialized view #at a materialized view site%. (hereas in multi master replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, $nown as a refreshes, from a single master site or master materialized view site. & materialized view provides indirect access to table data by storing the results of a query in a separate schema object. *nli$e an ordinary view, which does not ta$e up any storage space or contain any data, Mview stores data, whereas view stores only query. he e"istence of a materialized view is transparent to +-, but when used for query rewrites will improve the performance of +- e"ecution. &n updatable materialized view lets you insert, update, and delete. (e can define inde"es on a materialized view. & materialized view can be stored in the same database as it's base table#s% or in a different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. uery rewrites are particularly useful in a datawarehouse datawarehouse environment. & materialized view can query tables, views and other materialized views. ollectively these are called master tables #a replication term% or detail tables #a datawarehouse term%. /or replication purposes, materialized views allow us to maintain copies of remote data on local node. hese copies are read!only. read!only. If we want to update the local copies, we have to use the &dvanced 0eplication feature. (e can select data from a materialized view as we would from a table or view.
/or datawarehousing purposes, mviews commonly created are aggregate views, single!table aggregate views and join views. In replication environments, mviews commonly created are primary $ey, rowid and subquery materialized views. (henever you create a materialized view, regardless of it's type, always specify the schema name of the table owner in the query for the materialized view.
Prerequisites: o create mviews, the user should have any one of 01&1 M&10I&-I213 4I1( or 01&1 &56 M&10I&-I213 4I1( privileges. +-7 0&5 01&1 M&10I&-I213 4I1( O user!name9 &nd +-7 0&5 *106 01(0I1 O user!name9 &nd following init parameters should be set query:rewrite:enabled ; true #default% query:rewrite:integrity ; enforced
Syntax: 01&1 M&10I&-I213 4I1( mview!name >partitioning!options? >storage!parameters? >&@-1+A&1 tablespace!name? >O/ object!type? >/O0 *A3&1? >@*I-3 IMM13I&1<@*I-3 31//101301/01+B >/&+<OMA-11O5 31M&53<OMMI? >+&0 (IB date? >51C date? >(IB A0IM&06 D16<0O(I3?? >3I+&@-1<15&@-1 *106 01(0I1? &+ select!query9
Refresh Types Oracle can refresh a materialized view using either a fast, complete or force refresh. he refresh option specifiesE a. 0efresh method used by Oracle to refresh data in materialized view. /O01 is the default option.
b. (hether the view is primary $ey based or row!id based. A0IM&06 D16 is the default option. c. ime and interval at which the view is to be refreshed.
Complete Refresh o perform OMA-11 refresh of a materialized view, the server that manages the materialized view e"ecutes the materialized view's defining query, which essentially recreates the materialized view. o refresh the materialized view, the result set of the query replaces the e"isting materialized view data. Oracle can perform a complete refresh for any materialized view. 3epending on the amount of data that satisfies the defining query, a complete refresh can ta$e a substantially longer amount of time to perform than a fast refresh. 5oteE If a materialized view is complete refreshed, then set it's A/011 to F and A*+13 to GG for ma"imum efficiency. he complete refresh re!creates the entire materialized view. If we request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible. +-7 01&1 M&10I&-I213 4I1( mv:emp 01/01+B OMA-11 +&0 (IB +6+3&1 51C +6+3&1 H (IB A0IM&06 D16 &+ +1-1 J /0OM empKremote:db9 o refresh this mview, +-7 1C1 3@M+:M4I1(.01/01+B#'mv:emp', ''%9 /rom Oracle Fg, complete refresh of single materialized view can do delete instead of truncate. o force the refresh to do truncate instead of delete, parameter &OMI:01/01+B must be set to false. &OMI:01/01+B ; /&-+1, mview will be truncated and whole data will be inserted. he refresh will go faster, and no undo will be generated. &OMI:01/01+B ; 0*1 #default%, mview will be deleted and whole data will be inserted. *ndo will be generated. (e will have access at all times even while it is being refreshed. +-7 1C1 3@M+:M4I1(.01/01+B#'mv:emp', '', atomic:refresh;7/&-+1%9 If we perform complete refresh of a master materialized view, then the ne"t refresh performed on any materialized views based on this master materialized view must be a complete refresh. If a fast refresh is attempted for such a materialized view after it's master materialized view has performed a complete refresh, then Oracle returns the following errorE ORA-12034 mview log is younger than last reresh
ast Refresh o perform /&+ refresh, the master that manages the materialized view first identifies the changes that occurred in the master since the most recent refresh of the materialized view and then applies these changes to the materialized view. /ast refreshes are more efficient than complete refreshes when there are few changes to the master because the participating server and networ$ replicate a smaller amount of data.
(e can perform fast refreshes of materialized views only when the master table or master materialized view has a materialized view log. &lso, for fast refreshes to be faster than complete refreshes, each join column in the 01&1 M&10I&-I213 4I1( statement must have an inde" on it. +-7 01&1 M&10I&-I213 4I1( mv:emp @*I-3 IMM13I&1 01/01+B /&+ +&0 (IB +6+3&1 51C +6+3&1 H L (IB A0IM&06 D16 15&@-1 *106 01(0I1 &+ +1-1 J /0OM empKremote:db9
materialized view lo!
& is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally. (e should create a materialized view log for the master tables if we specify the 01/01+B /&+ clause. +-7 01&1 M&10I&-I213 4I1( -O O5 emp9 o refresh this mview, +-7 1C1 3@M+:M4I1(.01/01+B#'mv:emp', '/'%9 &fter a direct path load on a master table or master materialized view using +-J-oader, a fast refresh does not apply the changes that occurred during the direct path load. &lso, fast refresh does not apply changes that result from other types of bul$ load operations on masters. 1"amples of these operations include some I5+10 statements with an &AA153 hint and some I5+10 ... +1-1 J /0OM statements. "ote: !77 /ast refreshable materialized views can be created based on master tables and master materialized views only. !77 Materialized views based on a synonym or a view must be complete refreshed. !77 Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.
orce Refresh o perform /O01 refresh of a materialized view, the server that manages the materialized view attempts to perform a fast refresh. If fast refresh is not possible, then Oracle performs complete refresh. *se the force setting when you want a materialized view to refresh if fast refresh is not possible. If you do not specify a refresh method, /O01 is the default. +-7 01&1 M&10I&-I213 4I1( mv:emp 01/01+B /O01 +&0 (IB +6+3&1 51C +6+3&1 H (IB A0IM&06 D16
3I+&@-1 *106 01(0I1 &+ +1-1 J /0OM empKremote:db9 o refresh this mview, +-7 1C1 3@M+:M4I1(.01/01+B#-I+ ;7'mv:emp', M1BO3 ;7'N'%9 #or% +-7 1C1 3@M+:M4I1(.01/01+B#-I+ ;7'mv:emp'%9
Partition Chan!e Trac#in! $PCT% Refresh In OracleGi, a new mechanism called Aartition hange rac$ing #A% has been introduced. his mechanism $eeps trac$ of the base table partitions that have been updated since the materialized view was last refreshed. It trac$s partition maintenance operations #AMOAs%, li$e add and truncate partition as well as 3M- changes to t he base data, on partition basis. his allows Oracle to identify fresh data in the materialized view. Aartition hange rac$ing #A% refresh refers to M4 refresh using only the changed partitions of the base tables of an M4. his refresh method is possible only if the base tables are partitioned and changes to base tables are trac$ed on a partition basis. o refresh these type of mviews #from Oracle Fg%, +-7 1C1 3@M+:M4I1(.01/01+B#'mv:emp', 'A'%9 /rom Oracle Gi, it was supporting, range and range!hash partitioning. /rom Oracle Fg, it was supporting, list partitioning also. 1nhanced Aartition hange rac$ing #1A% 0efresh refers to A based refresh applied to M4s containing columns that are partition!join dependent on the partitioning column of the base table.
Timin! the refresh he +&0 (IB clause 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. he 51C clause specifies the interval between refreshes +-7 01&1 M&10I&-I213 4I1( mv:emp:p$ 01/01+B /&+ +&0 (IB +6+3&1 51C +6+3&1 H L (IB A0IM&06 D16 &+ +1-1 J /0OM empKremote:db9 In the above e"ample, the first copy of t he materialized view is made at +6+3&1 #immediately% and the interval at which the refresh has to be performed is every two days. +-7 01&1 M&10I&-I213 4I1( mv:emp:p$ 01/01+B OMA-11 +&0 (IB +6+3&1 51C +6+3&1 H L)#LJPF% (IB 0O(I3 &+ +1-1 J /0OM empKremote:db9 In this e"ample, the interval is two minutes. /or every two minutes, fast refresh will happen.
+-7 01&1 M&10I&-I213 4I1( mv:emp:p$ 01/01+B /O01 +&0 (IB +6+3&1 51C +6+3&1 H F)#LJPFJPF% (IB A0IM&06 D16 &+ +1-1 J /0OM empKremote:db9 In this e"ample, the interval is F seconds. +-7 01&1 M&10I&-I213 4I1( mv:emp:f 01/01+B /&+ O5 OMMI (IB A0IM&06 D16 &+ +1-1 J /0OM empKremote:db9 +-7 01&1 M&10I&-I213 4I1( mv:emp:c 01/01+B OMA-11 O5 31M&53 (IB 0O(I3 &+ +1-1 J /0OM empKremote:db9 +-7 1C1*1 3@M+:M4I1(.01/01+B#'mv:emp:c',''%9 PR&M'R( )*( and RO+&, Clause (IB A0IM&06 D16 is used to create a primary $ey materialized view i.e. the materialized view is based on the primary $ey of the master table instead of 0O(I3. A0IM&06 D16 is the default option. o use the A0IM&06 D16 clause we should have defined A0IM&06 D16 on the master table or else you should use 0O(I3 based materialized views.
-ow to #now when was the last refresh happened on materialized views: +-7 select M4I1(:5&M1, to:char#-&+:01/01+B:3&1,'6666!MM!33 BBLEMIE++'% from dba:mviews9 #or% +-7 select M4I1(:5&M1, to:char#-&+:01/01+B:3&1,'6666!MM!33 BBLEMIE++'% from dba:mview:analysis9 #or% +-7 select 5&M1, to:char#-&+:01/01+B,'6666!MM!33 BBLEMIE++'% from dba:mview:refresh:times9
Read.Only/ 0pdata1le and +ritea1le Materialized 2iews & materialized view can be either read!only, updatable or writeable. *sers cannot perform data manipulation language #3M-% statements on read!only materialized views, but they can perform 3M- on updatable and writeable materialized views.
•
. 0ead only annot be updated and comple" materialized views are supported.
•
L. *pdateable an be updated even when disconnected from the master site.
•
&re refreshed on demand.
•
onsumes fewer resources.
•
•
0equires &dvanced 0eplication option to be installed. . (riteable reated with the for update clause.
•
hanges are lost when view is refreshed.
•
0equires &dvanced 0eplication option to be installed. 5oteE /or read!only, updatable, and writeable materialized views, the defining query of the materialized view must reference all of the primary $ey columns in the master.
Read.Only Materialized 2iews (e can ma$e a materialized view read!only during creation by omitting the /O0 *A3&1 clause or disabling the equivalent option in the 0eplication Management tool. 0ead!only materialized views use many of the same mechanisms as updatable materialized views, e"cept that they do not need to belong to a materialized view group. In addition, using read!only materialized views eliminates the possibility of a materialized view introducing data conflicts at the master site or master materialized view site, although this convenience means that updates cannot be made at the remote materialized view site. he following is an e"ample of a read!only materialized viewE +-7 01&1 M&10I&-I213 4I1( hr.emp &+ +1-1 J /0OM hr.empKorcl9
0pdata1le Materialized 2iews (e can ma$e a materialized view updatable during creation by including the /O0 *A3&1 clause or enabling the equivalent option in the 0eplication Management tool. /or changes made to an updatable materialized view to be pushed bac$ to the master during refresh, the updatable materialized view must belong to a materialized view group. *pdatable materialized views enable us to decrease the load on master sites because users can ma$e changes to the data at the materialized view site. he following is an e"ample of an updatable materialized viewE +-7 01&1 M&10I&-I213 4I1( hr.dept /O0 *A3&1 &+ +1-1 J /0OM hr.departmentsKorcl9 he following statement creates a materialized view groupE @1I5 3@M+:01A&.01&1:M4I1(:01A0O*A # gname ;7 'hr:repg', master ;7 'orcl', propagation:mode ;7 '&+65B0O5O*+'%9 1539 ) he following statement adds the hr.departments materialized view to the materialized view group, ma$ing the materialized view updatableE @1I5 3@M+:01A&.01&1:M4I1(:01AO@=1 # gname ;7 'hr:repg', sname ;7 'hr',
oname ;7 'departments', type ;7 '+5&A+BO', min:communication ;7 0*1%9 1539 ) (e can also use the 0eplication Management tool to create a materialized view group and add a materialized view to it. 5oteE 3o not use column aliases when we are creating an updatable materialized view. olumn aliases cause an error when we attempt to add the materialized view to a materialized view group using the 01&1:M4I1(:01AO@=1 procedure. 1.
&n updatable materialized view based on a master table or master materialized view that has defined column default values does not automatically use the master's default values. 2.
3.
•
*pdatable materialized views do not support the 31-11 &+&31 constraint.
he following types of materialized views cannot be masters for updatable materialized viewsE 0O(I3 materialized views
•
omple" materialized views
•
0ead!only materialized views Bowever, these types of materialized views can be masters for read!only materialized views.
•
•
•
•
&dditional 0estrictions for *pdatable Materialized 4iews @ased on Materialized 4iews, those mustE @elong to a materialized view group that has the same name as the materialized view group at it's master materialized view site. 0eside in a different database than the materialized view group at it's master materialized view site. @e based on another updatable materialized view or other updatable materialized views, not on a read!only materialized view. @e based on a materialized view in a materialized view group that is owned by A*@-I at the master materialized view site.
+ritea1le Materialized 2iews A writeable materiali!ed view is one that is created using the "OR #$%A&' clause but is not (art o a materiali!ed view grou(. *sers can perform 3M- operations on a writeable materialized view, but if we refresh the materialized view, then these changes are not pushed bac$ to the master and the changes are lost in the materialized view itself. (riteable materialized views are typically allowed wherever fast!refreshable read!only materialized views are allowed. 5oteE writeable materialized views are rarely used.
Materialized 2iews Types
0ses of Materialized 2iews •
(e can use materialized views to achieve one or more of the following goalsE -ess networ$ loads
•
reate mass deployment environment
•
1nable data subsetting
•
1nable disconnected computing 3ess "etwor# 3oads (e can use materialized views to reduce networ$ loads Q to distribute your corporate database to regional sites. Instead of the entire company accessing a single database server, user load is distributed across multiple database servers. hrough the use of multitier materialized views, we can create materialized views based on other materialized views, which enables us to distribute user load to an even greater e"tent because clients can access materialized view sites instead of master sites. o decrease the amount of data that is replicated, a materialized view can be a subset of a master table or master materialized view. (hile multimaster replication also distributes a database among multiple sites, the networ$ing requirements for multimaster replication are greater than those for replicating with materialized views because of the transaction by transaction nature of multimaster replication. /urther, the ability of multimaster replication to provide real!time or near real! time replication may result in greater networ$ traffic, and might require a dedicated networ$ lin$. Materialized views are updated through an efficient batch process from a single master site or master materialized view site. hey have lower networ$ requirements and dependencies than multimaster replication because of the point in time nature of materialized view replication. (hereas multimaster replication requires constant communication over the networ$, materialized view replication requires only periodic refreshes. In addition to not requiring a dedicated networ$ connection, replicating data with materialized views increases data availability by providing local access to the target data. hese benefits, combined with mass deployment and data subsetting #both of which also reduce networ$ loads%, greatly enhance the performance and reliability of your replicated database. Create mass deployment environment 3eployment templates allow us to precreate a materialized view environment locally. (e can then use deployment templates to quic$ly and easily deploy materialized view environments to support sales force automation and other mass deployment environments. Aarameters allow us to create custom data sets for individual users without changing the deployment template. his technology enables you to roll out a database infrastructure to hundreds or thousands of users. *na1le data su1settin! Materialized views allow us to replicate data based on column and row!level subsetting, while multimaster replication requires replication of the entire table. 3ata subsetting enables us to replicate information that pertains only to a particular site. /or e"ample, if we have a regional sales office, then we might replicate only the data that is needed in that region, thereby cutting down on unnecessary networ$ traffic.
*na1le disconnected computin! Materialized views do not require a dedicated networ$ connection. hough we have the option of automating the refresh process by scheduling a job, we can manually refresh your materialized view on!demand, which is an ideal solution for sales applications running on a laptop. /or e"ample, a developer can integrate the replication management &AI for refresh on!demand into the sales application. (hen the salesperson has completed the d ay's orders, the salesperson simply dials up the networ$ and uses the integrated mechanism to refresh the database, thus transferring the orders to the main office. 5oteE •
•
•
@oth the master site and the materialized view site must have compatibility level #OMA&I@-1 initialization parameter% G.F. or higher to replicate user!defined types and any objects on which they are based. (e cannot create refresh!on!commit materialized views based on a master with user!defined types. 0efresh!on!commit materialized views are those created using the O5 OMMI 01/01+B clause in the 01&1 M&10I&-I213 4I1( statement. &dvanced 0eplication does not support type inheritance.
Materialized 2iew 3o! 0pdata1le Materialized 2iew 3o! Materialized 2iew 4roups & materialized view group in a replication system maintains a partial or complete copy of the objects at the target replication group at it's master site or master materialized view site. Materialized view groups cannot span the boundaries of the replication group at the master site or master materialized view site. roup & at the materialized view site contains only some of the objects in the corresponding roup & at the master site. roup @ at the materialized view site contains all objects in roup @ at the master site. *nder no circumstances, however, could roup @ at the materialized view site contain objects from roup & at the master site. & materialized view group has the same name as the master group on which the materialized view group is based. /or e"ample, a materialized view group based on a personnel master group is also named personnel. In addition to maintaining organizational consistency between materialized view sites and their master sites or master materialized view sites, materialized view groups are required for supporting updatable materialized views. If a materialized view does not belong to a materialized view group, then it must be a read!only or writeable materialized view.
Refresh 4roups Managing M4s is much easier in Oracle Fg with the introduction of the powerful new tuning advisors that can tell us a lot about the design of the M4s. uning recommendations that can generate a complete script that can be implemented quic$ly, saving significant time and effort. he ability to force rewriting or abort the query can be very helpful in decision!
support systems where resources must be conserved, and where a query that is not rewritten should not be allowed to run amuc$ inside the database.
Related 2iews 3@&:M4I1(+ 3@&:M4I1(:-O+ 3@&:M4I1(:D16+ 3@&:01I+1013:M4I1(+ 3@&:01I+1013:M4I1(:0O*A+ 3@&:M4I1(:01/01+B:IM1+ 3@&:M4I1(:&5&-6+I+
•
Related Pac#a!e5Procedures 3@M+:M4I1( pac$age 01/01+B
•
01/01+B:&--
•
01/01+B:&--:M4I1(+
•
01/01+B:31A15315
•
01I+10:M4I1(
•
*501I+10:M4I1(
•
A*01:-O 3@M+:01A& pac$age 3@M+:01/01+B pac$age
