0

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

Aspirant
  • 2,238
  • 9
  • 31
  • 43

2 Answers2

2

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
0

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;

Hamza Shahid Ali
  • 224
  • 4
  • 14