Step 1: Create Materialized View Log with Rowid on Each Table
create materialized view log on emp with rowid;
create materialized view log on dept with rowid;
Step 2: Create Materialized View with rowids for all the base tables
create materialized view empdept_mv
refresh fast on commit as
select
e.rowid e_rowid,
d.rowid d_rowid,
e.empno,
e.ename,
d.deptno,
d.dname,
from
emp e,
dept d
where
e.deptno = d.deptno;
DONE :)
----------------------------------------------------------------------------------------------------------
NOTE: Restrictions on Fast Refresh on Materialized Views with Joins Only
- All restrictions from "General Restrictions on Fast Refresh".
- They cannot have GROUP BY clauses or aggregates.
- Rowids of all the tables in the FROM list must appear in the SELECT list of the query.
- Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.
- You cannot create a fast refreshable materialized view from multiple tables with simple joins that include an object type column in the SELECT statement.
create materialized view log on emp with rowid;
create materialized view log on dept with rowid;
Step 2: Create Materialized View with rowids for all the base tables
create materialized view empdept_mv
refresh fast on commit as
select
e.rowid e_rowid,
d.rowid d_rowid,
e.empno,
e.ename,
d.deptno,
d.dname,
from
emp e,
dept d
where
e.deptno = d.deptno;
DONE :)
----------------------------------------------------------------------------------------------------------
NOTE: Restrictions on Fast Refresh on Materialized Views with Joins Only
- All restrictions from "General Restrictions on Fast Refresh".
- They cannot have GROUP BY clauses or aggregates.
- Rowids of all the tables in the FROM list must appear in the SELECT list of the query.
- Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.
- You cannot create a fast refreshable materialized view from multiple tables with simple joins that include an object type column in the SELECT statement.
Hi all! can anyone explain me why are we using build immediate in materialized views.?? And also why do we use REFRESH FORCE ON DEMAND when we have dbms_mview.refresh??
ReplyDeletethere are 2 options to build a mview build immediate & build deferred. The materialized view does not initially contain any data if build method is DEFERRED. A complete refresh is required for the first refresh of a build deferred materialized view.
DeleteREFRESH FORCE ON DEMAND is ofcoure on demand refresh, REFRESH FORCE to take advantage of fast refresh when it is possible. This needs to be refreshed manually using dbms_mview.refresh or dbms_refresh.refresh.
DeleteHi Nimish ;
ReplyDeleteMay i know the difference between Master table Vs Local Base table. I am getting confused. Where to get all basic table information when creating materialized view?
I think both Master table and Base table are same. Just different words to use same item.
DeleteThanks buddy. This post was really helpful
ReplyDeleteHi Nimish ,
ReplyDeleteThere is one question asked in the interview ?
we have created materialized view with fast refresh by joining multiple table ( say 3 tables). The Question is every 5 sec DML operation is done on Base tables( i.e. 3 tables) and frequency of materialized view refresh is 10 sec. This materialized is used by GUI.
there is delay of 5sec. So what is the best approach you will suggest to see all data without 5 sec delay?
Try using ON COMMIT fast refresh
DeleteHi all,
ReplyDeleteCould you please clarify me that does we can create fast refresh materialized view when we are using user defined function in our select clause.
Can anyone please explain why this materialized view is not working with 9i joining condition like inner join , left join etc., Its only working with 8i joining condition ?
ReplyDelete