Refresh in Materialized Views

Question

I have been trying to understand Materialized Views concept from a long time but was unable to get the exact functioning of it except that it is a physical table(replica) for the tables in different server.

I have tried the following

CREATE MATERIALIZED VIEW TEMP_MV BUILD DEFERRED REFRESH COMPLETE ON DEMAND AS SELECT C1,C2,C3 FROM TAB;

Here i have used BUILD DEFERRED so that i will get only the structure of the base table later i can get the records using REFRESH but how do i perform REFRESH

I have even tried BUILD IMMEDIATE so that it will show the records but then if i have inserted new records in base table i cannot get those new records in MV.

CREATE MATERIALIZED VIEW TEMP_MV BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND FOR UPDATE AS SELECT C1,C2,C3 FROM TAB;

I am using ORACLE 10G. Please help me in understanding MV better .

Thanks

Justin Cave · Accepted Answer · 2012-07-23 20:23:07Z

If you do not specify a schedule in your materialized view definition and the materialized view is not defined to refresh on commit, you would need to call DBMS_MVIEW.REFRESH in order to refresh the data in the materialized view. That is the "demand" that REFRESH COMPLETE ON DEMAND refers to-- you have to tell the materialized view when you want it to refresh.

Hamza Shahid Ali · Accepted Answer · 2017-07-12 02:24:10Z

First Create Log on base relation.
I use emp table as a base relation,and i want to update materialized view after 1 second of the commit.
So, Create Log on base relation using this query
Create materialized view log on emp;
then
Create materialized view test
refresh fast
start with sysdate
next sysdate+(1/(24*60*60))
as
select * from emp;

Collectives™ on Stack Overflow

Refresh in Materialized Views

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related