Skip to Main Content
  • Questions
  • Refresh Materialized view when there is records in the source table

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Santhosh.

Asked: June 21, 2016 - 9:07 am UTC

Last updated: June 29, 2016 - 3:28 am UTC

Version: 9.0.4

Viewed 1000+ times

You Asked

Hi ,

Need help to refresh one materialize view only when there is records in the table.

say example .

create materialize view MABCD as
select field1,field2,field3 from abcdef ;

my requirement is if abcdef have records greater than zero then the materialized view MABCD should be refreshed, if the table abcdef have zero records then materialized view MABCD should not be refreshed and should contain old data.

Can you kindly help on this.

Many Thanks
Santhosh

and Chris said...

There isn't an option to "conditionally" refresh a materialized view. Either you do it on commit, on demand (i.e. call refresh manually) or on a schedule.

If you want an MV to only refresh after inserts, then you should place a call to:

dbms_mview.refresh

after your inserts, but not your deletes.

This requirement seems bizarre though. Why do you need to do this?

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Job

Ghassan, June 21, 2016 - 1:04 pm UTC

Create a job or what else plsql sub-programs ..
Get the count
..If count ... then dbms redress. . End if;

Sometimes the simplest things didn't go up to mind.
Chris Saxon
June 21, 2016 - 2:00 pm UTC

I'm not following you. You mean have a job in the background that periodically checks and refreshes the MV if required?

You could do this. It's not clear what the requirement is though. If it's "simulate refresh on commit, but only for inserts" then the job approach will likely miss some data.

If it's "refresh whenever, but only refresh when there's data in the table", then yes, the job does the trick.

Followup

Ghassan, June 21, 2016 - 2:57 pm UTC

Yes seem that the requirements above fit with in the very last phrase.
But indeed no clear demand no 100 % adequate response .

Santhosh Kumar, June 28, 2016 - 12:09 pm UTC

Thanks for the reply ,

i have written small code to refresh based on condition, but im facing difficulty as im getting errors.

Below is the code which i written and calling it to refresh based on condition

DECLARE
a number(10) := 0;
BEGIN
-- check the boolean condition using if statement
select count(1) INTO a from CDWMARTMANUFACTURING.DIM_RAW_WAFER_MATERIAL;

IF( a > 0 ) THEN

DBMS_MVIEW.refresh('CDWMARTMANUFACTURING.MV_DIM_RAW_WAFER_SFDC');

END IF;

END;


Can you kindly help to know what is the issue in the above piece of code.

Thanks
Santhosh
Chris Saxon
June 29, 2016 - 3:28 am UTC

When you say "i'm getting errors" and then dont tell us what the errors are, this is like us saying:

"I've got a favourite colour. Please tell me what it is"

You need to help us to help you.

More to Explore

DBMS_MVIEW

More on PL/SQL routine DBMS_MVIEW here