Advance Replication [Part-II Master Replication]

Part-II Master Replication

After Reading and understand Part-I. Now understand how to setup multimaster replication enviourment.

What is Master Replication?
Oracle has two types of master replication: single master replication and multimaster replication.
Note: Here I explain only Multimaster replication.

1. Multimaster replication includes multiple master sites, where each master site operates as an equal peer.
2. Multimaster replication, also known as peer-to-peer or n-way replication, is comprised of multiple master sites equally participating in an update-anywhere model.
3. Oracle database servers operating as master sites in a multimaster replication environment automatically work to converge the data of all table replicas, and ensure global transaction consistency and data integrity.
4. Conflict resolution is independently handled at each of the master sites.
5. Multimaster replication provides complete replicas of each replicated table at each of the master sites.



Why Use Multimaster Replication?
From a very basic point of view, replication is used to make sure that data is available when and where you need it.
But some of possibilities when we use multimaster replication are
Failover
Multimaster replication can be used to protect the availability of a mission critical database. For example, a multimaster replication environment can replicate data in your database to establish a failover site should the primary site become unavailable due to system or network outages. Such a failover site also can serve as a fully functional database to support application access when the primary site is concurrently operational.
Load Balancing
Multimaster replication is useful for transaction processing applications that require multiple points of access to database information for the following purposes:
1. Distributing a heavy application load
2. Ensuring continuous availability
3. Providing more localized data access

Multimaster Replication Process
There are two types of multimaster replication: asynchronous and synchronous.
asynchronous
Asynchronous replication, often referred to as store-and-forward replication, captures any local changes, stores them in a queue, and, at regular intervals, propagates and applies these changes at remote sites. With this form of replication, there is a period of time before all sites achieve data convergence.
synchronous
Synchronous replication, also known as real-time replication, applies any changes or executes any replicated procedures at all sites participating in the replication environment as part of a single transaction. If the data manipulation language (DML) statement or procedure fails at any site, then the entire transaction rolls back. Synchronous replication ensures data consistency at all sites in real-time.

Note: You can change the propagation mode from asynchronous to synchronous or vice versa for a master site.

How to setup Master sites?

Set below parameters
global_names
alter system set global_names=TRUE;

job_queue_processes
alter system set job_queue_processes = 1;

Considerations for Replicated Tables
Primary Keys
If possible, each replicated table should have a primary key. Where a primary key is not possible, each replicated table must have a set of columns that can be used as a unique identifier for each row of the table.
Foreign Keys
When replicating tables with foreign key referential constraints, Oracle Corporation recommends that you always index foreign key columns and replicate these indexes, unless no updates and deletes are allowed in the parent table. Indexes are not replicated automatically. To replicate an index, add it to the master group containing its table using either the Replication Management tool or the CREATE_MASTER_REPOBJECT procedure in the DBMS_REPCAT package.

Create DB for replication
Here I created three databases for replication
db01
db02

Setting up DB01 master site

Step 1 Connect as SYSTEM at a master site at DB01
SQL> conn system/oracle@db01
Connected.

Step 2 Create the replication administrator at DB01
SQL> CREATE USER repadmin IDENTIFIED BY repadmin;

User created.

Step 3 Grant privileges to the replication administrator at DB01
SQL> BEGIN
2 DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
3 username => 'repadmin');
4 END;
5 /

PL/SQL procedure successfully completed.

Note: This privilege to create and manage a replicated enviourments

SQL> GRANT SELECT ANY DICTIONARY TO repadmin;

Grant succeeded.

Note: If you want your repadmin to be able to connect to the Replication Management tool,then grant SELECT ANY DICTIONARY to repadmin:

Step 4 Register the propagator at DB01
SQL> BEGIN
2 DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
3 username => 'repadmin');
4 END;
5 /

PL/SQL procedure successfully completed.

Note: The propagator is responsible for propagating the deferred transaction queue to other master sites.

Step 5 Register the receiver at DB01
SQL> BEGIN
2 DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
3 username => 'repadmin',
4 privilege_type => 'receiver',
5 list_of_gnames => NULL);
6 END;
7 /

PL/SQL procedure successfully completed.

Note: The receiver receives the propagated deferred transactions sent by the propagator from other master sites.
Step 6 Schedule purge at master site at DB01
SQL> CONNECT repadmin/repadmin@db01
Connected.
SQL>
SQL> BEGIN
2 DBMS_DEFER_SYS.SCHEDULE_PURGE (
3 next_date => SYSDATE,
4 interval => 'SYSDATE + 1/24',
5 delay_seconds => 0);
6 END;
7 /

PL/SQL procedure successfully completed.

Note: In order to keep the size of the deferred transaction queue in check, you should purge successfully completed deferred transactions. The SCHEDULE_PURGE procedure automates the purge process for you. You must execute this procedure as the replication administrator.
Note: Repeat above 6 steps in database DB02 master sites.

Step-2 Create Scheduled Links between the Master Sites

Step 1 Create database links between master sites.
The database links provide the necessary distributed mechanisms to allow the different replication sites to replicate data among themselves. Before you create any private database links, you must create the public database links that each private database link will use. You then must create a database link between all replication administrators at each of the master sites that you have set up.
SQL> conn system/oracle@db01
Connected.
SQL> create public database link db02 using 'db02';

Database link created.

SQL> conn repadmin/repadmin@db01
Connected.
SQL> create database link db02 connect to repadmin identified by repadmin;

Database link created.

SQL> conn system/oracle@db02
Connected.
SQL> create public database link db01 using 'db01';

Database link created.

SQL> conn repadmin/repadmin@db02
Connected.
SQL> create database link db01 connect to repadmin identified by repadmin;

Database link created.

Step 2 Define a schedule for each database link to create scheduled links.
Create a scheduled link by defining a database link when you execute the SCHEDULE_PUSH procedure. The scheduled link determines how often your deferred transaction queue is propagated to each of the other master sites. You need to execute the SCHEDULE_PUSH procedure for each database link that you created in Step 1. The database link is specified in the destination parameter of the SCHEDULE_PUSH procedure.
Even when using Oracle's asynchronous replication mechanisms, you can configure a scheduled link to simulate continuous, real-time replication. The scheduled links in this example simulate continuous replication.
SQL> conn repadmin/repadmin@db01
Connected.
SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_DEFER_SYS.SCHEDULE_PUSH (
3 destination => 'DB02',
4 interval => 'SYSDATE + (1/144)',
5 next_date => SYSDATE,
6 parallelism => 1,
7 execution_seconds => 1500,
8 delay_seconds => 1200);
9* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> conn repadmin/repadmin@db02
Connected.
SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_DEFER_SYS.SCHEDULE_PUSH (
3 destination => 'DB01',
4 interval => 'SYSDATE + (1/144)',
5 next_date => SYSDATE,
6 parallelism => 1,
7 execution_seconds => 1500,
8 delay_seconds => 1200);
9* END;
SQL> /

PL/SQL procedure successfully completed.

Step-3 Create Master Group

Here I am using SCOTT schema and their objects for replicate purpose because SCOTT schema exists in Default Oracle installation.
Objects which use for replication in scott schema.
1. DEPT - table
2. EMP - table
Primary key must exists on each table which use for replication.
Note: If DB version is 10g then first unlock scott account;

Step-1 Unlock Scott Schema and reset schema password or Create New Schema for replication.
Step 2 Create the master group
SQL> conn repadmin/repadmin@db01
Connected.
SQL> begin
2 dbms_repcat.create_master_repgroup (
3 gname => 'scott_repg');
4 end;
5 /

PL/SQL procedure successfully completed.
Note: This step must be completed by the replication administrator.

Step 3 Add objects to master group.
SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'scott_repg',
4 type => 'TABLE',
5 oname => 'EMP',
6 sname => 'scott',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'scott_repg',
4 type => 'TABLE',
5 oname => 'DEPT',
6 sname => 'scott',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9* END;
SQL> /

PL/SQL procedure successfully completed.

Note: Use the CREATE_MASTER_REPOBJECT procedure to add an object to your master group. In most cases, you probably will be adding tables and indexes to your master group, but you can also add procedures, views, synonyms, and so on.
Step 4 Add additional master sites.
SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_REPCAT.ADD_MASTER_DATABASE (
3 gname => 'scott_repg',
4 master => 'DB02',
5 use_existing_objects => TRUE,
6 copy_rows => FALSE,
7 propagation_mode => 'ASYNCHRONOUS');
8* END;
SQL> /

PL/SQL procedure successfully completed.
Note: You should wait until db02 appears in the DBA_REPSITES view before continuing. Execute the following SELECT statement in another SQL*Plus session to make sure that db02 has appeared:
SQL> conn repadmin/repadmin@db01
Connected.
SQL> SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = 'SCOTT_REPG';
DBLINK
------------------------------------------------------------------
DB01
DB02
Note:
1. In this example, the use_existing_objects parameter in the ADD_MASTER_DATABASE procedure is set to TRUE because it is assumed that the scott schema already exists at all master sites. In other words, it is assumed that the objects in the scott schema are precreated at all master sites. Also, the copy_rows parameter is set to FALSE because it is assumed that the identical data is stored in the tables at each master site.
2. Here I am using replication Process is “ASYNCHRONOUS”.
3. Here I am skip “conflict resolution methods”
Note: Repeat Step-3 at DB02 master site.
Step 5 Generate replication support.
SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'scott',
4 oname => 'emp',
5 type => 'TABLE',
6 min_communication => TRUE);
7* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'scott',
4 oname => 'dept',
5 type => 'TABLE',
6 min_communication => TRUE);
7* END;
SQL> /

PL/SQL procedure successfully completed.
Note: You should wait until the DBA_REPCATLOG view is empty before resuming master activity. Execute the following SELECT statement to monitor your DBA_REPCATLOG view:

SQL> SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = 'SCOTT_REPG';

COUNT(*)
----------
0
Step-6 Start replication.
After creating your master group, adding replication objects, generating replication support, and adding additional master databases, you need to start replication activity. Use the RESUME_MASTER_ACTIVITY procedure to "turn on" replication for the specified master group.
SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
3 gname => 'scott_repg');
4* END;
SQL> /
PL/SQL procedure successfully completed.
Here we complete multimaster replication procedure.
Now let check what we did is correct or not :)
Connect with scott to db01 and insert new rows in dept table and commit;
SQL> conn scott/tiger@db01
Connected.
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> insert into dept values (50,'DUMMY','DUMMY');

1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 DUMMY DUMMY--- New row

--Wait some time ...and connect scott to db02 database and check that new row replicate in dept table.
SQL> conn scott/tiger@db02
Connected.
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 DUMMY DUMMY --- New row
Note: you can perform any DML operation on Master Definition site “db01” and commit. Changes will update in DB02 site and if you perform any changes in DB02 db then update will changes in DB01 vice versa.
SQL> conn scott/tiger@db02
Connected.
SQL> select count(*) from dept;

COUNT(*)
----------
5
--Delete One row in dept table “db02”.
SQL> delete dept where dname = 'DUMMY';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from dept;

COUNT(*)
----------
4

SQL> disconn
SQL> conn scott/tiger@db01
Connected.
SQL> select count(*) from dept;

COUNT(*)
----------
4

Altering a Replicated Object

Step 1 Connect to the master definition site as the replication administrator.

SQL> conn repadmin/repadmin@db01
Connected.
Step 2 If necessary, then quiesce the master group.
SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
3 gname => 'scott_repg');
4* END;
5 /

PL/SQL procedure successfully completed.
Step 3 In a separate SQL*Plus session, check the status of the master group you are quiescing.
Do not proceed until the group's status is QUIESCED.
SQL> SELECT GNAME, STATUS FROM DBA_REPGROUP;

GNAME STATUS
------------------------------ ---------
SCOTT_REPG QUIESCING
Step 4 Alter the replicated object.
SQL> ED
Wrote file afiedt.buf

1 BEGIN
2 DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
3 sname => 'SCOTT',
4 oname => 'dept',
5 type => 'TABLE',
6 ddl_text => 'ALTER TABLE SCOTT.DEPT ADD (timestamp DATE)');
7* END;
SQL> /

PL/SQL procedure successfully completed.
Note: Add Timestamp column in scott.dept@db01 table
Step 5 Regenerate replication support for the altered object.
SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'scott',
4 oname => 'dept',
5 type => 'TABLE',
6 min_communication => TRUE);
7* END;
8 /

PL/SQL procedure successfully completed.
Step 6 In a separate SQL*Plus session, check if DBA_REPCATLOG is empty.
SQL> select count(*) from dba_repcatlog;

COUNT(*)
----------
0
Note: Do not proceed until this view is empty.
Step 7 Resume replication activity.
SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
3 gname => 'scott_repg');
4* END;
SQL> /

PL/SQL procedure successfully completed.
Now check dept table in both database at “db01” or “db02”.
SQL> conn scott/tiger@db01
Connected.
SQL> desc dept
Name Null? Type
----------------------------------------- -------- -------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
TIMESTAMP DATE

SQL> conn scott/tiger@db02
Connected.
SQL> desc dept
Name Null? Type
----------------------------------------- -------- -------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
TIMESTAMP DATE

Listing the Master Sites Participating in a Master Group

Here I created two DB, one is master definition site and other is Master site.
Through below query we can fintout which one is master definition site or which is master site.
SQL> conn repadmin/repadmin@db01
Connected.
SQL> COLUMN GNAME HEADING 'Master Group' FORMAT A20
SQL> COLUMN DBLINK HEADING 'Sites' FORMAT A25
SQL> COLUMN MASTERDEF HEADING 'Master|Definition|Site?' FORMAT A10
SQL> SELECT GNAME, DBLINK, MASTERDEF
2 FROM DBA_REPSITES
3 WHERE MASTER = 'Y'
4 AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = 'Y'
)
5 ORDER BY GNAME;
Master
Definition
Master Group Sites Site?
-------------------- ------------------------- ----------
SCOTT_REPG DB02 N
SCOTT_REPG DB01 Y
Note: DB02 is master site and DB01 is master definition site.

Advance Replication [Part-I Introduction]

Dear Friends,

We can divide advance replication in three parts for better understanding.

1. Part-I Introduction
2. Part-II Multimaster Replication
3. Part-III Materialized view replication

Part-I Introduction

What is Advance Replication?
Replication is the process of copying and maintaining database objects, such as tables, in multiple databases that make up a distributed database system. Changes applied at one site are captured and stored locally before being forwarded and applied at each of the remote locations. Advanced Replication is a fully integrated feature of the Oracle server; it is not a separate server.

Replication uses distributed database technology to share data between multiple sites, but a replicated database and a distributed database are not the same. In a distributed database, data is available at many locations, but a particular table resides at only one location. For example, the employees table resides at only the ny.world database in a distributed database system that also includes the hk.world and la.world databases. Replication means that the same data is available at multiple locations. For example, the employees table is available at ny.world, hk.world, and la.world.

Some of the common reasons for using replication are:

Availability
Replication improves the availability of applications because it provides them with alternative data access options. If one site becomes unavailable, then users can continue to query or even update the remaining locations. In other words, replication provides excellent failover protection.
Performance
Replication provides fast, local access to shared data because it balances activity over multiple sites. Some users can access one server while other users access different servers, thereby reducing the load at all servers. Also, users can access data from the replication site that has the lowest access cost, which is typically the site that is geographically closest to them.
Disconnected computing
A materialized view is a complete or partial copy (replica) of a target table from a single point in time. Materialized views enable users to work on a subset of a database while disconnected from the central database server. Later, when a connection is established, users can synchronize (refresh) materialized views on demand. When users refresh materialized views, they update the central database with all of their changes, and they receive any changes that may have happened while they were disconnected.
Network load reduction
Replication can be used to distribute data over multiple regional locations. Then, applications can access various regional servers instead of accessing one central server. This configuration can reduce network load dramatically.

Architecture of advance replication


There are two types of Advance Replication

1. Multimaster Replication
2. Materialized view replication

1. Multimaster replication
Start --- > set up master site --- > Select object for replication --- > Create Group for selected objects --- > if conflict possible then configure conflict resolution ---> end

2. Materialized view replication
Start --- > set up master site -- > set up materialized view site --- > create materialized view group --- > end

Note: In Detail explanation is Part-II, III.

Replication Sites
A replication group can exist at multiple replication sites. Replication environments support two basic types of sites: master sites and materialized view sites. One site can be both a master site for one replication group and a materialized view site for a different replication group. However, one site cannot be both the master site and the materialized view site for the same replication group.

The differences between master sites and materialized view sites are the following:
Replication Group
1. A replication group at a master site is more specifically referred to as a master group.
2. A replication group at a materialized view site is based on a master group and is more specifically referred to as a materialized view group.

Replication Objects
1. A master site maintains a complete copy of all objects in a replication group,
Example: if the hr_repg master group contains the table’s employees and departments, then all of the master sites participating in a master group must maintain a complete copy of employees and departments.

2. Materialized views at a materialized view site can contain all or a subset of the table data within a master group.
Example: one materialized view site might contain only a materialized view of the employees table, while another materialized view site might contain materialized views of both the employees and departments tables.

Communication with master sites
1. All master sites in a multimaster replication environment communicate directly with one another to continually propagate data changes in the replication group
2. Materialized view sites contain an image, or materialized view, of the table data from a certain point in time.

Replication Objects
A replication object is a database object existing on multiple servers in a distributed database system. In a replication environment, any updates made to a replication object at one site are applied to the copies at all other sites. Advanced Replication enables you to replicate the following types of objects:
• Tables
• Indexes
• Views and Object Views
• Packages and Package Bodies
• Procedures and Functions
• User-Defined Types and Type Bodies
• Triggers
• Synonyms
• Index types
• User-Defined Operators

Replication Groups
In a replication environment, Oracle manages replication objects using replication groups. A replication group is a collection of replication objects that are logically related.
By organizing related database objects within a replication group, it is easier to administer many objects together. Typically, you create and use a replication group to organize the schema objects necessary to support a particular database application. However, replication groups and schemas do not need to correspond with one another. A replication group can contain objects from multiple schemas, and a single schema can have objects in multiple replication groups. However, each replication object can be a member of only one replication group.


Part-II

Materialized view for Data Warehouse

Dear friends,

Did you hear about Materialized view?
Ans: Yes, No or May be.

Now I will explain you in details

What is materialized view?

1. Materialized view is normal database object like “table,index”
2. It is basically use for Data warehouse or Replication purpose.
3. Snapshot is synonym for materialized view.
4. A materialized view can be stored in the same database as its base tables or in a different database
5. A materialized view provides access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views.
6. A materialized view improve response time through query rewrite or reduce execution time.

Note: Query rewrite is a mechanism where Oracle or applications from the end user or database transparently improve query response time, by automatically rewriting the SQL query to use the materialized view instead of accessing the original tables. Query rewrites are particularly useful in a data warehouse environment.

What is syntax for materialized view?

Create materialized view “materialized_view_name”
Build [immediate / deferred]
Refresh [fash [on commit / demand]]
Enable query rewrite
As
Sql statement
/

Now Details explanation about syntax
1. Create materialized view “name’
I hope it is understood because self explanatory

2. Build [immediate / deferred]
1. Build immediate -- > materialized view populate with data on creation time from tables.
2. Build deferred --- > materialized view not populate with data on creation time, we have to populate later.

Eg:
With build immediate


create materialized view mymview1
build immediate
as
select ename,sum(sal) from emp group by ename;

SQL> select count(*) from emp;

COUNT(*)
----------
14

Note: materialized view populate with data on creation time.

With build deferred
create materialized view mymview2
build deferred
as
select ename,sum(sal) from emp group by ename;

SQL> select count(*) from mymview2;

COUNT(*)
----------
0

Note: materialized view not populate with data on creation time, we have to populate manually through DBMS_MVIEW.RERESH package.


3. Refresh [fast [on commit / demand]]
Refresh is method for populate materialized view with data whenever made changes in their base tables.

There are five refresh method for materialized view
1. refresh fast (materialized view log needed)
2. refresh on commit
3. refresh on demand

What is materialized view log?
A materialized view log 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.
Each materialized view log is associated with a single master table. The materialized view log resides in the same database and schema as its master table.

Eg:
With refresh on commit

SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> create materialized view MYVIEW1
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select ename, sum(sal) from emp group by ename;
Materialized view created.

SQL> select count(*) from myview1;
COUNT(*)
----------
14
SQL> insert into emp (empno,ename,sal) values(1754,'TAJ',2500);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from myview1;
COUNT(*)
----------
15
With refresh on commit
SQL> select count(*) from emp;

COUNT(*)
----------
15

SQL> create materialized view MYVIEW5
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select ename,sum(sal) from emp group by ename;

Materialized view created.

SQL> select count(*) from myview5;

COUNT(*)
----------
15

SQL> insert into emp (empno,ename,sal)values(1100,'xyz',1000);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

COUNT(*)
----------
16

SQL> select count(*) from myview5;

COUNT(*)
----------
15

SQL> exec dbms_mview.refresh('MYVIEW5');

PL/SQL procedure successfully completed.

SQL> select count(*) from myview5;

COUNT(*)
----------
16

Whenever any changes made in base tables and perform commit then materialized view refreshed.

4. Enable query rewrite
A materialized view is like a query with a result that is materialized and stored in a table. When a user query is found compatible with the query associated with a materialized view, the user query can be rewritten in terms of the materialized view. This technique improves the execution of the user query, because most of the query result has been precomputed. The query transformer looks for any materialized views that are compatible with the user query and selects one or more materialized views to rewrite the user query. The use of materialized views to rewrite a query is cost-based. That is, the query is not rewritten if the plan generated without the materialized views has a lower cost than the plan generated with the materialized views.
Note: query_rewrite_enabled=true AND query_rewrite_integrity=enforced must be set to use query rewrite feature

5. How Materialized view improve query performance.

Let’s check with demo

1. Create Big Table :)
SQL> create table big
2 as select * from all_objects
3 union all
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 union all
10 select * from all_objects;

Table created.

SQL> select count(*) from big;

COUNT(*)
----------
188995

2. Now execute below query and check query statistics
SQL> alter system flush shared_pool;

System altered.

SQL> set autotrace traceonly
SQL> set timing on
SQL> select owner,count(*) from big group by owner;

12 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=624 Card=188024 By
tes=3196408)

1 0 SORT (GROUP BY) (Cost=624 Card=188024 Bytes=3196408)
2 1 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=598 Card=1880
24 Bytes=3196408)





Statistics
----------------------------------------------------------
957 recursive calls
0 db block gets
2844 consistent gets---- > too big :)
0 physical reads
0 redo size
1028 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
12 rows processed

3. Now Create materialized view and try
SQL> create materialized view MYVIEWBIG
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select owner,count(*) from big group by owner;

Materialized view created.

SQL> alter system flush shared_pool;

System altered.

SQL> set autotrace traceonly
SQL> set timing on
SQL> select owner,count(*) from big group by owner;

12 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=12 Bytes=36
0)

1 0 MAT_VIEW REWRITE ACCESS (FULL) OF 'MYVIEWBIG' (MAT_VIEW RE
WRITE) (Cost=3 Card=12 Bytes=360)





Statistics
----------------------------------------------------------
7356 recursive calls
0 db block gets
1313 consistent gets --- > just less then half :)
0 physical reads
0 redo size
1028 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
49 sorts (memory)
0 sorts (disk)
12 rows processed


4. What Privileges Need for materialized view ?

To create a materialized view in your own schema, you must have the CREATE MATERIALIZED VIEW privilege and the SELECT privilege to any tables referenced that are in another schema. To create a materialized view in another schema, you must have the CREATE ANY MATERIALIZED VIEW privilege and the owner of the materialized view needs SELECT privileges to the tables referenced if they are from another schema. Moreover, if you enable query rewrite on a materialized view that references tables outside your schema, you must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table outside your schema.

5. How to check Status of a Materialized View?

SQL> select mview_name,query,rewrite_enabled,refresh_mode,build_mode,staleness,c
ompile_state
2 from user_mviews;

MVIEW_NAME QUERY R REFRES BUILD_MOD STALENESS COMPILE_ST
---------- -------------------- - ------ --------- ---------- ----------

MYBIG select owner, count( Y DEMAND IMMEDIATE NEEDS_COMP NEEDS_COMP
*) from big group by ILE ILE
owner

MYMVIEW2 select ename,sum(sal N DEMAND DEFERRED UNUSABLE NEEDS_COMP
) from emp group by ILE
ename

MYVIEW5 select ename,sum(sal Y DEMAND IMMEDIATE FRESH VALID
) from emp group by
ename

MYMVIEW3 select ename,sum(sal Y COMMIT IMMEDIATE UNUSABLE VALID
) from emp group by
ename

MYVIEW1 select ename, sum(sa Y COMMIT IMMEDIATE FRESH VALID
l) from emp group by

Explanation:
MVIEW_NAME -------- > Materialized View name
QUERY ------------- > Query that defines the materialized view
REFRESH_ENABLED --- > If query_rewrite_enabled=TRUE then value is “Y” otherwise “N”
REFRESH_MODE ------ > DEMAND, COMMIT, NEVER
BUILD_MODE -------- > IMMEDIATE, DEFERRED
STALENESS --------- > Relationship between the contents of the materialized view and the contents of the materialized view's masters.
COMPILE_STATE ----- > Validity of the materialized view with respect to the objects upon which it depends
VALID -> No objects depends has changed since the last validation
NEED_COMPILE -> Some objects depends materialized view has changed an “alter materialized view … compile;” statement is required to validate this materialized view