To create a Materialized view with FAST REFRESH
Materialized views is used to replicate data from master(parent) to non-master sites in a replication environment. at non-master site it is replica of master site. so it is also known as 'snapshots' of master.
Need of Materialized views :-
Suppose I have two application, 1. for User Administration 2. actual user console. where based on user role user can perform different task. like Admin user can do administrative work, support user can monitor session, or see logs, and normal user can do all other things.
For Both this application I have different schema, from User console while logging into application, i have to check user role given in User Administration console. So I need data from other application and which need to be synchronized. we can do this by DB jobs but these operations are expensive in terms of time and processing power. As DB jobs mostly given on time scheduling. So if in given time nothing is updated in master, still it will copy data or take time to manipulate difference in master and child. BUT
'The contents of the materialized view gets updated when the underlying detail tables are modified.'
Example :-
- Master table for which you have to create Materialized views :-
CREATE table "USER_INFO" ("USER_ID" VARCHAR2(80) NOT NULL,
"USER_NAME" VARCHAR2(80),
"ROLE" VARCHAR2(80) NOT NULL,
constraint "USER_INFO_PK" primary key ("USER_ID")
);
- Create Materialized view log on master table.
CREATE MATERIALIZED VIEW LOG ON USER_INFO;
- Insert data into table.
insert into USER_INFO (USER_ID,USER_NAME,ROLE) values ('1','PIYUSH','ADMIN'),
insert into USER_INFO (USER_ID,USER_NAME,ROLE) values (2,'PIYUSH1','USER'),
insert into USER_INFO (USER_ID,USER_NAME,ROLE) values (3,'PIYUSH2','SUPPORT_USER');
- Read data from table
select * from USER_INFO;
- Create Materialized view of Master table.
CREATE MATERIALIZED VIEW MV_USER_INFO
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT U.* FROM USER_INFO U;
- Read the data from materialized view
select * from MV_USER_INFO;
Now onwards MV_USER_INFO (materialized view) gets updated when USER_INFO table detial is modified(updated).
- Try to update USER_INFO tables detail and read detail from Materialized view.
insert into USER_INFO (USER_ID,USER_NAME,ROLE) values ('4','PIYUSH3','USER');
select * from MV_USER_INFO;
Comments
Post a Comment