Create Materialized view which refresh records on daily

Question

Currently the Materialized view which I had created using REFRESH ON DEMAND so in this case I need to refresh MV explicitly using below command:

BEGIN DBMS_MVIEW.REFRESH('MV_DATA'); END; 

But now I need to refresh this MV on daily basis so could anyone please help to write this. I have seen that we can refresh this MV using writing explicit Job or using COMPLETE/FAST REFRESH statement in MV itself.

Thanks in advance!

Dorison

So if you have seen the ways MV can be refreshed, what is your exact problem? – Saphena

@YaroslavShabalin: I need to do this automate refresh instead of doing this manually – Dorison

Answer 1

You need to create the materialized view using START WITH and NEXT Clause

create materialized view <mview_name>
refresh on demand 
start with sysdate next sysdate + 1
as select ............

So if you want to refresh mview daily, you need to keep it refresh on demand and set the next refresh time as sysdate + 1. You can set any interval although.

Once you do this the materialized view is created and a job is set in Oracle that will refresh mview every 24 hrs (sysdate + 1).

For more information on how to do that, follow this link

Trek

if it is scheduled to be refreshed automatically then why on demand ? – Singleminded

On Demand is default mode and scheduled refresh(automatic) is also part of on demand mode. Apart from that you can mention on commit where mview will be refreshed when DML happens on underlying tables and commit is fired. – Trek

Answer 2

If you simply need a SQL query to simply refresh at 12 AM, then the below query would be enough.

CREATE MATERIALIZED VIEW MV_DATA
BUILD IMMEDIATE 
REFRESH FAST START WITH (SYSDATE) NEXT (SYSDATE + 1) WITH ROWID
ON COMMIT
DISABLE QUERY REWRITE
AS SELECT * FROM <YOUR TABLE>

If you need to have it refreshed around 6 AM, then use the below script. You can see and additional logic as + 6 / 24. In case if you need to change to 4 AM, use the logic as + 4 / 24.

CREATE MATERIALIZED VIEW MV_DATA
BUILD IMMEDIATE 
REFRESH FAST START WITH (SYSDATE) NEXT (SYSDATE + 1) + 6 / 24 WITH ROWID
ON COMMIT
DISABLE QUERY REWRITE
AS SELECT * FROM <YOUR TABLE>

Chitarrone

IMMEDIATE not IMMEDEATE – Alti

How would you do 7:30? – Adlei

Answer 3

I have edited Sarath's Script for it to run on specific time (i.e. 6AM).

CREATE MATERIALIZED VIEW MV_DATA
BUILD IMMEDIATE 
REFRESH FAST START WITH (SYSDATE) NEXT (TRUNC(SYSDATE) + 1) + 6 / 24 WITH ROWID
ON COMMIT
DISABLE QUERY REWRITE
AS SELECT * FROM YOURTABLE

Koopman

Answer 4

Refresh the mv every day at 1 AM

CREATE MATERIALIZED VIEW test1 BUILD IMMEDIATE USING INDEX REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT (trunc(sysdate)+1)+1/24 USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE "Your query"

Kazan